【GASでIoT】GASで「ラズパイ Pico W」から、Googleスプレッドシートへの読み書きにトライ！（その6～スプレッドシートの読み書きを試す～）

Particlemethod

2023年5月14日 11:30

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

手元環境では「MicroPython」を使ったプログラムが上手く実装できなかったため、「C」言語で「Arduino IDE」を使ったプログラミングを進める事にしました。

サンプル・スケッチ（プログラム）を利用してLチカの実装をテストしました。

次いで、読み書きテストに使う、スプレッドシートとGASも用意しました。

更に、HTTPSクライアントとしての通信プログラムもテストしました。

今回は、通信プログラムを修正して、スプレッドシートとの読み書きにトライします。

イフト（IFTTT）などの中継システムやAPIサービスは使わない前提で考えます。

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

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

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

スプレッドシートの読み出し、書き込みは、それぞれ違う手段で対応する

RaspberryPiシリーズでも、「Zero」などのOSを有するデバイスの場合は、OSやブラウザがうまく対応して、Goole Apps Script（GAS）を使った読み出し、書き込みが一度にできました。

GASによる読み出し：読み出すセルの値をレスポンスする様に設定

GASによる書き込み：マクロコード（URLに続く”？・・・”部）付きでアクセスすると、その情報をセルに書き込む様に設定

クライアント側：以下の関数を用意すれば、読み出し、書き込みを同時に行えます。

レスポンス＝requestする関数（マクロコード付きURL）

別途POSTメソッドを発生させたり、APIを準備しなくても、とにかくGASを用意してデプロイURLにアクセスしさえすれば良いので、大変簡便に通信ができました。

例はこちらのシリーズ記事。

ところが、「Pico W」では同じ手法だと、読み込み側がうまくできません。

今回用意した、読み書きテスト用のスプレッドシートとGASを再掲します。

//-----------------------------------------
//----ラズパイのトライ-----------------------
//---著作：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●●●●');

  }
  
}

GASをデプロイし、PCなどからアクセスすると判りますが、レスポンスが返された場合のURLは、アクセスしたURLとは異なっています。

PCや「ラズベリーパイ Zero」では、URLを追いかけてレスポンスを取れたのですが、「Pico W」では残念ながら、別のURLから返されたレスポンスは取得できない様で、読み出しプロセスは失敗しました。

一方、マクロコード付きURLでアクセスしたことで、書き込みプロセスは成功しました。

そこで、以下の通信方法を取ることにします。

ラズベリーパイPico Wの対応：
　読み出し：シートを公開して公開URLから取得して対応
　書き込み：GASにマクロコード付きURLでアクセスして対応

上記方針に従って、Arduino IDE のサンプル・プログラムを修正します。

「Arduino IDE」のサンプルプログラムを修正

以下、他者の作成したプログラムを、改変して再配布するのはあまり勧められないこととされておりますので、今回の記事シリーズでは修正後のプログラムは掲載せず、「プログラムの修正部」を提示する方針です。

行番号などは修正途中で変わっていくため掲載せず、修正部を辿れる手がかりとなる前後のコードを記載します。

ご了承ください。

サンプル・プログラムの利用

「Arduino IDE」から、「ファイル」⇒「スケッチ例」を開くと、サンプルプログラムが表示されます。

Pico用のボードマネージャをインストールしていれば、「Raspberry Pi Pico Wのスケッチ例」というカテゴリが表示され、ここに「HTTPClient」というグループが見つかるはずです。

ここにある、「BasicHttpsClient」というサンプルを流用します。これを選択し、適当な名称で保存してください。

スケッチ（プログラム）の内容は、以下のGIT（プログラム情報などの保存サイト）のサイトでも、「BasicHttpsClient.ino」として公開されています。

プログラムの修正1～WiFiのアクセスポイントの修正

まず、WiFiのSSIDなどを打ち替えます。

以下の冒頭部分をご覧ください。

/**
   BasicHTTPSClient.ino

    Created on: 20.08.2018

*/

 #include  <Arduino.h> #include  <WiFi.h> #include  <HTTPClient.h>


★WiFiのSSIDとパスワードを打ち替える★
 #ifndef  STASSID #define  STASSID "your-ssid" #define  STAPSK "your-password" #endif 

・・・以下略・・・

上の最後の方に、"your-ssid"と "your-password"とある部分を、各自利用しているWiFiのSSIDとパスワードに打ち替えてください。

プログラムの修正2～ルート認証情報の修正

次に、HTTPSアクセスの鬼門、「ルート認証」の打ち替えです。

文字検索あるいはスクロールによって、-----BEGIN CERTIFICATE-----、
-----END CERTIFICATE-----、とある部分を確認ください。

この間の長い文字列が「ルート認証」で、これを、ご自身のプラウザから取得して打ち替えます。


const char *jigsaw_cert = R"EOF(
-----BEGIN CERTIFICATE-----
この間を打ち替え
-----END CERTIFICATE-----
)EOF";

ルート認証の取得と貼り付け

