M5StackからGoogleスプレッドシートに送信

M5Stack

M5StackからGoogleDriveのスプレッドシートに送信して、センサの測定データなどを自動的に収集できるようにしました。Google Apps Scriptを使うのは初めてです。

数百のデータを一気に送る方法をネットで検索してみたのですが、jsonで少量のデータを散発的に送っているケースが多く、用途に合ったものを見つけることができません。仕方ないので自分で作ってみることにしたところ、何とか動くようになり、その後も便利に使っています。

M5Stack:変数にカンマ区切りでファイル名もデータも全部いれてしまう

以下がM5Stackのプログラムです。ライブラリにWiFiClientSecureを準備して、最初の方にある、ssid, password, exec_urlを設定すると動きます。ssid, passwordはご利用の環境にあわせて設定してください。exec_urlは、Google Drive側で「ウェブアプリケーションとして公開」すると決まります(後述)。 Google Driveにスクリプトを準備・公開して、M5Stackを起動すると、WiFiに接続し、Googleにデータを送信します。

  • String型の変数valuesを準備して、ここに以下をカンマ区切りで全部入れてしまう。最初にファイル名、2番目にシート名、3番目に送るデータの項目数(スプレッドシートでは列数になる)。続けてカンマ区切りで、データ本体。下のサンプルでは3項目のデータを3行分送っています。実験してみたら300行は問題なく送れました。
  • WiFiClientSecureでWiFiに接続し、Googleにコマンドを送ってvaluesをPOSTする。
#include <M5Stack.h>
#include <WiFiClientSecure.h>

//****** ネットワーク関連 ******
const char* ssid     = "xxxxxx";   // your network SSID (name of wifi network)
const char* password = "xxxxxx";    // your network password

const char* host = "script.google.com";
String exec_url = "https://script.google.com/macros/s/xxxxxxxxxxx/exec";
//ウェブアプリケーションのURL

WiFiClientSecure client;
String values;  //送信するデータ

String postValues(String values_to_post) {
  if (client.connect(host, 443)){
    LcdInit();
    M5.Lcd.println("Posting data...");
    
    client.println("POST " + exec_url + " HTTP/1.1");
    client.println("HOST: " + (String)host);
    client.println("Connection: close");
    client.println("Content-Type: text/plain");
    client.print("Content-Length: ");
    client.println(values_to_post.length());
    client.println();
    client.println(values_to_post);
    delay(100);

    while (client.available()) {
      char c = client.read();
      M5.Lcd.print(c);
    }
    client.stop();
    delay(1000);
    return "post end";
  } 
  else {
    return "ERROR";
  }
}

void connectingWiFi(){

  boolean WiFiOn;  // WiFi接続したらtrue
  int n_trial, max_trial = 10;  //WiFi接続試行回数とその上限

  WiFi.mode(WIFI_STA);
  WiFi.disconnect();
  WiFi.begin(ssid, password);
  LcdInit();
  M5.Lcd.print("Connecting to WiFi");
  // attempt to connect to Wifi network:
  n_trial = 0;
  while (WiFi.status() != WL_CONNECTED && n_trial < max_trial) {
    M5.Lcd.print(".");
    // wait 1 second for re-trying
    n_trial++;
    delay(1000);
  }
  LcdInit();
  if (WiFi.status() == WL_CONNECTED)
    {WiFiOn = true;
    M5.Lcd.print("Connected to ");
    M5.Lcd.println(ssid);
    M5.Lcd.print("IP: ");
    M5.Lcd.println(WiFi.localIP());  }
  else
    {WiFiOn = false;
    M5.Lcd.print("WiFi connection failed");  }
  delay(1000);
}

void LcdInit(){
  M5.Lcd.clear(BLACK);
  M5.Lcd.setTextSize(2);
  M5.Lcd.setTextColor(WHITE, BLACK);
  M5.Lcd.setCursor(0, 0);
}

