見出し画像

【GASでIoT】ＰＣ画面のグラフ表示を「リアルタイム棒グラフ掲示板」で表してみよう（最終）～スプレッドシート上のデータをレスポンスするWEBアプリをGASで用意する～

Particlemethod

この記事は、LEDを数珠つなぎにした、NeoPixelをり用して作ったリアルな棒グラフを表示させるための、GASプログラムについての記事です。

上に挙げる先回の記事では、ラズベリーパイでWEBアプリからLEDを光らせるためのレスポンスを得て、表示させるための準備をしました。

今回の記事はこの続きで、必要なレスポンスを与えるためのWEBアプリをGASで用意する仕組みとプログラムをご紹介します。

実機が関わる場合は様々な理由により、この説明通りにいかない場合がしばしばあります。申し訳ありませんが、自己責任・自己解決でお進めくださるよう、お願いいたします。

表示させるデータの流れの全体像について

最初に、「リアルタイム棒グラフ掲示板」に表示させるデータの流れの全体像について触れておきます。

まず、表示させたいデータは、スプレッドシート上に表現しておきます。

このデータを専用のスプレッドシートに読み込んで、ＬＥＤのオン／オフに変換し、これをＧＡＳを使ってレスポンスさせます。

ラズベリーパイが、そのレスポンスに基づいてNeoPixelを光らせるのは前回の記事でご紹介した通りです。

図でいうと、以下の様になりますが、今回の説明は図で囲った部分、つまりLEDのオン／オフの情報に直すシート、およびWEB上にそれをレスポンスさせるWEBアプリについてです。

データをLEDのオン／オフに直すシートについて

表示させる元のデータ

まず、表示させるデータは、別のスプレッドシート上に、下図の様に横1線で、7つのセルに表示されているものとします。

ここでは毎日の支出を、1週間分表記している想定ですが、横一行に7つ並んだ数値であれば何でもかまいません。この例では、I8セルからO8セルにかけて記載しています。

データをLEDのオン／オフに変えるシート


これを、以下に示す様なシートに転記してLEDのオン／オフデータに変換します。

以下に手順を示します。
まずは先に示した参照元のデータを、以下のシートの黄色い部分にコピーします。（詳細は後述）このシートにはその下に0と1を示す関数が記載されています。


このシートの一番左の列は、黄色い部分の値を示す目盛りと考えてください。

0と1が表示されている部分は、以下の様な関数を入れています。
　=IF(上の黄色セルの数値　＞　左側の一行下の目盛り数値　,1,0)

分かりにくいので具体例で示します。例えば、上記のグレーの色の付いた領域（一番上にある数値は3815です）では、上から順に、以下の様な結果が表示されています。


  • 3815（黄色セル）　＞　5000（該当行の1行下の目盛り）　ではないので　0

  • 3815（黄色セル）　＞　4000（該当行の1行下の目盛り）　ではないので　0

  • 3815（黄色セル）　＞　3000（該当行の1行下の目盛り）　なので　1

  • 3815（黄色セル）　＞　2000（該当行の1行下の目盛り）　なので　1

  • 3815（黄色セル）　＞　1000（該当行の1行下の目盛り）　なので　1

  • 3815（黄色セル）　＞　0（1行下は何もないので0）　なので　1

こうする事で、下図の様に、数値を1000刻みの「1」で棒グラフ化しています。

次に、この「1」（と「0」）のデータを、以下の様に右上の角を起点に、千鳥に読み取って並べ直します。

これは、ちょうどNeoPixelの、ラズベリーパイに近い側からの並びに対応しています。

このデータを受けたラズベリーパイ側では以下のコードで、順次LEDを点灯させています。

    #---黄色で１灯ずつ点灯の有無を判断し追加点灯（１なら点灯）       
    for i in range(70): 
        if r.text[i+1] == '1':
            pixels[i]=(50, 50, 0)
            pixels.show()

コードの意味：レスポンスとして受け取ったテキストの2文字目以降（1文字目は意味の無い「Ｔ」なので無視）を順次調べ、「1」であれば、該当のLEDを黄色く点灯しています。（pixels[i]=(50, 50, 0)）

実際のデータの作成の流れをみてみましょう。
以下の、1と0のデータ群を、前述の様に千鳥に読み取って・・・

・・・・以下の様に縦1列に配置しなおし（色で、大体の領域を表しています。上下逆になる範囲は目印の為に左に数字を書いています）・・・

・・・

・・・最後に上記の列の文字全体を以下の関数で結合して一つのテキストにしています。

　="T"&concatenate(セル範囲)

（頭に”T”を入れたのは、数字以外の文字を加えて、強制的にテキストデータとして表示させ、頭のゼロが消えたりしない為です。）

