見出し画像

【GASでIoT】スプレッドシートと連携する「かんたんスマート・カレンダー」を試作する（その3）～スプレッドシートとスクリプト、およびPicoWのスケッチご紹介～

Particlemethod

WEBネイティブのアプリである、Googleスプレッドシートは、ネットに繋がる他のデバイスで容易に情報を利用することができます。

そのシンプルな応用例として、スプレッドシートに記載した、イベントが有るか／無いか程度の簡単なカレンダー情報を、LEDの点灯情報で掲示することを試作してみました。

題して「かんたんスマート・カレンダー」です。

この掲示板は、以下の記事で紹介している天気予報表示システムを転用しつつ、スケール・アップしたものです。

先回の記事では、この「かんたんスマート・カレンダー」のシステム全体をご紹介しました。

今回は、システムでデータベースとして使用するスプレッドシート（試作版ですので、まともなカレンダーとしてはまだ使えません）および、そのデータを受信するラズベリーパイPico Wのスケッチをご紹介します。


本記事では実装コードをご紹介していますが、様々な理由により、この説明通りにいかない場合がしばしばあります。こうした場合の対応は、申し訳ありませんが、自己責任・自己解決でお進めくださるよう、お願いいたします。　

GASによるプログラムはの内容は、個人や小規模なグループ内での利用を想定しており、規模の大きなグループや商用での利用は想定しておりませんのでご注意下さい。

電子的なデータをスマホやPCの画面ではなく、それなりのサイズの掲示物として表示する意味


今回作ろうとしているシステムは、電子的に記録されている、ごく単純なカレンダー情報を掲示物として表示するものです。そんなことをする意味は何でしょうか。

電子的に記録されたカレンダー情報は、適当なアプリがあれば、スマートフォンやPCで見ることが出来ます。

しかし、スマートフォンなどを取り出して、画面をタップして・・・と、わずかな手間ではありますが、”見る”だけなのに余計な一手間が挟まります。

見たい情報は、「あるイベントが有るか／無いか」程度のごく単純なもので、できればどこかに掲示して、すぐ見れる様にしたいものです。アナログなカレンダーを併用するのも一案ですが、データが二元化してしまいます。

電子的なデータをカレンダーとして表示できるディスプレイが市販されていますが、それなりに値が張りますので、シンプルな情報の表示のために購入するのは躊躇われます。

そこで、シンプルなしか表示できない代わりに、安価に実装できる「かんたんスマート・カレンダー」を考えた次第です。これでも電子的なデータとリンクしている立派な「IoT」です。


カレンダーのデータベースとなるスプレッドシート

この簡単なスマート・カレンダーは、スプレッドシートをデータベースに使います。

ひな形のインポート

データベースとなるスプレッドシートのひな形を、以下に掲載します。

上記のエクセルのワークブックを、グーグル・ドライブ上のスプレッドシートにインポートしてご使用ください。

インポート後に行うこと

インポートしたら、外部からデータ読み取るためのシートの公開と、このシートへ、データを転記するためのスクリプトの追加とトリガー設定をします。

シートの公開

このスプレッドシートには、「ピコ表示」「カレンダ」の2つのシートがある筈です。

この内、「ピコ表示」の方は、外部のラズベリーパイPico WからアクセスするためにWEBページとして公開します。

以下の様に、「ファイル」⇒「共有」⇒「ウェブに公開」と進み、公開範囲で「ピコ表示」を選択して、このシートを公開します。


ここまで進むと、公開ページのURLが表示されるので、これを控えておきます。

URLは途中までの画像です

後で実装するスクリプトを動かしたあとで、公開URLにアクセスすると、以下の様なテキストがブラウザに表示されるはずです（下側は空白になっています）。

わざわざ公開用のシートを設定しているのは、公開範囲を最低限にするためです。公開シートに表示されるのは、記号の羅列のみですので、他者に見られても何の意味やら判らないでしょう。

公開したWEBページからデータを得る様にしているのは、GASを使ってデータをレスポンスさせる方法では、Pico側で上手く値が取れなかったためです。

ラズベリーパイPico WはOSが無く、Googleの各サービス（HTTPSで始まるURL）にアクセスしてレスポンスをうまく受信するのは、SSL認証の壁を乗り越える事が必要ですが、このプログラム事例が殆どない上、唯一得られたプログラム例では、記事にしている手法でしか値を取れませんでした。

トライした経緯は以下記事にあります。


スクリプトの追加

今度は、「カレンダ」シートを表示し、ここにスクリプトを追加します。このスクリプトは、カレンダー情報を記載したシートから、「ピコ表示」シートに最低限のデータだけを転記するスクリプトです。

スプレッドシートでは、シートを跨いでの値の転記を関数で行うことも可能です。ただ、関数の仕様上、ユーザがスプレッドシートにアクセスして編集している最中でないと値がリアルタイムに反映されない様でしたので、あえて関数を使わず、スクリプトで明示的に値を転記させます。

メニューから「拡張機能」⇒「Apps Script」を選択します。


コード・エディタが起動するので、以下のコードを記入します。

function myDataCopy() {
   
  //アプリケーションを取得｜★スプレッドシートのID★は各自のものを記入
  var myApp = SpreadsheetApp.openById('★スプレッドシートのID★');

  //対象シートをシートの名前を指定して取得
  var mySheet1 = myApp.getSheetByName('カレンダ');
  var mySheet2 = myApp.getSheetByName('ピコ表示');

 //データ記録範囲を指定して範囲を取得
  var myData = mySheet1.getRange(31,3,1,1).getValue();

  //値の書き出し
  if(myData!='▼▼▼▼D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000●●●●'){
    mySheet2.getRange(1,1,1,1).setValue(myData);
  }
}

