Google Sheets スニペット

このスニペットは、neqto.jsスクリプトにコピー & ペーストして使用します。

NEQTO Bridge	SPRESENSE
v00.00.30+	v01.00.00+

このスニペットは、Google SheetsにIoTデータを送信する機能を提供します。

詳細

send_sheets 関数は、HTTPS経由のアクセストークンを使用して、指定した「parameters」に従って、渡された文字列「payload」をGoogleスプレッドシートにPOSTできます。 アクセストークンは、サービスアカウントのメールと秘密キーを使用して getAccessToken 関数で取得できます。 次に、結果（エラー/応答）がコールバック関数に渡されます。

注意: この関数は、最大4KBのデータのみを許可します。より大きなサイズのデータについては、neqto.jsドキュメントのNEQTO BridgeとSony Spresenseの分割書き込みを参照してください。

次のCAを使用できます（2020-07-30時点）。

/* GTS CA 1O1 - 01e3b49aa18d8aa981256950b8 */
var CA = "-----BEGIN CERTIFICATE-----\nMIIESjCCAzKgAwIBAgINAeO0mqGNiqmBJWlQuDANBgkqhkiG9w0BAQsFADBMMSAw\nHgYDVQQLExdHbG9iYWxTaWduIFJvb3QgQ0EgLSBSMjETMBEGA1UEChMKR2xvYmFs\nU2lnbjETMBEGA1UEAxMKR2xvYmFsU2lnbjAeFw0xNzA2MTUwMDAwNDJaFw0yMTEy\nMTUwMDAwNDJaMEIxCzAJBgNVBAYTAlVTMR4wHAYDVQQKExVHb29nbGUgVHJ1c3Qg\nU2VydmljZXMxEzARBgNVBAMTCkdUUyBDQSAxTzEwggEiMA0GCSqGSIb3DQEBAQUA\nA4IBDwAwggEKAoIBAQDQGM9F1IvN05zkQO9+tN1pIRvJzzyOTHW5DzEZhD2ePCnv\nUA0Qk28FgICfKqC9EksC4T2fWBYk/jCfC3R3VZMdS/dN4ZKCEPZRrAzDsiKUDzRr\nmBBJ5wudgzndIMYcLe/RGGFl5yODIKgjEv/SJH/UL+dEaltN11BmsK+eQmMF++Ac\nxGNhr59qM/9il71I2dN8FGfcddwuaej4bXhp0LcQBbjxMcI7JP0aM3T4I+DsaxmK\nFsbjzaTNC9uzpFlgOIg7rR25xoynUxv8vNmkq7zdPGHXkxWY7oG9j+JkRyBABk7X\nrJfoucBZEqFJJSPk7XA0LKW0Y3z5oz2D0c1tJKwHAgMBAAGjggEzMIIBLzAOBgNV\nHQ8BAf8EBAMCAYYwHQYDVR0lBBYwFAYIKwYBBQUHAwEGCCsGAQUFBwMCMBIGA1Ud\nEwEB/wQIMAYBAf8CAQAwHQYDVR0OBBYEFJjR+G4Q68+b7GCfGJAboOt9Cf0rMB8G\nA1UdIwQYMBaAFJviB1dnHB7AagbeWbSaLd/cGYYuMDUGCCsGAQUFBwEBBCkwJzAl\nBggrBgEFBQcwAYYZaHR0cDovL29jc3AucGtpLmdvb2cvZ3NyMjAyBgNVHR8EKzAp\nMCegJaAjhiFodHRwOi8vY3JsLnBraS5nb29nL2dzcjIvZ3NyMi5jcmwwPwYDVR0g\nBDgwNjA0BgZngQwBAgIwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly9wa2kuZ29vZy9y\nZXBvc2l0b3J5LzANBgkqhkiG9w0BAQsFAAOCAQEAGoA+Nnn78y6pRjd9XlQWNa7H\nTgiZ/r3RNGkmUmYHPQq6Scti9PEajvwRT2iWTHQr02fesqOqBY2ETUwgZQ+lltoN\nFvhsO9tvBCOIazpswWC9aJ9xju4tWDQH8NVU6YZZ/XteDSGU9YzJqPjY8q3MDxrz\nmqepBCf5o8mw/wJ4a2G6xzUr6Fb6T8McDO22PLRL6u3M4Tzs3A2M1j6bykJYi8wW\nIRdAvKLWZu/axBVbzYmqmwkm5zLSDW5nIAJbELCQCZwMH56t2Dvqofxs6BBcCFIZ\nUSpxu6x6td0V7SvJCCosirSmIatj/9dSSVDQibet8q/7UK4v4ZUN80atnZz1yg==\n-----END CERTIFICATE-----";

