【GASでIoT】GASで「ラズパイ Pico W」から、Googleスプレッドシートへの読み書きにトライ！（その4～テスト用スプレッドシートとGASの準備～）

2023年5月6日 09:32

エクセルのクラウド版ともいうべき、Googleスプレッドシートに対し「ラズベリーパイ　Pico W」使って読み書きする、「お手軽IoT」の実装トライする記事です。

「Pico W」はOSを有さないため、開発環境はPC側で構築し、ここで実行コードを生成してデバイスに転送します。

ところがメジャーな開発言語である「MicroPython」では、上手く環境構築ができなかったため、「C」言語で、「Arduino IDE」を使ったプログラミングを進める事にしました。

本環境で”Lチカ”に成功した記事はこちらです。

環境構築ができたところで、いよいよスプレッドシートへの読み書きプログラムの実装へ進みます。

この記事は、一介のアマチュアが、断片的な手がかりを寄せ集め、試行錯誤しならがら行った記録です。

失念による「手順の記載もれ」、たまたま上手くできた条件を見逃している「前提条件の未記載」、誤認や理解の浅さによる「誤り」を含んでいる可能性が大いにあります。

うまく再現できない場合があることをご了承頂き、自己責任、自己解決を前提にお読み頂くことをお願いします。

テスト用スプレッドシートの準備

まず、今回の通信テストに使う、Googleスプレッドシートを用意します。

Googleドライブ上にスプレッドシートを新規作成し、デフォルトで生成される「シート1」の、A1セルに、以下を記入しておきます。

▼▼▼▼◎◎◎通信テスト◎◎◎●●●●

上図では青く色を付けていますが、このA1セルのテキスト情報を読み出そうという訳です。

また、上図では黄色く色を付けている、A2セルには、テキストの書き込みをしてみる事にします。

なお、このスプレッドシートは、名前は何でもかまいかせんが、ブラウザのヘッダ部分から以下★部分のスプレッドシートIDを確認しておきましょう。

https://docs.google.com/spreadsheets/d/★スプレッドシートID★/edit・・・

書き込み用Goole Apps Scriptの用意

次に、書き込み用Goole Apps Script（GAS）を用意します。

このGASは、アクセスすることで、A2セルへの書き込みを行うものです。

Googleドライブの左上の「新規＋」アイコンをクリックし、「その他」⇒「Goole Apps Script」と進んで、GASのプロジェクトを作成します。

デフォルトで生成されるスクリプトファイルに、以下のコードを入力します。

シート名「シート１」は変えない様にしてください。また、★スプレッドシートID★部分は各自打ち替えてください。

//-----------------------------------------
//----ラズパイのトライ-----------------------
//---著作：Particlemethod-2023年04月15日-----
//-----------------------------------------
function doGet(e) {
  var data=e.parameter.data;


  if(e.parameter.data == null){
    return ContentService.createTextOutput('No Data');
  }else{

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

  //対象シートをシートの名前を指定して取得
  var mySheet = myApp.getSheetByName('シート1');

  //データをA１セルに代入 
  mySheet.getRange(2, 1).setValue(data);
  return ContentService.createTextOutput('▼▼▼▼Success Data●●●●');

  }
  
}

入力したら保存し、誰でもアクセス可能な設定で、デプロイ（アクセスするURLを発行する）しておきます。

参考情報

「デプロイ」などの基本手順について知りたい場合は、ネットなどで探してみてください。

参考まで、以下記事に紹介するKindleにまとめました。Amazon読み放題なら無料で読めます。

若干のコード解説です。

このGASは、デプロイしたURLにアクセスする時に、マクロコード（？に続く、変数＝値　部分：以下例）付きでアクセスすると、これをセルA2に記入します。

アクセスURL？data=文字

上記を扱っている部分が以下の２行のコードです。

・・・・

マクロコードから値を取り出して変数ｄａｔａに代入する
var data=e.parameter.data;

・・・・

変数ｄａｔａの値をセルＡ２に値を記入する
 mySheet.getRange(2, 1).setValue(data);

・・・

プログラムには、レスポンスを返すコードもあるのですが、この部分はテスト用です。

GASのテスト

コードをデプロイしたら、試しにＵＲＬに、ＰＣ上のブラウザからアクセスしてみてください。

マクロコード無しだと、以下の様になるはずです。

マクロコード付きで以下の様にアクセスすると、
https://script.google.com/macros/s/★デプロイID：各自違います★/exec?data=X

ブラウザには、以下の様に表示されます。

スプレッドシートには「X」という文字がA2セルに記入されます。

以上の状態であれば、GASはうまく機能しています！

この内容をAPI（ネット上のサービス）を使って実装することも可能ですが、この記事では、アクセスを公開（ラズパイからのアクセスは、Googleにログインしていない前提になるので）して使う事に不安があるため、機能を限定したGASを公開する方法を取っています。

読み出しシートの公開

今回、書き込み側はGASを使いますが、読み出し側は、シートをWEBページとして公開することで行います。

先のスプレッドシートで、「ファイル」⇒「共有」⇒「ウェブに公開」
とすすみます。

範囲をシート名、公開形式をウェブページにして公開します。

公開形式をテキストなどにすると、うまく情報がとれません。

URLは保存しておきましょう。

ここまでで、ArduinoIDEでプログラムを書くための前準備が済みました。

ラズベリーパイ Zero W の場合との違い

少し脇にそれますが、ラズベリーパイ Zero W　との違いを軽く触れます。

ラズベリーパイ Zero Wでは、以下のコードでアクセスとレスポンスが取れました。

#---WEBにリクエストを投じるためのライブラリ
import requests

・・・
レスポンス = requests.get("https・・・ＧＡＳへのアクセスＵＲＬ　？マクロコード"）

GASの側で工夫して、セルの値をレスポンスする様にしておけば、マクロコードで書き込み、レスポンスで値取得が容易にできました。

参考記事です。

ところが、「Pico W」では２つの問題があります。

HTTPSにアクセスする際のSSL認証を通過できない

PicoWにも、ネットのURLにアクセスするコードはあるのですが、単純なコードでは、HTTPSではじまるURLに固有の、SSL認証を通過できず、アクセスできません。

「Zero W」では、ブラウザとOSがこの役目を裏方で行っていたのですが、「Pico W」ではプログラムでユーザ自身が対応しなければなりません。

レスポンスを取れない

GASを動かす場合、PC上でレスポンスを見ると判りますが、アクセスしたURLと全く異なるURLからレスポンスを返しています。

https://script.googleusercontent.com/macros/e・・・から返しています。

こんな場合でも、「Zero W」では、ブラウザが上手くさばいて、レスポンスを取得できていたのですが、「Pico W」では全くレスポンスを感知できません。

対応方法

以上の問題に対応するため、SSL認証については、「ルート認証」と呼ばれるアクセスのための鍵を、ユーザ自身でプログラムに埋め込みます。また、レスポンス受信についてはあきらめ、セルの値は、スプレッドシート側で公開ページを作って取得する様にします。

さて、次の記事では、具体的なコードをご紹介していきます。

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

【GASでIoT】GASで「ラズパイ Pico W」から、Googleスプレッドシートへ の読み書きにトライ！（その4～テスト用スプレッドシートとGASの準備～）