arduino/arduino-iot-google-sheet-script

A script to integration Arduino IoT Cloud with Google Sheet to automatically save historical data about your sensors

arduino-iot-google-sheet-script

This repo contains a script (Code.gs) to integrate Arduino IoT Cloud with Google Sheets using web-hooks. The script is part of a project in which data read from different sensors are sent to a google spreadsheet in order to update tables and charts in real time.

arduino-iot-google-sheet-script's Issues

Fixed several issues with the code and suggest this as a new version

Hi, it may be that Google AppScript changed or deprecated some APIs but the original version posted here didn't work for me and always resulted in empty spreadsheet.

Also the original solution of avoiding duplicate rows can be improved - I suggest a new solution that merges all values into the same row as long as 1/2 of the interval reporting time has not expired.

Here is my new suggested solution which works more cleanly for me:

/*
* Copyright 2022 Peter Bendel
* copied (and then modified) from: https://github.com/arduino/arduino-iot-google-sheet-script/blob/master/Code.gs
* Copyright 2018 ARDUINO SA (http://www.arduino.cc/)
* This file is part of arduino-iot-google-sheet-script.
* Copyright (c) 2019
* Authors: Marco Passarello
*
* This software is released under:
* The GNU General Public License, which covers the main part of 
* arduino-iot-google-sheet-script
* The terms of this license can be found at:
* https://www.gnu.org/licenses/gpl-3.0.en.html
*
* You can be released from the requirements of the above licenses by purchasing
* a commercial license. Buying such a license is mandatory if you want to modify or
* otherwise use the software for commercial activities involving the Arduino
* software without disclosing the source code of your own applications. To purchase
* a commercial license, send an email to [email protected].
*
*/


// get active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

// get sheet named ArduinoIOTCloudSensorData
var sheet = ss.getSheetByName('RawData');


var MAX_ROWS = 1440;     // max number of data rows to display
// 1 day * 60 (per hour) * 24 hours
var HEADER_ROW = 1;     // row index of header
var TIMESTAMP_COL = 1;  // column index of the timestamp column
var INTERVAL_IN_SECONDS = 60;  // 1 minute

function doPost(e) {  
  var cloudData = JSON.parse(e.postData.contents); // this is a json object containing all info coming from IoT Cloud
  //var webhook_id = cloudData.webhook_id; // really not using these three
  //var device_id = cloudData.device_id;
  //var thing_id = cloudData.thing_id;
  var values = cloudData.values; // this is an array of json objects
  
  // store names and values from the values array
  // just for simplicity
  var incLength = values.length;
  var incNames = [];
  var incValues = [];
  for (var i = 0; i < incLength; i++) {
    incNames[i] = values[i].name;
    incValues[i] = values[i].value;
  }
  
  // read timestamp of incoming message
  var timestamp = values[0].updated_at;          // format: yyyy-MM-ddTHH:mm:ss.mmmZ
  var date = new Date(Date.parse(timestamp)); 
    
    // this section write property names 
    sheet.getRange(HEADER_ROW, 1).setValue('timestamp');
    for (var i = 0; i < incLength; i++) {
      var lastCol = sheet.getLastColumn(); // at the very beginning this should return 1 // second cycle -> it is 2
      if (lastCol == 1) {
        sheet.getRange(HEADER_ROW, lastCol + 1).setValue(incNames[i]);
      } else {
        // check if the name is already in header
        var found = 0;
        for (var col = 2; col <= lastCol; col++) {
          if (sheet.getRange(HEADER_ROW, col).getValue() == incNames[i]) {
            found = 1;
            break;
          }
        }
        if (found == 0) {
          sheet.getRange(HEADER_ROW, lastCol+1).setValue(incNames[i]);
        }
      }
    }
    
    // redefine last coloumn and last row since new names could have been added
    var lastCol = sheet.getLastColumn();
    var lastRow = sheet.getLastRow();
    
    // delete last row to maintain constant the total number of rows
    if (lastRow > MAX_ROWS + HEADER_ROW - 1) { 
      sheet.deleteRow(lastRow);
    }
  
  if (lastRow > 1) { // this is not the first time - check if we have a new date
    // read last date written and only if we are at least 
    var lastDate = sheet.getRange(HEADER_ROW+1, TIMESTAMP_COL).getValue();
    // only if enough time has passed after last reporting interval add a new row
    if (date.getTime() - lastDate.getTime() > (INTERVAL_IN_SECONDS*1000)) { 
      // insert next row after header row 
      sheet.insertRowAfter(HEADER_ROW);
      // write the timestamp only once per interval
      sheet.getRange(HEADER_ROW+1, TIMESTAMP_COL).setValue(date).setNumberFormat("yyyy-MM-dd HH:mm:ss");
    }
  } else {
    // insert first row after header row 
    sheet.insertRowAfter(HEADER_ROW);
    // write the timestamp only once per interval
    sheet.getRange(HEADER_ROW+1, TIMESTAMP_COL).setValue(date).setNumberFormat("yyyy-MM-dd HH:mm:ss");
  }
    
    // reset style of the new row, otherwise it will inherit the style of the header row
    var range = sheet.getRange('A2:Z2');
    //range.setBackground('#ffffff');
    range.setFontColor('#000000');
    range.setFontSize(10);
    range.setFontWeight('normal');
    
    // write values in the respective columns
    for (var col = 1+TIMESTAMP_COL; col <= lastCol; col++) {
      for (var i = 0; i < incLength; i++) {
        var currentName = sheet.getRange(HEADER_ROW, col).getValue();
        if (currentName == incNames[i]) {
          // turn boolean values into 0/1, otherwise google sheets interprets them as labels in the graph
          if (incValues[i] == true) {
            incValues[i] = 1;
          } else if (incValues[i] == false) {
            incValues[i] = 0;
          }
          sheet.getRange(HEADER_ROW+1, col).setValue(incValues[i]);
        } 
      }
    }  
}