void setup() {
  M5.begin();
  LcdInit();
  M5.Lcd.print("Send data to Google Spreadsheet\n");
  delay(1000);
  connectingWiFi();
}

void loop() {
  LcdInit();
  M5.Lcd.print("MainLoop\n");

  values = "FilenameA, SheetnameA, 3,";   //ファイル名、シート名、データ列数、
  values += "1, 10, 20, 2, 12, 28, 3, 20, 30";     //以後データ本体
  String response = postValues(values);

  M5.Lcd.print("Response : ");
  M5.Lcd.println(response);
  delay(1000);
  WiFi.disconnect();
  while(1);
}

GAS : データを受信して、スプレッドシートに記録し、最後にグラフを作成

続いて、Google App Scriptの内容です。最初の方にある、var folder = DriveApp.getFolderById(‘xxxx’);の xxxx の部分を、スプレッドシートを保存するGoogle Driveのフォルダ(作業フォルダ)のフォルダIDに変更してください。
スクリプトは、以下のような内容になっています。

  • 変数aryにM5Stackから受信したデータを格納。ary[0]がファイル名(スクリプトではfileName)、ary[1]がシート名(同sheetName)、ary[2]がデータの列数(項目数)(同n_of_colms)となる。ary[3]以下にデータ本体が続く。
  • ルートフォルダにファイル名がfileNameのスプレッドシートを作成。作業フォルダ(スクリプト中にフォルダIDを記述)の中に、同じファイル名のスプレッドシートがあれば、そのファイルのID取得。同じファイル名のスプレッドシートがない場合は、ルートに作成したスプレッドシートを作業フォルダにコピー。ルートに作成したスプレッドシートを削除。
  • 作業フォルダのスプレッドシートfileName中に、シート名sheetNameのシートがなければ新規作成。シート名sheetNameがあれば、データを上書きでなく追記するために、入力済行数を取得する。
  • シートsheetNameに書き込む準備。n_of_colmsで指定した列数(項目数)ずつ組にしながら、ary[3]以下のデータ本体を新しい2次元配列ary2[0][0], ary2[0][1], ary2[0][2]・・・にコピー。
  • 配列ary2をnewRange.setValues(ary2);でシートsheetNameに書き込み。
  • グラフのデータ範囲を設定した後、古いグラフをすべて削除し、新たにグラフを作成。