ルート認証の取得はPCなどのブラウザから取得します。（他の情報を見ても、認証は各自がそれぞれ取得して利用する説明になっているので、以下、方法を記します）

取得するには、先に用意したGASにPC環境で以下のURLからアクセスしてください。（最後の？以降はマクロコードで、「data=X」という情報を付加しています）

https://script.google.com/macros/s/★デプロイコード★/exec?data=X

すると、ブラウザにレスポンスが表示されます。

ブラウザのヘッダ部分に南京錠のアイコンがあります。これをクリックし、「この接続は保護されています」をクリックします。

ブラウザが「Chrom」の場合です。他のブラウザの場合は類似のメニューを試してください。

「この証明書は有効です」の後ろにある、四角と⇒のアイコンをクリックします。

「証明書ビューア」ダイアログが表示されるので「詳細」タブをクリックします。

ここで、「証明書の階層」「証明書のフィールド」から適当なものを選択して、「エクスポート」をクリックすることで、ルート認証の取得ができます。

自分の環境では、「GTS　CA　1C3」の階層で、「証明書」を選ぶ事で取得できましたが、いつもそうかは不明です。申し訳ないのですが、各自試してください。

うまく選択できていれば、拡張子が「．ｃｒｔ」という、30行程度のテキストファイルがダウンロードされます。（「．ｃｏｍ」などになっていれば、選択を間違っています）

ルート認証が、テキストエディタで開けますので、コピーして、さきほどの部分を書き直します。

紛らわしい「エッジ認証」

ややこしいのが、「エッジ認証」です。

誤った選択をした場合、もっと行数の多い「．ｃｒｔ」ファイルが取得されますが、これは「エッジ認証」というもので、利用できない情報です。

30行から大幅に長い場合は、他の項目を選択してください。

プログラムの修正3～一連のアクセス処理部分をコピーする

コピーする部分

今回は、書き込みと読み出しを別々のURLで行うために、HTTPSへのアクセス部分をコピーして、それぞれの機能を記載します。

コピーする部分は、メイン処理を記述している　loop（　）節のすぐ後ろから、

　void loop(　){
★ここから★

HTTPSアクセス終了を記述している以下の、delay(10000);　のコード部分までを、

https.end();
} else {
　Serial.printf("[HTTPS] Unable to connect\n");
　}
}
delay(10000);
★ここまで★

コピーして、直後に貼り付けます。

（貼り付けたあと、その部分が後で判る様に、「//リピート部分」などとコメントを入れてから行うことを推奨します）

void loop() {
★ここから★
  // wait for WiFi connection
  if ((WiFiMulti.run() == WL_CONNECTED)) {
    HTTPClient https;
    ・・・・・
    ・・・・・ 
    ・・・・・
      https.end();
    } else {
      Serial.printf("[HTTPS] Unable to connect\n");
    }
  }
delay(10000);
★ここまで★

//リピート部分↓
　★ここにコピー★
//リピート部分↑

同じ処理を2回くりかえして、書き込み、読み出しのプロセスを実行します。

書き込み部分の修正

アクセスURLの書き換え

HTTPSアクセス部分の記載のうち、コピー元となった前の部分を書き換えて、書き込みの機能を実装します。

以下のコードをGASへのアクセスURLに書き換えます。

