Googleスプレッドシートをウェブアプリ化してシートにネット経由でデータを書き込めるようにしたので、MQTTでパブリッシュしているデータをシートに蓄積するためのデータ転送プログラムも作った。その1

みなさん、マイコンの計測データをどのように記録していますか？

企業であれば、様々なデータをデータベースに記録して色々と活用できるようにするのが一般的だと思いますが、個人であればもっと手軽にデータを扱いたいと思う人は多いと思います。

UbidotsやMachinist、ThingSpeakなど簡単に送ったデータをグラフにできるサービスも沢山ありますが、無料で使おうとすると様々な制約があって思うように使えなかったり、最終的に使い慣れている表計算ソフトにデータを取り込んで作業をする方も多いのではないでしょうか。

それなら、「最初から表計算ソフトでデータを受け取ればいいんじゃないか」と、考えるのが普通です。

でも、そんな魔法のようなものが現実にあるのか？

あったんです、Googleスプレッドシートという魔法のツールが！

Googleスプレッドシートのウェブアプリ化

Googleには色々なサービスやAPIが用意されているため、Goolgeスプレッドシートにネットからデータを書き込む方法も色々とあるみたいです。また、同じ方法でも時期によって違いがあったりしていて混乱を醸成していますが、今は無料で簡単にできる状況になっていました。

では、早速はじめましょう！

1. Googleスプレッドシートのメニューから[拡張機能] > [Apps Script]を選びます。
   

   

2. コードの入力画面が表示されるので、次のコードを入力します。
   

   // GETリクエスト処理
   function doGet(e) {
     append(e.parameter);                          // シートにキーバリューを追加する処理
     return HtmlService.createHtmlOutput("<h1>OK</h1>"); // OKを返す
   }
        
   // POSTリクエスト処理
   function doPost(e) {
     const message = e.postData.getDataAsString(); // ポストデータを取得
     const dict = JSON.parse(message);             // キーバリューにデータを変換
     append(dict);                                 // シートにキーバリューを追加する処理
     return HtmlService.createHtmlOutput("<h1>OK</h1>"); // OKを返す
   }
        
   // シートにキーバリューを追加する処理
   function append(kv) {
     const sheet = SpreadsheetApp.getActive().getSheetByName("シート1"); // 対象シート
     const headerRow = sheet.getDataRange().getValues()[0]; // ヘッダー行取得（最上部行）
     let row = [];                                 // シートに追加する配列
     for (let colName of headerRow) {              // ヘッダーの要素分ループ
       if (colName == "時刻" && kv[colName] === undefined) { // 時刻キーがないとき
         row.push(new Date());                     // 現在時刻を追加
       } else {
         row.push(kv[colName]);                    // ヘッダーと同じキーのバリューを追加
       }
     }
     sheet.appendRow(row);                         // シートに行を追加
   }
   

   

3. Apps Scriptの[デプロイ]ボタンから[新しいデプロイ]を選びます。
   

   

4. 「デプロイタイプを選択してください」が表示されたら、歯車から[ウェブアプリ]を選びます。
   

   

5. 「新しい説明文」を入力後、「アクセスできるユーザー」を[全員]に変更して[デプロイ]をクリックします。
   

   

6.  この画面が表示されたら[アクセスを承認]をクリックします。
   

   
   
   

7. 新しいウインドウが開いてGoogleのログイン画面が表示されたら、自分（Googleスプレッドシートを作った人と同じ人）を選びます。
   

   

8. こんな脅し文句が表示されますが、自分が作ったアプリなので気にせず「詳細」をクリックします。
   ちなみに「デベロッパー」には自分のGoogleアカウントが表示されます。（ぼくのアカウントは表示されませんよ。）
   

   

9. しつこい脅しに負けずに「無題のプロジェクト（安全でないページ）に移動」をクリックします。（自分を信頼できない人は思い止まりましょう。）
   

   

10. しつこいのですが、ここで問題視している「無題のプロジェクト」は許可を与えようとしているアカウント（同一）で作ったものなんですよ。
    気にせず「許可」をクリックしましょう。
    

    

11. Goolgeから「セキュリティ通知」のメールが届きますが、それは放っておいて大丈夫です。
    「デプロイを更新しました」と表示されたら、ウェブアプリのURLをコピーしてメモしておきます。「完了」をクリックしたら終了です。
    

    

    画面が更新されなかったときは、再度「アクセス承認」をクリックして開いたウインドウを閉じると更新されるみたいです。

ウェブアプリのテスト

作成したウェブアプリはGETリクエストとPOSTリクエストに対応しています。

どちらのリクエストもシートのヘッダー行（1行目）にリクエストと同じ項目名があれば行を追加してデータを書き込みます。

実例の方がわかり易いので、いくつか例を書きます。

シートの準備

シートのヘッダー行に項目名をセットしておきます。

例えばこんなふうに。

GETリクエストのテスト

GETリクエストのテストは簡単で、ブラウザを使って行うことができます。

ブラウザのアドレス入力欄に<ウェブアプリのURL>?項目名=値&項目名=値と入力してエンターキーを押すだけです。

（Firefoxのプライベートウィンドウでの入力例。?以降は「湿度=59.1&温度=26.1」）

シートにはこのように書き込まれます。

「時刻」はリクエストに含まれていない場合は「現在時刻」をスクリプトで自動付与するようにしています。

ブラウザの入力欄に記載する順番は関係なく、同じ名前の列に値が書き込まれます。

ヘッダー行に項目はあるけど、リクエストに名前がない場合はその列には何も書き込まれません。

続けて、もう一回ブラウザからリクエストを飛ばしてみましょう。

（Firefoxのプライベートウィンドウでの入力例。?以降は「気圧=1001&温度=26.2」）

シートにはこのように書き込まれます。

POSTリクエストのテスト

POSTリクエストは何かツールを使わないと送ることができません。

普段はPostmanというツールを使っていますが、プログラムをインストール必要があるため、ここではPOSTリクエストを送信できるサイトを使って説明しようと思います。

データ形式

POSTリクエストではJSONでデータを送るようにしました。
例えばこのようになります。

{
  "湿度": 59.3,
  "温度": 26.3,
  "時刻": "2022/08/22 15:18:00"
}

「POSTメソッドのテストツール」を使った送信例

上記のJSONデータをツールを使って送ってみましょう。

下図のように入力したら「Send」ボタンを押します。

次のように「200」のステータスコードが返って来たら成功です。

シートにはこのように書き込まれます。

長くなってしまったので、データ転送プログラムは次の記事に回そうと思います。