neqto: Docs
  • Languages iconEnglish
    • 日本語
  • Docs
  • Region API
  • Global API
  • FAQ

›neqto.js Snippets

Getting Started

  • neqto: Hello World!
  • Step 1. Cloud Setting
  • Step 2. Device Setting & Start Service

    • When using neqto: Bridge
    • When using Spresense
  • Step 3. Using Sensors

neqto:

  • neqto: Account Registration
  • API Usage
  • Batch Registration
  • Support Guidelines

neqto: Console

  • Introduction
  • Fundamentals
  • Administrative Actions
  • Device Management
  • Scripts
  • Actions and Contacts
  • neqto: Apps
  • Machine Driver
  • Recommended Browsers
  • Billing Information

SPRESENSE

    Hardware Specifications

    • 01. About Spresense

    Software Specifications

    • 01. Operational Flow
    • 02. Initial Installation
    • 03. Spresense Wi-Fi Initial Setup
    • 05. Debug Log Acquisition
    • 06. System LED Indications
    • 07. Event Messages
    • 08. Updating Firmware

    neqto.js

    • 01. About neqto.js
    • 02. Log
    • 03. Timers
    • 04. HTTP
    • 05. HTTPS
    • 06. MQTT
    • 07. Secure
    • 08. Storage
    • 10. RTC
    • 12. GPIO
    • 13. UART
    • 15. I2C
    • 17. Camera
    • 18. nqSpresense
    • 19. nqService
    • 20. nqMqtt
    • 21. nqFOTA
    • 22. nqWiFi

neqto: Bridge Series

    Hardware Specifications

    • 01. neqto: Bridge Module
    • 02. neqto: Bridge Wi-Fi Module
    • 03. neqto: Bridge LTE-1 Module
    • 04. neqto: Bridge LTE-M/NB Module
    • 05. neqto: Bridge IO Board
    • 06. neqto: Bridge IO Board Short Plug
    • 07. neqto: Bridge Digital IO Board
    • 08. neqto: Bridge Digital IO Board Short Plug

    Software Specifications

    • 01. Operational Flow
    • 02. neqto: Bridge Wi-Fi Module Initial Setup
    • 03. neqto: Bridge LTE Module Initial Setup
    • 04. Debug Log Acquisition
    • 05. System LED Indications
    • 06. Event Messages
    • 07. Updating Firmware

    neqto.js

    • 01. About neqto.js
    • 02. Log
    • 03. Timers
    • 04. HTTP
    • 05. HTTPS
    • 06. MQTT
    • 07. Secure
    • 08. Storage
    • 09. Sleep
    • 10. RTC
    • 11. UserSW
    • 12. GPIO
    • 13. UART
    • 14. SPI
    • 15. I2C
    • 16. ADC
    • 17. BLE
    • 18. nqBridge
    • 19. nqService
    • 20. nqMqtt
    • 21. nqFOTA
    • 22. nqWiFi
    • 23. nqLte
    • 24. nqLAN
    • 25. nqEx

neqto.js Libraries

    I2C

    • LIS2DW12 v2 Accelerometer
    • HTS221 v2 Temperature and Humidity Sensor
    • [Archive] LIS2DW12 Accelerometer
    • [Archive] HTS221 Temperature and Humidity Sensor

    Integration

    • AWS IoT Core v2 Library
    • AWS S3 v2 Library
    • Azure IoT v2 Library
    • GCP IoT Core Library
    • [Archive] AWS S3 Library
    • [Archive] AWS IoT Core Library

neqto.js Snippets

  • DataDog Snippet
  • Dropbox Snippet
  • Google Sheets Snippet
  • InfluxDB Snippet
  • Oracle Cloud Object Storage Snippet
  • Salesforce Snippet
  • SAP Cloud Platform Internet of Things Snippet
  • Splunk Snippet
  • Niagara Snippet

Release Notes

  • neqto: Cloud Updates
  • neqto: Firmware (Bridge Wi-Fi/LTE Module) Releases
  • neqto: Firmware (Spresense Wi-Fi) Releases

Google Sheets Snippet

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

neqto: BridgeSPRESENSE
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`, `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-09-08
← Dropbox SnippetInfluxDB Snippet →
  • Details
  • Function Usage Example
AboutNewsProductsFAQPrivacy Policy}
neqto: Cloud
IntroductionFundamentalsAdministrative ActionsDevice Management neqto: Apps
neqto: Bridge Series
neqto: Bridge Moduleneqto: Bridge Wi-Fi Moduleneqto: Bridge LTE-1 ModuleError Logging Event Messages
API Documentation
API UsageGlobal APIRegional APIAPI Terms of Service
Jigsaw, Inc.
© 2021 JIG-SAW INC.