//=================================================================
// GOOGLE SHEETS SNIPPET
//=================================================================

//=================================================================
// The following configuration are MANDATORY. Set by user.
//=================================================================
// The email address of the service account created on Google Cloud Platform that has permission to use `sheets.googleapis.com`.
// eg. '<service_account_name>@<gcp_project_id>.iam.gserviceaccount.com'
var SA_EMAIL = "<YOUR_SERVICE-ACCOUNT-EMAIL>";

// A generated private key for the Service Account from Google Cloud Platform.
// eg. '-----BEGIN PRIVATE KEY-----\n...<PRIVATE_KEY>...\n-----END PRIVATE KEY-----'
var PRIVATE_KEY = '<YOUR_PRIVATE-KEY>';

// The ID of the Spreadsheet to append data to.
// eg. 'https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit#gid=0'
var SPREADSHEET_ID = '<YOUR_SPREADSHEET-ID>';

// Public certificate of the certificate authority that signed the Google API server certificate for SSL/TLS handshake.
// eg. '-----BEGIN CERTIFICATE-----\n...<CA>...\n-----END CERTIFICATE-----'
var CA = '<YOUR_CA>';
//=================================================================

/**
 * Get Access Token from Google Sheets using Service Account email by JWT assertion.
 * https://developers.google.com/identity/protocols/oauth2#serviceaccount
 * @function getAccessToken
 * @param {object} sessionInfo - Information about the session, as an Object.
 * @param {number} tokenTimeout - Amount of time (in seconds) to generate a valid JWT for before it expires, as a Number.
 * @param {function} callback - User callback to return the result (error/response).
 * @returns {undefined}
 */