function doPost(e){
  var param = e.postData.getDataAsString();//データ取得
  var ary = param.split(',');//取得データをカンマで区切ってaryに格納

  var fileName = ary[0];//aryの最初はファイル名(SpreadSheet)
  var sheetName = ary[1];//次にシート名
  var n_of_colms = ary[2];//次に項目数(列数)
  var folder = DriveApp.getFolderById('xxxx');//作業フォルダは固定にする:ここにフォルダIDを記入

  var SS_ID = SpreadsheetApp.create(fileName).getId();//ルートにファイル名fileNameで新規スプレッドシート作成
  var SS = DriveApp.getFileById(SS_ID);// 新規作成スプレッドシートのIDを取得

  var files = folder.getFilesByName(fileName);//作業フォルダ内にfileNameと同じ名前のスプレッドシートが存在するか?
  if (files.hasNext()) {
    SS_ID = files.next().getId();//存在する場合はIDをそのファイルに書き換え
  }
  else{
    folder.addFile(SS);//存在しない場合はルートのファイルをコピー
  }
  DriveApp.getRootFolder().removeFile(SS);//ルートに作成したスプレッドシートは不要なので削除

  var ary_length = ary.length;
  var n_of_data = Math.round((ary_length - 3) / n_of_colms); //データの行数(組数)

  var spreadsheet = SpreadsheetApp.openById(SS_ID);//あらためてSpreadSheetを開く
  var newSheet = spreadsheet.getSheetByName(sheetName);//同じシート名があるかチェック
 if(!newSheet){
   newSheet= spreadsheet.insertSheet(sheetName,0);//シートがなければ新規作成
  }

  newSheet.activate();
  var columnA_Vals = newSheet.getRange('A:A').getValues();
  var LastRow = columnA_Vals.filter(String).length;  //空白を除き、入力済の行数を取得

  var ary2 = [];//シートに書き込むための配列
  for(var i=0;i<n_of_data;i++){
    ary2[i] = [];      //まず1次元の配列にして
    for(var j=0;j<n_of_colms;j++){  //その中にさらにデータを格納して2次元にする
      ary2[i][j]=ary[i*n_of_colms + j + 3];//ary[0]はファイル名、1はシート名、2は列数なので3を足している
    }
  }
  var newRange = newSheet.getRange(LastRow+1,1,n_of_data,n_of_colms);//入力済の次の行から、入力範囲を設定
  newRange.setValues(ary2);  //ary2の内容を一気に書き込み

  var graphRange = newSheet.getRange(1,1,LastRow+n_of_data,n_of_colms);//グラフ作成するデータの範囲
  var oldCharts = newSheet.getCharts();  //シートにあるすべてのグラフ
  for (var i in oldCharts) {//グラフの数だけ繰り返す
	// シートからそのグラフを削除:古いグラフはすべて削除される
    sheet.removeChart(charts[i]);
  }

  var myChart = newSheet.newChart()// 最新のグラフの作成
  .addRange(graphRange)
  .setChartType(Charts.ChartType.SCATTER)
  .setPosition(1,n_of_colms,50,200)
  .build();

  newSheet.insertChart(myChart);
}

これをGoogleドライブにGoogle Apps Scriptとして保存し、ウェブアプリケーションとして公開します。公開の際に発行されるURLを、M5Stack側の exec_url に設定してください。

Google側での設定方法がわかるように、動画を作成しました(プログラムの内容が少しだけこのページとは違います)。

以下が今回参考にしたサイトです。ありがとうございました。

【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアル

こんな感じで使っています

実際には、ファイル名に日付をいれ、シート名を測定開始時刻として使用することが多いです。例えば2020年5月11日の17:50:19に開始した温度データであれば、ファイル名TempData_200511、シート名175019としています。

時刻がわかるような動画を撮影しておけば、グラフと簡単に同期がとれるので便利です。こちらのプログラムも別記事に載せ、動画も作成しました。

コメント

  1. 藤原 より:

    YouTube拝見しとても勉強になっています。M5StackからGoogleスプレッドシートにデータを記録する方法試させていただきました。前のやり方、新しいやり方共に試しましたが、いずれもhostへの接続でエラーが起こってしまうようです。WiFiは接続できています。Googleのスクリプトも自信ありませんがまだこの時点ではそちたの問題ではないと思います。

    const char* host = “script.google.com”;
    const int httpsPort = 443;

    間違いよう無いと思うのですが…。
    もしおわかりになりましたらご教授ください。
    オフグリットもン酸鉄リチウムバッテリーも楽しく拝見しています。

    • じょーじ より:

      コメントありがとうございます。
      申し訳ありませんが、私もGoogleへの接続関連については、アドヴァイスできるほどの知識がありません。実際、ブログには書いていないトラブルを何度も経験しています。エラーが起きても、どこに問題があるのかわかりにくいですよね。
      近日中に、2021年版のプログラムもこちらに公開する予定です。YouTubeとあわせ、今後もご覧いただければ幸いです。

      • 中西 より:

        じょーじさんはじめまして
        youtubeも含め大変勉強させていただいております
        当方も苦戦しておりまして。。。

        2021年版期待してお待ちしております

        • じょーじ より:

          公開遅れまして、すみません。もう2022年になってしまいましたね。
          とりあえず古いものでデータが取れてしまっているので、後回しになっています。
          もう一度、勉強し直さないといけなそうです。

タイトルとURLをコピーしました