「★スプレッドシートのID★」の部分は、先にインポートしたスプレッドシートのものに各自で書き換えください。

一旦保存し、「実行」をクリックしてスクリプトが稼働するか確認ください。

スクリプトのトリガー設定

次にトリガー設定します。コード・エディタのメニューから時計のマークの「トリガー」を選択します。

右下にある「トリガーを追加」をクリックします。

さきほどのスクリプトの関数を選び、実施は時間主導型とします。以下は例ですが、他の設定でもかまいません。（総実行時間の制約があるので、むやみに頻度を上げないでください）

以上で、サーバ側であるスプレッドシートの設定は完了です。

スプレッドシートの解説


このシートには、2つのシートがあります。内容を少し解説します。

一つ目は、「カレンダ」シートです。

最の方の範囲に、預手表があります。この表は以下の構成となっています。

・横に7列、日曜日～土曜日までの1週間のマス目がならぶ
・縦には、1日を3行（午前・日中・夜間）として、4週間分のマス目が並ぶ
・4種類の予定を絵文字で1つだけ入れる

ここにはデモとして、簡単なデータをアイコンで表示する形で記入しています。

この簡単なデモ用カレンダーを一部拡大して見てみます。

上記で最上段に「日」「月」とあるのは日曜日のつもりです。3行あるのは、各日の午前・日中・夜間の予定を記入する欄です。

記入するイベントは塾・学校・テスト・特訓（これは適当です）の4種類とし、上記のマス目にこの種類を絵文字で記入されています。（記入できるのは1つのみです）

ここで書き入れた情報に従って「かんたんスマート・カレンダー」がLEDの点灯色として表示するシステムです。

予定表の情報を文字列に置き換える

下の方、16行目付近からは、この絵文字を単純な数字に置き換えるシートがあります。（関数を使っています。自シート内であれば関数を使っても反映モレは発生しません）

LEDの点灯順に文字を並べ変えて結合する

更に下の方、31行目付近からは、この文字列を、LEDの点灯順に並べ変えています。最後にデータのヘッダとして▼を4つ、フッタとして●を4つで挟んでいます。


関数に何らかのエラーがあった場合は、すべてゼロのデータになる様にエラートラップ処理も記載しています。

このデータは、掲示板の発光デバイスである、NeoPixcelの各LEDへの信号になり、文字列の位置に従った位置のLEDが数字に依る色で点灯します。





次の記事では、ラズベリーパイPico W側のプログラムをご紹介します。

エクセルのVBAでアマチュアが自分だけのプログラムを作った様に、GASを使うことで、アマチュアでも比較的容易にWEBアプリを作れることを示したくて、GASで作った家計簿アプリの説明を簡単な本にしました。

デバイスを選ばないWEBアプリは、忙しい日常での記帳にぴったりかと思います。とても簡単な機能ですが、アマチュアによるWEBアプリ作成の事例として紹介させて頂きました。

内容は以下ですが、読みやすい様に縦書きにしています。（IT部門で縦書きはあまりないと思いますが、縦書きは日本語マッチした表記と考えています）

〔目次〕
第1章　ＷＥＢアプリ『簡単！どこでも家計簿』ご紹介
　1．データ原本にグーグル・スプレッドシートを使う
　2．『簡単！どこでも家計簿』の機能ご紹介
　3．ＷＥＢページを介してスプレッドシートに書き込む利点

第2章　ＧＡＳで作るＷＥＢアプリの動く仕組み
　1．ＷＥＢアプリとは何か、その動く仕組み
　2．ＧＡＳを利用してＷＥＢアプリを作るメリット
　3．『簡単！どこでも家計簿』の動く仕組み

第3章　実装しよう！『簡単！どこでも家計簿』アプリ
　1．はじめにお願い
　2．『簡単！どこでも家計簿』アプリのファイル構成
　3．『簡単！どこでも家計簿』アプリを実装する
　4．実装に使うエクセルシートやコードの入手方法

第4章　『簡単！どこでも家計簿』のコード解説
　1．本書でご紹介するコードの注意点
　2．本書のコード解説の方針
　3．アクセスして帳票を開くまでのコード（一般部分）
　4．アクセスして帳票を開くまでのコード（固有部分）
　5．記帳内容を書き込むコード（一般部分）
　6．記帳内容を書き込むコード（固有部分）
　7．指定日付のデータを読み込むコード（一般部分）

第5章　『簡単！どこでも家計簿』の全コード

第6章　結び

GASで、WEBアプリに特化した解説本はあまり見掛けないので、ご紹介としまして・・・。

コア部分の帳票アプリは以下で紹介しています。

WEBアプリは、意見集約にも使えます。スケジュール調整アプリはよくみる事例ですが、意見集約に使えるWEBアプリは少ないので、ご参考まで本にしました。

メールでは返信内容が埋もれる、チャットではスレッドが流れて最終意見のピックアップが難しい、Googleフォームなどの単票アンケートでは横並び形式に成形する手間がかかる・・・・といった欠点がありますが、WEBアプリを使うと、はじめから横並び形式で集約可能です。


（公式な使い方ではありませんが）GASのテンプレートにJSライブラリを組み込むと、エクセル風のインターフェースにしたWEB帳票もつくれます。

実験データをオンラインで書き込んだりする様な場合に使えると思い、これも解説本にしました。

以上、ご紹介まで・・・。

この記事が気に入ったらサポートをしてみませんか？