Google Apps Script(GAS)を使って、リモートからHTTPSリクエスをを行うことでGoogle Drive内のスプレッドシートを更新することができる。
同様のことはIFFFTなどからも可能だけど、登録が面倒だったり、増えると有料プランになったりしてやっかいなので使いたくない。AWS IoTなどのIoTサービスの無料枠内でもできるのだけど、グラフ化する場合などにサービス連携を書くのがめんどくさい。そういう時は、GASとスプレッドシートの組み合わせは、マクロを使ってデータをフィルタしたりグラフ化したりできて便利。
Googleアカウントは、みんな持っていると思うので、新しく何かサービスに登録しなくていいしね。
まず、スプレッドシートを作っておく。ブラウザでdrive.google.comにアクセスして、Google Driveを開く。
新規スプレッドシートを開く。
新しいタブでスプレッドシートが開く。
スプレッドシートには名前を付けておく。ここでは「二酸化炭素計測」にした。
スプレッドシートのURLをメモしておく。この例では、こんな感じ。
https://docs.google.com/spreadsheets/d/1zfJoVa59imvyVt_DhG_-YR7OI3bzSsHOoC3n5P1xSFk/edit#gid=0
送信想定データは、センサで計測した二酸化炭素濃度でco2ppm=410という感じの単独の値のみ、日付はESP32の内蔵時計がアテにならないことを考慮して、GAS側で付ける。ESP32内でNTP同期してもいいんだけどね。
スプレッドシートからスクリプトエディタを開く。メニューから、「ツール」->「スクリプトエディタ」を選ぶ。
新しいタブが開いて「無題のプロジェクト」と表示される。
テンプレートで、最初から"function myFunction() {}"と書かれている。
ここでは、POSTでデータを受信する。スクリプト内ではeに受信データが格納される。渡されるパラメーターは、e.parameterに入っている。
データを保管するスプレッドシートは、スプレッドシートIDで指定する。IDは、先程確認したスプレッドシートのURLに入っている。今回の例では、"1zfJoVa59imvyVt_DhG_-YR7OI3bzSsHOoC3n5P1xSFk"になる。
スクリプトはこんな感じ。
function doPost(e) {
var co2ppm = e.parameter.co2ppm;
if(co2ppm){
try{
var spreadsheet = SpreadsheetApp.openById("1zfJoVa59imvyVt_DhG_-YR7OI3bzSsHOoC3n5P1xSFk"),
sheet = spreadsheet.getSheets()[0],
date = new Date();
sheet.appendRow([date, co2ppm, e.postData.getDataAsString()]);
}catch(e){
console.log(e);
}
}
}
プログラム名が「コード.gs」になっているが、特段変更の必要はない。
メニューから「保存」を選んでスクリプトを保存する。
プロジェクトに名前を付けるようにうながされる。
名前は、スプレッドシートと同じ「二酸化炭素計測」にしておく。
作成したGASを外部からアクセスできるようにする。
メニューから、「公開」->「ウェブアプリケーションとして導入」を選ぶ。
ウェブアプリケーションとして公開する設定画面が表示される。わたしは、Googleアカウントのデフォルトが英語インターフェイスなので、英語表記になっている。
「プロジェクトバージョン」は、今回はデフォルトの「新規」で良い。保存時に、自動的に「１」が割り当てられる。プログラムを書き換えた場合は、バージョンを上げて再度公開設定を行う必要がある。
「次のユーザーとしてアプリケーションを実行」では、とりあえず使用しているGoogleアカウントを使う。すぐ下には「URLを配布する前に、スクリプトを承認する必要があります」と書かれている。
「アプリケーションにアクセスできるユーザー」を「全員（匿名ユーザーを含む）」に変更して、「導入」ボタンを押す。
なんで、全ユーザーだけ日本語なのかはわからないw
こうすると、このインターフェイスには、今回のGoogleアカウント権限で、誰でもアクセスができるようになってしまう。しかし、URLは知らない限りアクセスができない十分な長さがあると思われるので、気にしないことにする。もし必要であれば、クライアント認証なども導入できる。
「許可が必要」というダイアログが出る。
「許可を確認」ボタンを押す。ポップアップウインドウが開いて、許可するアカウントを選択する画面になる。今回のGoogleアカウントを選択する。
「このアプリは確認されていません このアプリは、Google による確認が済んでいません。よく知っている信頼できるデベロッパーの場合に限り続行してください。」と警告が表示される。
左下にある「詳細」リンクを押すと、さらに細かい内容が表示される。
一番下に「二酸化炭素計測（安全ではないページ）に移動」が出てくるので、それを選択する。
アクセスのリクエストを許可する画面に移動するので、「許可」ボタンを押す。
同じアカウントに登録している携帯電話などに通知が来るので、こちらでも許可をする。
「はい、心当たりがあります」を押す。
許可後に、メールのほうにも結果が通知される。
これで、ようやく外部からのアクセスが許可された。もとのウインドウに戻ると、アクセス用のURLが表示されているのでコピーしておく。
https://script.google.com/macros/s/AKfycbz5da5_XD6xDULiNdF5771LaWQz1q2A2Njmbb11RcT53XsDVqY/exec
登録されたGASに外部からアクセスできるかどうかcurlコマンドで確認してみる。
$ curl -X POST -d 'co2ppm=410' https://script.google.com/macros/s/AKfycbz5da5_XD6xDULiNdF5771LaWQz1q2A2Njmbb11RcT53XsDVqY/exec
結果はエラーが返っているが、スクリプトは完了していいて、特に何もデータを返していないのでこれで問題ない。
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>エラー</title><style type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">スクリプトが完了しましたが、何も返されませんでした。</div></body></html>
スプレッドシートを見ると、データが書き込まれているのが確認できる。
あとは、センサを接続したマイコン側でデータを作って投げてやるだけでよい。
誰でも書き込めてしまう問題や、マイコンのプログラムで個別にリクエストURLをハードコードする必要があるなどの問題があるので、商用で利用するのは難しいが、個人のデータ計測であれば、これくらいで問題がないだろう。
オリジナル投稿：
GASでスプレッドシートをリモート更新する｜kinneko｜pixivFANBOX
https://kinneko.fanbox.cc/posts/632330
