エクセルのクラウド版ともいうべき、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

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

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

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

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

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

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

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

-----BEGIN CERTIFICATE-----
MIIFVzCCAz+gAwIBAgINAgPlk28xsBNJiGuiFzANBgkqhkiG9w0BAQwFADBHMQsw
CQYDVQQGEwJVUzEiMCAGA1UEChMZR29vZ2xlIFRydXN0IFNlcnZpY2VzIExMQzEU

・・・・
（30行程度）
・・・・
Z6tGn6D/Qqc6f1zLXbBwHSs09dR2CQzreExZBfMzQsNhFRAbd03OIozUhfJFfbdT
6u9AWpQKXCBfTkBdYiJ23//OYb2MI3jSNwLgjt7RETeJ9r/tSQdirpLsQBqvFAnZ
0E6yove+7u7Y/9waLd64NnHi/Hm3lCXRSHNboTXns5lndcEZOitHTtNCjv0xyBZm
2tIMPNuzjsmhDYAPexZ3FL//2wmUspO8IFgV6dtxQ/PeEMMA3KgqlbbC1j+Qa3bb
bP6MvPJwNQzcmRk13NfIRmPVNnGuV/u3gm3c
-----END CERTIFICATE-----
)EOF";

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

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

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

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

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

コピーする部分

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

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

HTTPSアクセス終了を記述している以下の、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に書き換えます。

書き換える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）を表示させている以下の部分を、

以下に書き換えます。

・・・前略・・・ 

String payload = https.getString();

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

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

        }

・・・略・・・ 

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

読み出し部分の修正

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

アクセスURLの書き換え

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

書き換える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」への書き込みをします。

実行結果の確認

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

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

GASへのアクセス部分

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

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

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

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

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

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

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

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

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

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

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

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

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

フラグを元に抽出

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

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

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

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

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

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

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