Google スプレッドシートの利用

• 1: Google スプレッドシートの利用

1 - Google スプレッドシートの利用

ソースコードの入手

GitHub (monowireless/spot-google-sheets) から入手できます。

システムの概要

TWELITE SPOT は、事前に作成したサービスアカウントを使って自動的にスプレッドシートを作成し、指定したユーザアカウントへ、そのファイルを共有します。

ユーザアカウントへログインすると、Google ドライブの「共有アイテム」ページから、TWELITE SPOT によって作成されたスプレッドシートを閲覧・編集できます。

TWELITE SPOT は、作成したスプレッドシートへデータ行を次々と追加していきます。

開発に必要なもの

• 無線LANゲートウェイ TWELITE SPOT
  • 電源用 USB-C ケーブル
  • USB AC アダプタ（1A 以上供給できるもの）
• 温湿度センサー無線タグ TWELITE ARIA （お持ちでない場合はご購入ください 👉 販売店一覧）
  • CR2032 コイン電池
• USBアダプター TWELITE R3 （お持ちでない場合はご購入ください 👉 販売店一覧）
  • 通信用 USB-C ケーブル
• 💻 開発用コンピュータ
  • Google アカウント

環境整備

IDE とツールチェインの導入

Arduino IDE 1.x による開発環境の構築方法 をご覧ください。

ライブラリの導入

ESP-Google-Sheet-Client ライブラリ

ライブラリマネージャを開き、検索ボックスに esp-google-sheet と入力してインストールします。

なお、GitHub (mobizt/ESP-Google-Sheet-Client) からも入手できます。

公式 NTP ライブラリ

ライブラリマネージャを開き、検索ボックスに ntpclient と入力してインストールします。

TimeLib ライブラリ

ライブラリマネージャを開き、検索ボックスに timelib と入力してインストールします。

事前準備：API のセットアップ

事前に、API を使用できるように準備する必要があります。Google アカウントを使います。

ここでは、下記の作業を行います。

• Google Cloud プロジェクトの作成
• Google Sheets API の有効化
• Google Drive API の有効化
• サービスアカウントの作成と設定
• サービスアカウントの認証情報の取得

プロジェクトの作成

API を使用するにあたって、まずは Google Cloud プロジェクトを作成します。

Google Cloud プロジェクトは、システム全体を束ねるような存在です。構築するシステムの名称をプロジェクト名にするとよいでしょう。ここでは、仮に SPOT-DEV とします。

下記のリンクにアクセスし、プロジェクトを作成してください。

https://console.cloud.google.com/projectcreate

Sheets API の有効化

TWELITE SPOT からスプレッドシートを操作するために、Sheets API を有効化します。

下記のリンクにアクセスし、API を有効化してください。

https://console.cloud.google.com/apis/library/sheets.googleapis.com

Drive API の有効化

TWELITE SPOT からスプレッドシートを共有するために、Drive API を有効化します。

下記のリンクにアクセスし、API を有効化してください。

https://console.cloud.google.com/apis/library/drive.googleapis.com

サービスアカウントの作成と設定

TWELITE SPOT からスプレッドシートを作成するために、サービスアカウントを作成します。

下記のリンクにアクセスし、プロジェクト名（ここでは SPOT-DEV ）を選択してサービスアカウント一覧画面を表示したのち、ページ上部のボタンからサービスアカウントの作成を開始します。

https://console.cloud.google.com/iam-admin/serviceaccounts

「① サービスアカウントの詳細」では、サービスアカウントの名称を入力します。

下記の例では、spot-dev-sa としています。

入力したら、「作成して続行」ボタンを押して次へ進みます。

「② このサービスアカウントにプロジェクトへのアクセスを許可する（省略可）」では、サービスアカウントの権限を設定します。

ここでは、下記の例のようにして「オーナー」を選択してください。

選択したら、「続行」ボタンを押して次へ進みます。

「③ ユーザーにこのサービスアカウントへのアクセスを許可（省略可）」では、何も行わずに「完了」を押してスキップします。

サービスアカウントの作成が完了すると、サービスアカウントの一覧画面へ戻ります。作成したサービスアカウントが表示されていることを確認してください。

サービスアカウントの認証情報の取得

作成したサービスアカウントを確認したら、「メール」列のリンクをクリックし、サービスアカウントの詳細画面へ移ります。

上部の「キー」タブを選択して、サービスアカウントの認証に必要な秘密鍵を管理する画面へ移ります。

「鍵を追加」ボタンから「新しい鍵を作成」を選択し、秘密鍵の作成を開始します。

