'Regional time and date on apps script to google sheet not converting to local time
I’m based in Australia and tried out the awesome ESP 8266 code by Sujay Phadke (electronicsguy) https://github.com/electronicsguy/ESP8266 - great code and project but I cannot get the date and time to sync with Australian time – it seems to be GMT. I’ve tried a few variations on lines 36 and 37 var date_now = Utilities.formatDate(new Date(), "CST", "yyyy/MM/dd"); // gets the current date var time_now = Utilities.formatDate(new Date(), "CST", "hh:mm:ss a"); // gets the current time and have set the regional time on both the Ap script ans spreadsheet to local (Australian) time – but nothing I do seems to make it work. Any ideas? Apps script is as follows:
// Example Google Scrips code to upload data to Google Sheets from Arduino/ESP8266
// Follow setup instructions found here:
// https://github.com/StorageB/Google-Sheets-Logging
// contact: [email protected]
//
// This example uses the HTTPSRedirect library by Sujay Phadke
// https://github.com/electronicsguy/ESP8266
// Enter Spreadsheet ID here
var SS = SpreadsheetApp.openById('3213k-okfjdk-gkeJHjd87-vdKei-lKQDIc-a2a_Hjg4');
var str = "";
function doPost(e) {
var parsedData;
var result = {};
try {
parsedData = JSON.parse(e.postData.contents);
}
catch(f){
return ContentService.createTextOutput("Error in parsing request body: " + f.message);
}
if (parsedData !== undefined){
var flag = parsedData.format;
if (flag === undefined){
flag = 0;
}
var sheet = SS.getSheetByName(parsedData.sheet_name); // sheet name to publish data to is specified in Arduino code
var dataArr = parsedData.values.split(","); // creates an array of the values to publish
var date_now = Utilities.formatDate(new Date(), "CST", "yyyy/MM/dd"); // gets the current date
var time_now = Utilities.formatDate(new Date(), "CST", "hh:mm:ss a"); // gets the current time
var value0 = dataArr [0]; // value0 from Arduino code
var value1 = dataArr [1]; // value1 from Arduino code
var value2 = dataArr [2]; // value2 from Arduino code
// read and execute command from the "payload_base" string specified in Arduino code
switch (parsedData.command) {
case "insert_row":
sheet.insertRows(2); // insert full row directly below header text
//var range = sheet.getRange("A2:D2"); // use this to insert cells just above the existing data instead of inserting an entire row
//range.insertCells(SpreadsheetApp.Dimension.ROWS); // use this to insert cells just above the existing data instead of inserting an entire row
sheet.getRange('A2').setValue(date_now); // publish current date to cell A2
sheet.getRange('B2').setValue(time_now); // publish current time to cell B2
sheet.getRange('C2').setValue(value0); // publish value0 from Arduino code to cell C2
sheet.getRange('D2').setValue(value1); // publish value1 from Arduino code to cell D2
sheet.getRange('E2').setValue(value2); // publish value2 from Arduino code to cell E2
str = "Success"; // string to return back to Arduino serial console
SpreadsheetApp.flush();
break;
case "append_row":
var publish_array = new Array(); // create a new array
publish_array [0] = date_now; // add current date to position 0 in publish_array
publish_array [1] = time_now; // add current time to position 1 in publish_array
publish_array [2] = value0; // add value0 from Arduino code to position 2 in publish_array
publish_array [3] = value1; // add value1 from Arduino code to position 3 in publish_array
publish_array [4] = value2; // add value2 from Arduino code to position 4 in publish_array
sheet.appendRow(publish_array); // publish data in publish_array after the last row of data in the sheet
str = "Success"; // string to return back to Arduino serial console
SpreadsheetApp.flush();
break;
}
return ContentService.createTextOutput(str);
} // endif (parsedData !== undefined)
else{
return ContentService.createTextOutput("Error! Request body empty or in incorrect format.");
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
