Google Sheets
このスニペットは、Google SheetsにIoTデータを送信する機能を提供します。
Resources used: HTTPS x 1
Details
send_sheets 関数は、HTTPS経由のアクセストークンを使用して、指定した「parameters」に従って、渡された文字列「payload」をGoogleスプレッドシートにPOSTできます。 アクセストークンは、サービスアカウントのメールと秘密キーを使用して getAccessToken 関数で取得できます。 次に、結果（エラー/応答）がコールバック関数に渡されます。
注意: 提供されている関数では、最大4KBまでのデータを取り扱うことができます。より大きなサイズのデータを取り扱う場合は、neqto.jsドキュメントのhttpsオブジェクト、分割書き込みを参考にしてください。
CAの取得方法はこちら。
var CA = "-----BEGIN CERTIFICATE-----\n...<CA>...\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: 2023-04-14