次の画面では、「JSON」を選択した状態のまま「作成」ボタンを押します。

「作成」ボタンを押すと、秘密鍵ファイル（.json）が自動的にダウンロードされます。

秘密鍵ファイルをテキストエディタで開くと、下記のような構成になっているはずです。

{
  "type": "service_account",
  "project_id": "???",
  "private_key_id": "???",
  "private_key": "-----BEGIN PRIVATE KEY-----\n???\n-----END PRIVATE KEY-----\n",
  "client_email": "???@???.iam.gserviceaccount.com",
  "client_id": "???",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "???",
  "universe_domain": "googleapis.com"
}

上記のうち、project_id / private_key / client_email の内容を動作確認で使用します。

動作確認

まずは動作確認を行ってみましょう。

プロジェクトファイルを入手

1. GitHub (monowireless/spot-google-sheets) から Zip ファイルをダウンロードします
2. Zip ファイルを展開し、フォルダ名を spot-google-sheets-main から spot-google-sheets に変更します
3. Arduino のスケッチブックの保存場所（Arduino IDE 環境設定に記載。例：C:\Users\foo\Documents\Arduino）に spot-google-sheets フォルダを配置します

スケッチの設定ファイルを修正

Arduino スケッチ spot-google-sheets.ino を開き、画面上部の config.h タブを選択して、4-11行目の値を修正してください。

4-5行目は、無線 LAN 関連の設定です。

const char* WIFI_SSID = "YOUR SSID";            // Modify it
const char* WIFI_PASSWORD = "YOUR PASSWORD";    // Modify it

SSID と パスワードを設定しています。

一方、8-11行目はスプレッドシート関連の設定です。

const char* PROJECT_ID = "YOUR-PROJECT-ID";                                                                         // Modify it
const char* SERVICE_ACCOUNT_EMAIL = "YOUR-SERVICE-ACCOUNT@YOUR-PROJECT-ID.iam.gserviceaccount.com";                 // Modify it
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\nYOUR-PRIVATE-KEY\n-----END PRIVATE KEY-----\n";    // Modify it
const char* USER_ACCOUNT_EMAIL = "YOUR-ACCOUNT@EMAIL";                                                              // Modify it

最初の3項目には .json ファイルの内容をコピーして、最後の USER_ACCOUNT_EMAIL にはあなたがログインしている Google アカウントのメールアドレスを入力してください。

スケッチを書き込み

ESP32 へのスケッチの書き込み方法 を参考に、スケッチを書き込んでください。

親機と子機を起動

TWELITE SPOT のリセットボタン（ESP32 側）を押してください。

Arduino のシリアルコンソールに以下のような表示がされたら、起動に成功しています。

Initializing queue...
Completed.
Started TWELITE.
Connecting to WiFi ...!...
Connected. IP: xxx.xxx.xxx.xxx
Initializing NTP...Completed. UNIX time: xxxxxxxxxx
Initializing sheets...
Creating sheets...
OAuth2.0 access token on initializing
OAuth2.0 access token on signing
OAuth2.0 access token on exchange request
OAuth2.0 access token ready
Requesting to create...
Succeeded.
Adding headers for ARIA...
Requesting to add header...
Succeeded.
Formatting the sheet for ARIA...
Requesting to format...
Succeeded.
Extending the sheet for ARIA...
Requesting to extend...
Succeeded.
Completed.

TWELITE ARIA（初期設定）にもコイン電池を挿入し、電源を投入します。

TWELITE SPOT が TWELITE ARIA からのパケットを正常に受信し、データ列の追加に成功すると、下記のような表示がなされます。

Got a new packet from ARIA.
Got a new packet from ARIA.
Requesting to add data...
Got a new packet from ARIA.
Succeeded.

ちなみに、上記の例ではリクエスト中にパケットを受信しています。後述のマルチタスクに成功している証です！

Google へアクセス

Google ドライブの 共有アイテム へアクセスし、SPOT Sheet (xxx) という名前のスプレッドシートを開きます。

以下のような画面が表示されます。

スクロールしていくと、TWELITE ARIA からのデータを確認できるはずです。

スケッチ解説

Arduino スケッチ spot-google-sheets.ino の解説です。

ライブラリのインクルード

Arduino および ESP32 公式ライブラリ

4-7行目では、Arduino および ESP32 の公式ライブラリをインクルードしています。

#include <Arduino.h>
#include <NTPClient.h>
#include <WiFi.h>
#include <WiFiUdp.h>

サードパーティのライブラリ

10-11行目では、サードパーティのライブラリをインクルードしています。

