今回の記事は以前の記事の続編という位置付けですが、Googleスプレッドシートに限らずPOSTでJSONデータを受け取れるサーバー（例えばUbidotsなど）であれば同じように扱えます。
環境
今回のプログラムはPythonで書かれています。
次の環境で動作確認を行なっていますが、Python3が動作する環境であれば動作可能だと思います。
動作確認環境
環境1
環境2
- Windows 10 Pro 21H2
- Debian GNU/Linux 11 (bullseye) - WSLで動作
- Python 3.9.2
環境構築
ここではvenvを用いて仮想環境に動作環境を作る手順を例に説明します。
ですから、Python3の実行環境が整っていることが前提となります。
- プロジェクトフォルダの作成
最初に、プログラムを保存するためのプロジェクトフォルダを作ります。
mkdir transfer
- 仮想環境の作成
次にプロジェクトフォルダに移動してPyhtonの仮想環境を作ります。
cd transfer python3 -m venv venv
- 仮想環境の起動
仮想環境ができたら仮想環境を起動します。
source venv/bin/activate
仮想環境を起動するとプロンプトの冒頭に仮想環境名が表示されるようになります。以下はmacOSの場合の表示例。
(venv) hiro@macOS transfer %
- ライブラリのインストール
次の内容をコピーして「requirements.txt」ファイルに保存します。
certifi==2022.6.15 charset-normalizer==2.1.0 idna==3.3 paho-mqtt==1.6.1 requests==2.28.1 urllib3==1.26.11
次のコマンドを実行して必要なライブラリをインストールします。
pip install -r requirements.txt
- プログラムの実行
これでプログラムが実行できる環境が整いました。
次のコマンドで、後でお見せする転送プログラム「transfer.py」を実行できます。
python transfer.py
- 仮想環境の終了
仮想環境を終了するときは次のコマンドを実行します。
deactivate
プログラム
短いプログラムなので、まずは全体を示して設定方法や注意点などを解説します。
# -*- coding: utf-8 -*- import paho.mqtt.client as mqtt # MQTTクライアントライブラリ import requests # HTTPクライアントライブラリ import time # 時間ライブラリ webAppUrl = 'スクリプトのデプロイで表示されるウェブアプリのURLで置き換え' broker = 'test.mosquitto.org' # MQTTブローカーのアドレス topic = ['202208311405/temp', '202208311405/hum', '202208311405/pres'] # サブスクライブトピック key = ['温度', '湿度', '気圧'] # 送信するキー value = list(range(16)) # 送信するバリュー interval = 60 # 送信間隔 lastSend = time.time() # 前回の送信時間 # ブローカーに接続したときの処理 def on_connect(client, userdata, flags, respons_code): print(broker + 'に接続しました') for i in range(len(topic)): # トピック分ループ client.subscribe(topic[i]) # トピックをサブスクライブ # メッセージが届いたときの処理 def on_message(client, userdata, msg): global data for i in range(len(topic)): # トピック分ループ if msg.topic == topic[i]: # トピックが見つかったら value[i] = msg.payload.decode('utf-8') # トピックのデータを取得 print(msg.topic + ' : ' + value[i]) # MQTTの処理 client = mqtt.Client() # クラスのインスタンス(実体)の作成 client.on_connect = on_connect # 接続時のコールバック関数を登録 client.on_message = on_message # メッセージ到着時のコールバック client.connect(broker, 1883, 60) # ブローカーに接続 client.loop_start() # MQTT処理開始 # Googleスプレッドシートにデータを送る処理 while (1): now = time.time() # 現在時刻を取得 if now - lastSend > interval: # 送信間隔が過ぎていたら lastSend = now # 前回の送信時間を更新 dict1 = dict(zip(key, value)) response = requests.post(webAppUrl, json=dict1) # データ送信 print('スプレッドシートに送信' + str(response)) time.sleep(.1) # 0.1秒待つ
トピックのデータ形式の前提
本来MQTTでパブリッシュ／サブスクライブするトピックのデータ形式は決まっていません。
例えば、センサーの温度・湿度・気圧を3つのトピック「env3/temp」「env3/hum」「env3/pres」に分けてそれぞれの値を送ってもいいし、「env3」という1つのトピックで次のようなJSON形式のデータを送っても構いません。
{
"temp": 25.1,
"hum": 50.1,
"pres": 999.1
}
ここで紹介しているプログラムは、1つのトピックで1つの値を受け取ることを前提としているので、JSON形式で受け取りたい場合はプログラムを修正する必要があります。
これは、最近よく使っているIoT OnOffというスマホアプリがJSONを扱うのに課金が必要で、金額は高くないのですが講座では使い難いからです。
ウェブアプリのURLの設定
7行目の「webAppUrl」は、Appls ScriptでデプロイしたウェブアプリのURLで置き換えます。
ブローカーのアドレスの設定
8行目の「broker」は、MQTTのブローカーアドレスを設定します。
プログラムではパブリックブローカーの1つ「test.mosquitto.org」を使っています。
サブスクライブするトピックの設定
9行目の「topic」は、サブスクライブするトピックを複数書くことができます。
ここではパブリックブローカーを使っているため、他のユーザーとトピックが重複しないようにトピックがユニークになるような工夫（日付と時刻を利用）をしています。
送信するキーの設定
このプログラムはウェブアプリのPOSTリクエストに対応しています。
10行目の「key」は、JSON形式で送られるデータのキーになり、シートのヘッダーの名前です。
この行は9行目のトピックに依存していて、トピックの数と順序にキーを合わせる必要があります。
送信間隔の設定
12行目の「interval」は、ウェブアプリにJSONデータを送る間隔を秒で設定します。
送るタイミングの直前に、サブスクライブしているトピックから届いた値を単純に送信するようになっています。
また、値も単純に上書きしているだけのため、サブスクライブしているトピックの送信間隔より長い時間にしないと、同じ値が送信されてしまします。
動作確認
動作確認は本物のMQTT対応温湿度計などがなくても、値をパブリッシュできるツールがあれば行うことができます。
ここでは手軽に利用できるHiveMQの「MQTT Websocket Client」を使ってみます。
- 「http://www.hivemq.com/demos/websocket-client/」をブラウザで開いて「MQTT Websocket Client」を表示します。
- 「Host」にMQTTブローカー、「Port」にそのMQTTブローカーのウェブソケットポートを入力して「Connect」ボタンをクリックします。
- 「Topic」にPythonのソースコードの9行目に設定したトピックのどれかを入力して、「Message」にそのトピックの値を入力したら「Publish」ボタンをクリックします。
- 転送プログラムの送信間隔（初期値は60秒）になるとデータが送られ、スプレッドシートに書き込まれます。
温度の「26.1」以外の値「0」「1」「2」はダミー値で、送信間隔中にトピックを受信しなくてもデータを送信する単純なプログラムのために書き込まれています。
