I am unable to get a Google Sheets App script to receive any valid data from Arduino Cloud Webhooks.
The script is triggered but does receive any data from the Arduino cloud.
The Arduino cloud has 2 float variables which are being updated to the Arduino cloud.
The Google script Deploy url is shown as "Active" after being entered into the webhooks edit box on the Arduino cloud Setup page.
The Google script when called by webhooks starts to executes and quits due to no (e) data being received (from the Arduino cloud?)
Here is the Google script
/*
https://github.com/arduino/arduino-iot-google-sheet-script/blob/master/Code.gs
*/
// Global constants
var MAX_ROWS = 1440; // max number of data rows to display (3600s / cloud_int(30s) * num_ore(12h))
var HEADER_ROW = 1; // row index of header
var TIMESTAMP_COL = 1; // column index of the timestamp column
function myFunction() {
// This function can be used for any initialization or manual tasks
Logger.log("Script initialized");
}
function doPost(e) {
Logger.log("1 V8");
Logger.log("Raw event data: " + JSON.stringify(e));
if (!e || !e.parameter) {
Logger.log("Error: No parameters received");
return;
}
Logger.log("2 V8");
// Example: if you're sending a variable called temperature
var temperature = e.parameter.temperatureCloud;
var volts = e.parameter.voltsCloud;
Logger.log("3 V8");
Logger.log("Temperature: " + temperatureCloud);
Logger.log("Volts: " + voltsCloud);
// Log to spreadsheet or continue processing...
//}
//function doPost(e) {
Logger.log("POST V8");
// Get active spreadsheet (not just the active sheet)
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get sheet named RawData
var sheet = ss.getSheetByName('RawData');
// If the sheet doesn't exist, log error and return
if (!sheet) {
Logger.log("Error: Sheet 'RawData' not found");
return;
} else {
Logger.log("Sheet 'RawData' found");
}
var cloudData = JSON.parse(e.postData.contents); // JSON object containing all info 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; // array of json objects
This programming issue now resolved. A Google script doPost(e) when triggered by Arduino Cloud Webhooks writes directly to a Google sheet (a free service).
function doPost(e) {
Logger.log("I was called")
if(typeof e !== 'undefined')
Logger.log(e.parameter);
var ss= SpreadsheetApp.openById("ID here")
var sheet = ss.getSheetByName("Sheet2")
sheet.getRange(1, 1).setValue(JSON.stringify(e))
return ContentService.createTextOutput(JSON.stringify(e))
}
This is the edited code which I am using.
Also the code line
if (date.getYear() > 2018) {
should be
if (date.getFullYear() > 2024) { //set this to the current year - 1
Here is the Google Sheet result
/*Google Script code for doPost(e)
https://github.com/arduino/arduino-iot-google-sheet-script/blob/master/Code.gs
*/
// Global constants
var MAX_ROWS = 1440; // max number of data rows to display (3600s / cloud_int(30s) * num_ore(12h))
var HEADER_ROW = 1; // row index of header
var TIMESTAMP_COL = 1; // column index of the timestamp column
var VER = "ver 19";
function myFunction() {
// This function can be used for any initialization or manual tasks
Logger.log("Script initialized");
}
function doPost(e) {
Logger.log(VER);
Logger.log("I was called");
console.log("i was called too");
if(typeof e !== 'undefined') {
Logger.log("I am undefined");
Logger.log(e.parameter);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("RawData");
//var cloudData = JSON.stringify(e);
Logger.log("JSON cloudData");
// Get active spreadsheet (not just the active sheet)
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get sheet named RawData
var sheet = ss.getSheetByName('RawData');
// If the sheet doesn't exist, log error and return
if (!sheet) {
Logger.log("Error: Sheet 'RawData' not found");
return;
} else {
Logger.log("Sheet 'RawData' found");
}
var cloudData = JSON.parse(e.postData.contents); // JSON object containing all info 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; // array of json objects
// Store names and values from the values array
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.toFixed(2);
}
// 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 if statement is due to the fact that duplicate messages arrive from the cloud!
If that occurs, the timestamp is not read correctly and date variable gets compromised.
Hence, execute the rest of the script if the year of the date is well defined and it is greater
than 2018 (or any other year before)
*/
//if (date.getYear() > 2018) { //use date.getFullYear()
if (date.getFullYear() > 2024) { //set this to the current year - 1
// Discard all messages that arrive 'late'
if (sheet.getRange(HEADER_ROW+1, 1).getValue() != '') { // for the first time app is run
var now = new Date(); // now
var COMM_TIME = 5; // rough overestimate of communication time between cloud and app
if (now.getTime() - date.getTime() > COMM_TIME * 1000) {
return;
}
}
// Write property names to header row
sheet.getRange(HEADER_ROW, 1).setValue('timestamp');
for (var i = 0; i < incLength; i++) {
var lastCol = sheet.getLastColumn();
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 column 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);
}
// Insert new row after header
sheet.insertRowAfter(HEADER_ROW);
// Reset style of the new row, otherwise it will inherit the style of the header row
var range = sheet.getRange(HEADER_ROW+1, 1, 1, 26); // A2:Z2 in R1C1 notation
//range.setBackground('#ffffff');
range.setFontColor('#000000');
range.setFontSize(10);
range.setFontWeight('normal');
// Write the timestamp
sheet.getRange(HEADER_ROW+1, TIMESTAMP_COL).setValue(date).setNumberFormat("yyyy-MM-dd HH:mm:ss");
// Write values in the respective columns
for (var col = 1+TIMESTAMP_COL; col <= lastCol; col++) {
// First copy previous values
// This avoids empty cells if not all properties are updated at the same time
sheet.getRange(HEADER_ROW+1, col).setValue(sheet.getRange(HEADER_ROW+2, col).getValue());
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]);
}
}
}
} // end if (date.getYear() > 2018)
//this line from Stack Exchange
return ContentService.createTextOutput(JSON.stringify(e));
}
}