#include <ESP_Google_Sheet_Client.h>
#include <TimeLib.h>

MWings ライブラリ

14行目では、MWings ライブラリをインクルードしています。

#include <MWings.h>

ユーザ設定の定義

17行目では、config.h をインクルードしています。

#include "config.h"

無線 LAN 設定の定義

config.h の4-5行目では、TWELITE SPOT に搭載された ESP32 に適用する無線 LAN 設定を定義します。

const char* WIFI_SSID = "YOUR SSID";            // Modify it
const char* WIFI_PASSWORD = "YOUR PASSWORD";    // Modify it

API 設定の定義

config.h の8-11行目では、API の設定を定義しています。

const char* PROJECT_ID = "YOUR-PROJECT-ID";                                                                         // Modify it
const char* SERVICE_ACCOUNT_EMAIL = "YOUR-SERVICE-ACCOUNT@YOUR-PROJECT-ID.iam.gserviceaccount.com";                 // Modify it
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\nYOUR-PRIVATE-KEY\n-----END PRIVATE KEY-----\n";    // Modify it
const char* USER_ACCOUNT_EMAIL = "YOUR-ACCOUNT@EMAIL";                                                              // Modify it

ピン番号の定義

20-24行目では、ピン番号を定義しています。

const uint8_t TWE_RST = 5;
const uint8_t TWE_PRG = 4;
const uint8_t LED = 18;
const uint8_t ESP_RXD1 = 16;
const uint8_t ESP_TXD1 = 17;

TWELITE 設定の定義

27-30行目では、TWELITE SPOT に搭載された TWELITE 親機に適用する設定を定義しています。

const uint8_t TWE_CH = 18;
const uint32_t TWE_APPID = 0x67720102;
const uint8_t TWE_RETRY = 2;
const uint8_t TWE_POWER = 3;

シート関連の定義

32-43行目では、シートに関連した情報を定義しています。

const char* SPREADSHEET_TITLE_PREFIX = "SPOT Sheet";
const char* SPREADSHEET_LOCALE = "ja_JP";
const char* SPREADSHEET_TIME_ZONE = "Asia/Tokyo";

const int MIN_REQUEST_INTERVAL = 1000;    // 60 requests per minute

const int SHEETS_DEFAULT_ROWS = 1000;            // Default length is 1000 rows
const int SHEETS_ROWS = 100000;                  // Max 1,000,000 rows at 10 columns

const int ARIA_SHEET_ID = 1;
const char* ARIA_SHEET_TITLE = "ARIA";
constexpr int ARIA_BUFFER_PACKETS = 32;    // Max number of rows per addition request

型の宣言

46-50行目では、型を宣言しています。

struct ParsedAppAriaPacketWithTime {
    ParsedAppAriaPacket packet;
    uint32_t elapsedMillis;
    uint32_t unixTime;
};

• elapsedMillis ：パケット受信時の起動からの経過時間（ミリ秒）
• unixTime ：パケット受信時の UNIX 時間（秒）です。

グローバルオブジェクトの宣言

53-61行目では、グローバルオブジェクトを宣言しています。

WiFiUDP ntpUDP;
NTPClient timeClient(ntpUDP, "ntp.nict.jp", 32400);

String spreadsheetIdString;    // Identifier of newly created file
bool readyForNewRequests = false;
uint32_t lastTimeRequestWasSent = UINT32_MAX;

QueueHandle_t ariaPacketQueue;       // Store received data from ARIA
uint32_t rowToAddNewAriaData = 2;    // Starting with the Row 2

関数プロトタイプの宣言

64-71行目では、関数プロトタイプを宣言しています。

void anotherLoop();

void waitUntilNewRequestsReady();
String createSpreadsheet();
bool formatSheet(const String spreadsheetId, const int sheetId);
bool extendSheet(const String spreadsheetId, const int sheetId, const int rows);
bool addSheetAriaHeaderRow(const String spreadsheetId, const char* const sheetTitle);
bool addSheetsDataRow(const String spreadsheetId);

キューの設定

82-83行目では、受信したパケットデータを受信時刻と合わせて格納するためのキューを初期化しています。

ariaPacketQueue = xQueueCreate(ARIA_BUFFER_PACKETS, sizeof(ParsedAppAriaPacketWithTime));
if (ariaPacketQueue == 0) { Serial.println("Failed to init a queue."); }

xQueueCreate() は、ESP32 の内部で動作する FreeRTOS の機能です。マルチタスクに対応したキューを簡単に作成することができます。

TWELITE の設定

