Google Sheets
このスニペットは、Google SheetsにIoTデータを送信する機能を提供します。
Resources used: HTTPS x 1
Details
send_sheets 関数は、HTTPS経由のアクセストークンを使用して、指定した「parameters」に従って、渡された文字列「payload」をGoogleスプレッドシートにPOSTできます。 アクセストークンは、サービスアカウントのメールと秘密キーを使用して getAccessToken 関数で取得できます。 次に、結果（エラー/応答）がコールバック関数に渡されます。
注意: 提供されている関数では、最大4KBまでのデータを取り扱うことができます。より大きなサイズのデータを取り扱う場合は、neqto.jsドキュメントのhttpsオブジェクト、分割書き込みを参考にしてください。
次の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");
});
};
Function Usage Example
次のスクリプト例では、send_sheets関数を使用して、設定されたGoogle Sheetsのスプレッドシートに一定間隔で継続的にデータを送信します。又、トークンの有効期限が切れた場合、getAccessToken関数を使用して再取得を行います。
/*
<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-08-18