Google Sheets Snippet

This snippet can be used by copy-pasting to the neqto.js script.

neqto: Bridge	SPRESENSE
v00.00.30+	v01.00.00+

This snippet provides a function to send IoT data to Google Sheets.

Details

The send_sheets function can be used to POST the passed string 'payload' to Google Sheets according to the specified 'parameters' using an Access Token, over HTTPS. The Access Token can be retrieved by the getAccessToken function using a Service Account Email and Private Key. The result (error/response) is then passed to the callback function.

To start using this snippet, SERVICE_ACCOUNT (Service Account with permissions to sheets.googleapis.com), PRIVATE_KEY (Private Key linked to the provided Service Account), and CA are required to be configured by the user.

NOTE: The provided function only allows payloads up to size of 4KB. For bigger sized payloads, please refer to the sample for divided writing in neqto.js docs for neqto: Bridge and Sony Spresense.

The following CA can be used (as of 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

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` and `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
        }
    }
};

The company names and product names mentioned above are registered trademarks or trademarks of their respective companies.

---

Updated: 2020/08/26