Google Apps Scriptを使用してスプレッドシート上の図形を押した際に、プログラムを実行させる

投稿日：2020年5月13日更新日：2020年6月7日

大学生の電子工作　ラズパイでスマートロック作ってみた⑤の記事に関連して、スプレッドシート上に設置したボタンを押した際に Google Apps Scriptを用いたプログラムを実行することで、ラズパイにHTTPリクエストを送信して、ラズパイ上でスプレッドシートのデータを取得しICカード情報の更新を行う方法について紹介します。

一言でいうと、「スプレッドシートのボタンを押す」→「 Google Apps Script （GAS）を実行」→「ラズパイに更新要求を行う」といった流れです。

今回の最終目的は以下のようなシートを作り、画像の右上の「認証情報更新」ボタンを押すとGASが実行されることです。

Google Apps Script （GAS）とは

Google Apps Script (GAS)とはスプレッドシートやグーグルカレンダー、Gmailなどの Google AppsのAPIを呼び出し、各種機能を実行したり、連携したりすることのできるものです。

ベースは JavaScript なので非常に簡単に扱えます。

スプレッドシート作成

今回作成するのは以下のようなシートです。

大まかな機能としては2つあり、

1つは、認証の欄の逆三角形のボタンを押すと、そのセルの下にAllow,Deny,Unregisteredの三つの選択肢が表示され、各ボタンを押すと選択した認証ステータスにセル内の文字が切り替わるという機能です。（2.1）

そしてもう1つは、右上の認証情報更新ボタンを押すとラズパイに信号が届き、ラズパイ側で、ICカード情報が最新のものに更新されるという機能です。（2.2）　

ICカード情報を記載するシートの作成

まず一列目に、記載したいICカード情報の名前（ここでは、日付、時刻、IDM、認証、所有者、カード名）を記載した後に赤い四角で囲った部分をクリックし、一行目を選択します。

次に、「表示」→「固定」→「現在の行まで」をクリックし、一列目を固定します。

これは表を見やすくするために行うだけなので、やらなくても問題ないです。

続いて、E列をクリックし、E列を選択状態にした後に、

「データ」→「データの入力規則」へと進みます。

するとこのような画面が出てくるので、

セル範囲をE1からE2へ　（タイトルの部分を残すため）
条件を「リストを直接指定」に変更し
右の空白のセルに、Allow,Deny,Unregisteredと入力します。

これでこのように決められたデータのみの入力ができるようになります。

スプレッドシートに認証情報更新ボタンを搭載

まず、 Google Apps Script のプログラムも記述を行います。

「ツール」→「スクリプトエディタ」へと進みます。

すると、このようなエディター画面が表示されるので、ここに以下のコードを記述します。

function update_info() {
  var headers = {
    "Content-Type": "application/json"
  };
  var json = '{"data":[{"terminal":"sp_app","device":"IC","action":"update"}]}';
  var options = {
    "headers": headers,
    "method": "post",
    "payload": json
  };
  UrlFetchApp.fetch("https://api.beebotte.com/v1/data/publish/xxxxxxx/xxxxxxx?token=token_xxxxxxxxx", options);
}

このコードは、MQTTのブローカーであるbeebotteにPOSTリクエストを送信するプログラムです。

コード中のxxxxxxのところには beebotte の TOKEN と TOPIC を入力してください。

続いて、ボタンの作成を行います。