88-92行目では、Twelite.begin() を呼び出し、TWELITE SPOT に搭載された TWELITE 親機の設定と起動を行っています。

Serial2.begin(115200, SERIAL_8N1, ESP_RXD1, ESP_TXD1);
    if (Twelite.begin(Serial2,
                      LED, TWE_RST, TWE_PRG,
                      TWE_CH, TWE_APPID, TWE_RETRY, TWE_POWER)) {
        Serial.println("Started TWELITE.");
    }

イベントハンドラの登録

94-103行目では、TWELITE ARIA からのパケットを受信した際に行う処理を登録しています。

Twelite.on([](const ParsedAppAriaPacket& packet) {
    Serial.println("Got a new packet from ARIA.");
    ParsedAppAriaPacketWithTime packetWithTime;
    packetWithTime.elapsedMillis = millis();
    packetWithTime.unixTime = timeClient.getEpochTime();
    packetWithTime.packet = packet;
    if (not(xQueueSend(ariaPacketQueue, &packetWithTime, 0) == pdPASS)) {
        Serial.println("Failed to add packet data to the queue.");
    }
});

ここでは、xQueueSend() により、受信したパケットデータを受信時刻と合わせてキューの末尾へ格納しています。

無線 LAN の設定

106-120行目では、無線 LAN の設定を行っています。

WiFi.mode(WIFI_STA);
WiFi.setAutoReconnect(true);
WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
Serial.print("Connecting to WiFi ..");
while (WiFi.status() != WL_CONNECTED) {
    static int count = 0;
    Serial.print('.');
    delay(500);
    // Retry every 5 seconds
    if (count++ % 10 == 0) {
        WiFi.disconnect();
        WiFi.reconnect();
        Serial.print('!');
    }
}

ここでは、無線 LAN 子機として設定したうえで、指定のネットワークへ接続しています。

NTP の設定

126-127行目では、NTP の設定をしています。

timeClient.begin();
timeClient.update();

Google スプレッドシートの設定

132-145行目では、Google スプレッドシートを設定しています。

GSheet.setTokenCallback([](TokenInfo info) {
    // Print token initialization states
    if (info.status == esp_signer_token_status_error) {
        Serial.print("Token error ");
        Serial.println(GSheet.getTokenError(info));
    }
    Serial.print(GSheet.getTokenType(info));
    Serial.print(" ");
    Serial.println(GSheet.getTokenStatus(info));
});
GSheet.setPrerefreshSeconds(60);    // Set refresh rate for auth token

Serial.println("Initializing sheets...");
GSheet.begin(SERVICE_ACCOUNT_EMAIL, PROJECT_ID, PRIVATE_KEY);

132-141行目でサービスアカウントのトークンを取得する際の状態表示の処理を登録したあと、142行目でトークンの再取得間隔を設定、145行目でサービスアカウントの初期化をしています。

また、147-173行目では、スプレッドシートの作成、ARIA 向けヘッダー行の追加、セルの書式設定、そして行の拡張を行っています。

Serial.println("Creating sheets...");
waitUntilNewRequestsReady();    // Wait for token
spreadsheetIdString = createSpreadsheet();
if (not(spreadsheetIdString.length() > 0)) {
    Serial.println("Failed to create sheets.");
}

Serial.println("Adding headers for ARIA...");
delay(MIN_REQUEST_INTERVAL);
waitUntilNewRequestsReady();
if (not addSheetAriaHeaderRow(spreadsheetIdString, ARIA_SHEET_TITLE)) {
    Serial.println("Failed to add headers.");
}

Serial.println("Formatting the sheet for ARIA...");
delay(MIN_REQUEST_INTERVAL);
waitUntilNewRequestsReady();
if (not formatSheet(spreadsheetIdString, ARIA_SHEET_ID)) {
    Serial.println("Failed to format.");
}

Serial.println("Extending the sheet for ARIA...");
delay(MIN_REQUEST_INTERVAL);
waitUntilNewRequestsReady();
if (not extendSheet(spreadsheetIdString, ARIA_SHEET_ID, SHEETS_ROWS - SHEETS_DEFAULT_ROWS)) {
    Serial.println("Failed to extend.");
}

タスクの登録

179-186行目では、TWELITE のデータを非同期で更新するためのタスクを登録しています。

xTaskCreatePinnedToCore(
    [](void* params) {
        while (true) {
            anotherLoop();
            vTaskDelay(1);
        }
    },
    "Task for anotherLoop()", 8192, nullptr, 18, nullptr, 0);

xTaskCreatePinnedToCore() は、FreeRTOS のマルチタスク機能に含まれる関数です。

