'Modifying script for Google sheets
To log sensor data to Google sheets using ESP32, I found a script online to accomplish that. However, I want to modify it so that I could have additional data fields logged. I don't know much about JavaScript but tried to add a new var and modify the code but it doesn't work. Here is the original script:
function doGet(e){
Logger.log("--- doGet ---");
var tag = "",
value = "";
try {
// this helps during debuggin
if (e == null){e={}; e.parameters = {tag:"test",value:"-1"};}
tag = e.parameters.tag;
value = e.parameters.value;
// save the data to spreadsheet
save_data(tag, value);
return ContentService.createTextOutput("Wrote:\n tag: " + tag + "\n value: " + value);
} catch(error) {
Logger.log(error);
return ContentService.createTextOutput("oops...." + error.message
+ "\n" + new Date()
+ "\ntag: " + tag +
+ "\nvalue: " + value);
}
}
// Method to save given data to a sheet
function save_data(tag, value){
Logger.log("--- save_data ---");
try {
var dateTime = new Date();
// Paste the URL of the Google Sheets starting from https thru /edit
// For e.g.: https://docs.google.com/..../edit
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-3NY0nbnJgx_RHJeG6GckfVesUXBI5rgxmTJ40yX0vE/edit");
var dataLoggerSheet = ss.getSheetByName("Datalogger");
// Get last edited row from DataLogger sheet
var row = dataLoggerSheet.getLastRow() + 1;
// Start Populating the data
dataLoggerSheet.getRange("A" + row).setValue(row -1); // ID
dataLoggerSheet.getRange("B" + row).setValue(dateTime); // dateTime
dataLoggerSheet.getRange("C" + row).setValue(tag); // tag
dataLoggerSheet.getRange("D" + row).setValue(value); // value
// Update summary sheet
summarySheet.getRange("B1").setValue(dateTime); // Last modified date
// summarySheet.getRange("B2").setValue(row - 1); // Count
}
catch(error) {
Logger.log(JSON.stringify(error));
}
Logger.log("--- save_data end---");
}
Here is the version with my additions:
function doGet(e){
Logger.log("--- doGet ---");
var tag = "",
value = "",
tag2 = "",
val2 = "";
try {
// this helps during debuggin
if (e == null){e={}; e.parameters = {tag:"test",value:"-1", tag2: "test2", val2: "-1"};}
tag = e.parameters.tag;
value = e.parameters.value;
tag2 = e.parameters.tag2;
val2 = e.parameters.val2;
// save the data to spreadsheet
save_data(tag, value, tag2, val2);
return ContentService.createTextOutput("Wrote:\n tag: " + tag + "\n value: " + value + "\n tag2: " + tag2 + "\n val2: " + val2);
} catch(error) {
Logger.log(error);
return ContentService.createTextOutput("oops...." + error.message
+ "\n" + new Date()
+ "\ntag: " + tag
+ "\nvalue: " + value
+ "\n tag2: " + tag2
+ "\n val2: " + val2);
}
}
// Method to save given data to a sheet
function save_data(tag, value, tag2, val2){
Logger.log("--- save_data ---");
try {
var dateTime = new Date();
// Paste the URL of the Google Sheets starting from https thru /edit
// For e.g.: https://docs.google.com/..../edit
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-3NY0nbnJgx_RHJeG6GckfVesUXBI5rgxmTJ40yX0vE/edit");
var dataLoggerSheet = ss.getSheetByName("sht31_1");
// Get last edited row from DataLogger sheet
var row = dataLoggerSheet.getLastRow() + 1;
// Start Populating the data
dataLoggerSheet.getRange("A" + row).setValue(row -1); // ID
dataLoggerSheet.getRange("B" + row).setValue(dateTime); // dateTime
dataLoggerSheet.getRange("C" + row).setValue(tag); // tag
dataLoggerSheet.getRange("D" + row).setValue(val); // value
dataLoggerSheet.getRange("E" + row).setValue(tag2); // tag
dataLoggerSheet.getRange("F" + row).setValue(val2); // value
// Update summary sheet
summarySheet.getRange("B1").setValue(dateTime); // Last modified date
// summarySheet.getRange("B2").setValue(row - 1); // Count
}
catch(error) {
Logger.log(JSON.stringify(error));
}
Logger.log("--- save_data end---");
}
So when I send the data using a GET request, only values of original variables (tag and value) are posted to the google sheet. Could you please tell how can I fix it?
EDIT: I have tried making a GET request manually using Postman to test if the script works. Here is the request:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