if (　https.begin　("https:/・・書き換え・・"））｛

書き換えるURLの部分です。（★部分は各自★）

ｈttps://script.google.com/macros/s/★デプロイID★/exec?data=X

    ・・・・
　　Serial.print("[HTTPS] begin...\n");　
　　★"https://以下を書き換え　
    if (https.begin("https://script.google.com/macros/s/★デプロイID★/exec?data=X")) {  // HTTPS
      Serial.print("[HTTPS] GET...\n");
   ・・・・

今回はテストのため、マクロコード部分は「data=X」で固定しています。（このマクロコードによりセルに「X」が記入されます）

レスポンス表示部分の書き換え

また、その直後、レスポンス（変数名payload）を表示させている以下の部分を、

Serial.println(payload);

以下に書き換えます。

payload = payload.substring(payload.indexOf("▼▼▼▼"),payload.indexOf("●●●●")); Serial.println(payload.substring(12));

・・・前略・・・ 

String payload = https.getString();

★書き換え部分
 //Serial.println(payload);

★以下にする
payload = payload.substring(payload.indexOf("▼▼▼▼"),payload.indexOf("●●●●"));
Serial.println(payload.substring(12));

        }

・・・略・・・

あとで解説しますが、うまくレスポンスが取得できれば、そのまま表示するのではなく、「▼▼▼▼」と「●●●●」に挟まれた部分を抽出して表示するためのおまじないです。

読み出し部分の修正

また、コピー先である、後半部分も同様に書き換えます。

アクセスURLの書き換え

書き込みと同様に、まず、アクセスするURLを修正します。URLは、スプレッドシートの公開ページのものです。

（後段部分）
if (　https.begin　("https:/・・書き換え・・"））｛

書き換えるURLの部分です。（★部分は各自★）

ｈttps://docs.google.com/spreadsheets/d/e/★公開EWBページID★/pub?gid=0&single=true&range=A1

    ・・後段部分・・
　　Serial.print("[HTTPS] begin...\n");　
　　★"https://以下を書き換え　
    if (https.begin("https://docs.google.com/spreadsheets/d/e/★公開EWBページID★/pub?gid=0&single=true&range=A1")) {  // HTTPS
      Serial.print("[HTTPS] GET...\n");
   ・・・・

URLの最後の　&range=A1　部分は、A1セルのみの値を参照するためのマクロコードです。

レスポンス表示部分の書き換え

レスポンスの表示部分は、前段部分とまったく同じ書き換えをします。

・・・後段部分・・・ 

String payload = https.getString();

★書き換え部分
 //Serial.println(payload);

★以下にする
payload = payload.substring(payload.indexOf("▼▼▼▼"),payload.indexOf("●●●●"));
Serial.println(payload.substring(12));

        }

・・・略・・・

その他、必要に応じて、シリアルモニタへのコメントの表示を修正追加しておくと良いかもしれません。

ここまでできたら、ArduinoIDEでヘッダの「⇒」ボタンを押して、コンパイルと「Pico W」への書き込みをします。

ボードマネージャは「RaspberryPicoW」を選び、適切なポートを選択しておいてください。

実行結果の確認

書き込みがすんだら（ArduinoIDE全般の特徴ですが、相当時間がかかります）自動的にコードが実行されます。

「ツール」からシリアルモニタを選択して、動作状況を確認してみましょう。

GASへのアクセス部分

WiFiにつながると、以下の様にモニタに、SSLへのアクセスを試みている記述が表示され始めます。

しばらくすると、前段のルーチンである、GASへのアクセス部分が表示されますが、この部分では、以下の様に「302」というエラーコードが出されて、レスポンスが表示されません。

これは、アクセスしたURLが自動的に他のURLに転送され、追いかけられなかったことを示しています。

しかし、このルーチンで、スプレッドシートのA2セルに、「X」という文字が記載されるのが確認できるはずです。

つまり、GASを使ってアクセスすると、書き込みはできるが、読み出しがうまくできない事が判ります。

スプレッドシートの公開WEBページへのアクセス部分

後段のスプレッドシートの公開WEBページへのアクセス部分はどうでしょうか。

みごと、A1セルのテキストの「▼▼▼▼」「●●●●」で挟まれた部分が表示されました！

つまり、公開WEBページへアクセスすると、読み出しが出来ることが確認されました。

レスポンスの表示部分について

後段で受けたレスポンス部分は、実は多くの書式情報を含んでいます。

そのまま表示させると、以下の様になります。

ここから、本当に必要な情報を抽出するため、少々工夫しています。

フラグを元に抽出

セルの情報は、「▼▼▼▼」「●●●●」で挟んで記述しています。これをフラグとして、必要部分だけを以下で抽出しています。

payload = payload.substring(payload.indexOf("▼▼▼▼"),payload.indexOf("●●●●"));

文字位置を調査する関数で、文字抽出を行う関数「．substring（最初の位置、最後の位置）」を付加して、必要な情報を切り出しています。

文字位置は、文字位置検索を行う関数「.indexOf(検索文字)」を使っています。

ただし、得られたデータの頭にゴミが残っている様なので、さらに前から12文字分を切り捨てて、以下のコードで表示しています。

Serial.println(payload.substring(12));

うまく機能したでしょうか！

「Pico W」を「GAS」と組み合わせたプログラムの情報は少なく、試行錯誤で出来た方法のため、うまく再現できない場合があることをご了承頂き、自己解決を前提にお試し願います。

ループさせていると、1～2割は何らかの理由でレスポンスの取得にしくじる場合がありますが、理由はよく分かりません。

さて、このRaspberryPicoWを使った理由は、GASの情報を、LEDなどで表示させたいためでした。

次回、テスト通信の追加事項として、NeoPixcelというLEDテープを「ArduinoIDE」を使ったプログラムで光らせてみます。

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

【GASでIoT】GASで「ラズパイ Pico W」から、Googleスプレッドシートへ の読み書きにトライ！（その6～スプレッドシートの読み書きを試す～）

スプレッドシートの読み出し、書き込みは、それぞれ違う手段で対応する

「Arduino IDE」のサンプルプログラムを修正

サンプル・プログラムの利用

プログラムの修正1～WiFiのアクセスポイントの修正

プログラムの修正2～ルート認証情報の修正

ルート認証の取得と貼り付け

紛らわしい「エッジ認証」

プログラムの修正3～一連のアクセス処理部分をコピーする

コピーする部分

書き込み部分の修正

アクセスURLの書き換え

レスポンス表示部分の書き換え

読み出し部分の修正

アクセスURLの書き換え

レスポンス表示部分の書き換え

実行結果の確認

GASへのアクセス部分

スプレッドシートの公開WEBページへのアクセス部分

レスポンスの表示部分について

フラグを元に抽出

【GASでIoT】GASで「ラズパイ Pico W」から、Googleスプレッドシートへの読み書きにトライ！（その6～スプレッドシートの読み書きを試す～）