最終的に以下の結果を得ます。これがレスポンスするデータになります。

ここまでノンプログラミングで出来るのが、初心者にはうれしいところです！


他のシートの参照は　ImportRange（）関数で行う

ところで前後しますが、最初に別なシートにある元のデータを・・・

・・・下記の黄色い範囲に引用している部分では、エクセルの様に「＝シート名！セル番地」による指定では参照せず、「＝ImportRange（）」というスプレッドシート固有の関数を使用しています。

これは、スプレッドシートでは、シート名！セル番地による指定では、最新状態に自動更新されない場合があるからです。（単純なセル番地指定で値が必ず更新されるのは、引用元が同一シートの場合のみです）

たとえ同じスプレッドシート（ファイル）内であっても、シートが分かれていれば更新が常にされるとは限りませんので（編集時などに限定しています）、最新状態を保つためには、ImportRange（）を使います。

この関数は以下の様に使用します。
=ImportRange(スプレッドシートID,　”シート名 ! セル番地1：セル番地2”)

セル番地1、セル番地2は、それぞれ引用範囲の左上、右下の番地を挿します。

以上説明したシートのひな形をエクセルワークブックで添付します。

ご利用になる場合は、ダウンロードした上でスプレッドシートにインポートしてください。


インポートしたら、左上に、参照元のスプレッドシートのID、参照するシート名、セル番地（ダミーで例を入れています）を入力する部分が黄色いセルで示されていますので、各自で打ち替えてご利用ください。

打ち込んだ値に応じてImportRange（）関数が参照元から目的のデータを引用します。

エクセルシートをスプレッドシートに取り込む手順詳細は、以下の記事の「スクレイピングするシートの例」の項に記してあるので、ご参考とされてください。


作成したデータをレスポンスするWEBアプリをGASで作成する

次に、以下のデータをWEBからアクセスして得るためのWEBアプリをGASで作成します。

内容的には以下の記事のアプリとほとんど変わりません。


コードを以下に記します。GASの新規プロジェクトで、適当な名前のスクリプトフェアを作り、以下を丸ごとコピーして保存します。

//-----------------------------------------
//----ラズパイで日毎支出をLED色表示-----------------
//----レスポンス　LEDの点灯有無--------
//---著作：Particlemethod-2021年12月27日-----
//---無断複製・転載・配布を禁じます-------------
//-----------------------------------------
function doGet(e) {


  if(e.parameter.x == "1"){

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

  //対象シートをシートの名前を指定して取得｜シート名は「支出」
  var mySheet = myApp.getSheetByName('支出');
  
  //C23セルの値をレスポンスとして返す
  return ContentService.createTextOutput(mySheet.getRange(23,3).getValue());

  }

}

★スプレッドシートID★は、さきほどインポートしたスプレッドシートのものに打ち替えてください。

これを前記の記事の手順で実装し、公開（デプロイ）します。

デプロイしたら、デプロイIDを確認し、前回の記事にある・・・

・・・ラズベリーパイのプロフラムの★ＧＡＳのデプロイID★部分に反映させてください。

#---URLにアクセス｜★ＧＡＳのデプロイID★　は各自打ち換え
    r = requests.get('https://script.google.com/macros/s/★ＧＡＳのデプロイID★/exec?x=1')

今回、蛇足ながら、WEBアプリにアクセスする場合、URLに「X=1」というテキストを付与しています。
これは、クエリーパラメータというもので、アクセスを受けたGASの側では、X=1という情報を判別して実行しています。以下がその部分です。

function doGet(e) {

  if(e.parameter.x == "1"){
　｝
｝

今回は分岐する他の条件がないので、全く余計なコードですが、後々、分岐条件を足したい場合のために付け加えています。

以上で、準備が完了しました。ラズベリーパイの電源、およびNeoPixelの電源（外付けの5Vです）をそれぞれ入れ・・・

・・・ラズベリーパイのターミナルからルート権限者（Sudo）としてPythonファイル（ここでは test.py とします。なお開発環境から実行させるとルート権限者扱いにならずエラーとなります ）を実行させます。

sudo python3  /home/pi/test.py

しばらくすると予備動作のあと、黄色いドットでグラフが表示されるはずです。


最後に補足

今回、デバイスを手作りし、ラズベリーパイ側とWEB側のプログラムも手作りしました。

個人が作る範囲が広いので、工作やプログラムのミスなど、さまざまなエラー要因が入り込む余地が大きく、うまく行かない場合もあると思います。

冒頭あります様に、基本自己解決をお願いします。

そろえる部品も多く、半田づけなど集中する作業もありますので、期限をきめず、時間に余裕がある時に少しずつ進めるのがストレスがなくお勧めです。


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