var getAccessToken = function (sessionInfo, tokenTimeout, callback) {
    // Make JWT for assertion
    var created = Math.ceil((new Date()).getTime() / 1000);
    var claim = {
        "iss": SA_EMAIL,
        "scope": 'https://www.googleapis.com/auth/spreadsheets',
        "aud": 'https://www.googleapis.com/oauth2/v4/token',
        "iat": created,
        "exp": created + parseInt(tokenTimeout)
    };
    var hdrB64 = secure.base64Encode('{"alg":"RS256","typ":"JWT"}').replace(/=/g, ""); // 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9'
    var claimB64 = secure.base64Encode(JSON.stringify(claim)).replace(/=/g, "");
    var stringSign = hdrB64 + "." + claimB64;
    secure.setSign('sha256');
    secure.signUpdate(stringSign);
    var signature = secure.signDigest(PRIVATE_KEY);
    var sigB64 = secure.base64Encode(signature).replace(/=/g, "").replace(/\+/g, "-").replace(/\//g, "_");
    var JWT = hdrB64 + "." + claimB64 + "." + sigB64;

    var options = {
        "method": 'POST',
        "host": 'oauth2.googleapis.com',
        "path": `/token?grantType=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=${JWT}`,
        "headers": {
            "Content-type": 'application/json',
            "Content-Length": '0'
        },
        "ca": CA
    };
    var request = https.request(options, function (response) {
        response.on('end', function () {
            var resBody = response.read();
            if (response.statusCode == 200) { // 200 - OK
                var resBodyObj = JSON.parse(resBody);
                sessionInfo.accessToken = resBodyObj.access_token;
            }
            callback(null, { "statusCode": response.statusCode, "statusMessage": response.statusMessage, "body": resBody });
        });
    });
    request.on('error', function () {
        callback({ "errCode": request.errCode }, null);
    });
    request.end(function () {
        print("[request] SUCCESS");
    });
};

/**
 * Append values to Google Sheets spreadsheet range using an Access Token.
 * https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
 * @function send_sheets
 * @param {object} sessionInfo - Information about the session, as an Object.
 * @param {string} parameters - Query parameters for the request, as an Object.
 * @param {object} payload - Range in A1 format to append to, and the corresponding values in ValueRange format, as an Object.
 * @param {function} callback - User callback to return the result (error/response).
 * @returns {undefined}
 */
var send_sheets = function (sessionInfo, parameters, payload, callback) {
    var body = JSON.stringify(payload);
    var query_parameters = Object.keys(parameters).map(function (key) {
        return `${encodeURIComponent(key)}=${encodeURIComponent(parameters[key])}`;
    }).join('&');
    var options = {
        "method": 'POST',
        "host": 'sheets.googleapis.com',
        "path": `/v4/spreadsheets/${SPREADSHEET_ID}/values/${payload["range"]}:append?${query_parameters}`,
        "headers": {
            "Authorization": `Bearer ${sessionInfo.accessToken}`,
            "Content-Type": 'application/json',
            "Content-Length": body.length.toString()
        },
        "ca": CA
    };
    var request = https.request(options, function (response) {
        response.on('end', function () {
            callback(null, { "statusCode": response.statusCode, "statusMessage": response.statusMessage, "body": response.read() });
        });
    });
    request.on('error', function () {
        callback({ "errCode": request.errCode }, null);
    });
    request.end(body, function () {
        print("[request] SUCCESS");
    });
};

使用例

The following example script uses the send_sheets function to continuously send data to the set Google Sheets spreadsheet at regular intervals, and re-fetches the token using the getAccessToken function when it expires.

/*
<INSERT ABOVE SNIPPET HERE WITH SET CONFIGURATIONS>
*/

//=================================================================
log.setLevel(-1);       //-1:NONE 0:ERROR 1:WARNING 2:DEBUG 3:TRACE
log.printLevel(2);      //0:DISABLE 1:LOG 2:CONSOLE 3:BOTH
//=================================================================
// MAIN SCENARIO
//=================================================================

/**
 * Callback to fetch error/response from the request.
 * @function callback
 * @param {object} err - Error returned in case of a failed request. Has one property - `errCode`.
 * @param {object} data - Response returned by a successfully completed request. Has three properties - `statusCode`, `statusMessage`, and `body`.
 */
var callback = function (err, data) {
    if (err) {
        print("[error]", err.errCode);
    } else {
        print("[status]", data.statusCode, data.statusMessage);
        print("[response]", data.body);
    }
};

/**
 * Object to store any session related information.
 * @enum sessionInfo
 * @property {string} accessToken - Access token to authorize API requests.
 */
var sessionInfo = {
    "accessToken": ''
};
var parameters = {
    "valueInputOption": 'USER_ENTERED', /* 'USER_ENTERED', 'RAW', 'USER_ENTERED' */
    "insertDataOption": 'INSERT_ROWS', /* 'INSERT_ROWS', 'OVERWRITE' */
    "includeValuesInResponse": true, /* true, false */
    "responseValueRenderOption": 'FORMATTED_VALUE', /* 'FORMATTED_VALUE', 'UNFORMATTED_VALUE', 'FORMULA' */
    "responseDateTimeRenderOption": 'SERIAL_NUMBER' /* 'SERIAL_NUMBER', 'FORMATTED_STRING' */
};
var payload = {
    "range": 'A1:D',
    "values": [
        ['Hello', 'from', 'NEQTO', 'Device'],
    ]
};

// A mutex to limit the active HTTP requests.
var lock = false;

while (1) {
    if (!lock) { // wait for the lock to release
        lock = true; // lock
        if (sessionInfo.accessToken) {
            print("[info] send_sheets()");
            send_sheets(sessionInfo, parameters, payload, function (err, data) {
                callback(err, data);
                if (data.statusCode == 401) sessionInfo.accessToken = ''; // 401 - Unauthorized
                lock = false; // unlock
            });
            setTimeout(60000).wait(); // wait for 60 seconds
        }
        else {
            print("[info] getAccessToken()");
            getAccessToken(sessionInfo, 3600, function (err, data) {
                callback(err, data);
                lock = false; // unlock
            });
            setTimeout(10000).wait(); // wait for 10 seconds
        }
    }
};

上記に記載されている会社名、製品名は、各社の登録商標または商標です。

---

Updated: 2021-02-19