ここでは、180-185行目のラムダ式を渡し、anotherLoop() を無限に呼ぶタスクを登録しています。

[](void* params) {
    while (true) {
        anotherLoop();
        vTaskDelay(1);    // IMPORTANT for Watchdog
    }
},

タスクの名称は Task for anotherLoop() で、スタックサイズは 8192 、タスクへのパラメータはなく、優先度は 18（大きいほど高く、無線 LAN 関連の処理は1個上の 19）、タスクを操作するインタフェースもなく、実行する CPU コアは無線 LAN などの RF 処理と同じ Core 0 です（ loop() 等は Core 1 ）。

"Task for anotherLoop()", 8192, nullptr, 18, nullptr, 0);    // Priority is 18 (lower than WiFi)

TWELITE のデータの更新

203行目では、anotherLoop() 内にて Twelite.update() を呼び出しています。

Twelite.update();

Twelite.update() は、TWELITE 親機から送信されるパケットデータ（ModBus ASCII 形式）を順次1バイトずつ読み出す関数です。

スプレッドシートの更新

192-195行目では、スプレッドシートの更新処理を呼び出しています。

if (millis() - lastTimeRequestWasSent > MIN_REQUEST_INTERVAL) {
    // Add available data
    addSheetsDataRow(spreadsheetIdString);
}

192行目の if 文では、60リクエスト毎分の API 制限を守るために、前回のリクエスト送信から必ず 1 秒以上空けるようにしています（JavaScript の Throttle / Debounce における Throttle に相当します）。

194行目では、必要に応じてスプレッドシートへデータ行を追加します。

addSheetsDataRow(spreadsheetIdString);

API ライブラリの更新

196行目では、Google API 用ライブラリの更新を行い、リクエストの送信可否の状態を取得しています。

readyForNewRequests = GSheet.ready();

NTP ライブラリの更新

197行目では、NTP ライブラリの更新を行っています。

timeClient.update();

スプレッドシートの操作

217行目以降では、Sheets API によるスプレッドシートの操作を行っています。

詳しくは ライブラリの API リファレンス や Sheets API の REST リソース をご覧ください。

関連情報

Google

• Google Cloud コンソール：ようこそ – SPOT-DEV – Google Cloud コンソール
• Sheets REST API リファレンス：Google Sheets API | Google for Developers
  • スプレッドシートの作成：Method: spreadsheets.create | Google Sheets | Google for Developers
  • スプレッドシート全体の更新：Method: spreadsheets.batchUpdate | Google Sheets | Google for Developers
  • スプレッドシートの値の更新：Method: spreadsheets.values.batchUpdate | Google Sheets | Google for Developers
• Sheets REST サンプル：サンプル | Google Sheets | Google for Developers
  • 行の追加など：行と列の操作 | Google Sheets | Google for Developers

Arduino

• 公式サイト：Arduino - Home
  • API リファレンス：Arduino Reference - Arduino Reference
  • コーディングスタイルガイド：Arduino Style Guide for Creating Libraries | Arduino Documentation
  • 公式 NTP ライブラリ：arduino-libraries/NTPClient: Connect to a NTP server

ESP32

• 製品情報：ESP32 Wi-Fi & Bluetooth MCU I Espressif Systems
  • データシート：esp32_datasheet_en.pdf
• Arduino 向けツールチェイン：espressif/arduino-esp32: Arduino core for the ESP32
  • スタートガイド：Getting Started — Arduino-ESP32 documentation
  • 導入方法：Installing — Arduino-ESP32 documentation
  • API リファレンス：Libraries — Arduino-ESP32 documentation
    • Wi-Fi API：Wi-Fi API — Arduino-ESP32 documentation
  • チュートリアル：Tutorials — Arduino-ESP32 documentation
  • トラブルシューティング：Troubleshooting — Arduino-ESP32 documentation
• ESP-IDF：espressif/esp-idf: Espressif IoT Development Framework
  • ドキュメント：ESP-IDF Programming Guide - ESP32
    • APIリファレンス：FreeRTOS (ESP-IDF) - ESP32

コミュニティ

ライブラリ

• Google Sheets / Drive API：mobizt/ESP-Google-Sheet-Client: Arduino Google Sheet REST client library for ESP8266, ESP32 and Raspberry Pi Pico RP2040
• 日付と時刻：PaulStoffregen/Time: Time library for Arduino

プラグイン

• スタックトレース：me-no-dev/EspExceptionDecoder: Exception Stack Trace Decoder for ESP8266 and ESP32