IMost of the functions I need are working.
My basic program produces data which will be put to a Google spreadsheet.
I have been following a "cookbook" program from the website
This uses the function/library HTTPSReDirect and a Google script and the Arduino code.
My last problem is how to get the current time of day (and date) when a run is finished, to associate it with the results. (I can put these at the top of the sheet, but I need it for each line.)
I include here the entire program as well as the Google script here. It MAY be of some help.
I suspect that my solution is just a line or two, but I have already spent too much time trying to understand things. Any help will be appreciated. (I have truncated a lot to fit within the 9000 char. limit.)
- Matt
Google Web App Script:
// BB_Sterilizer_101_19Mar20 - Google App Script
var SS = SpreadsheetApp.openById('1xl22gxR-V3r28mesihZJiPkojStyKqch1Yu1PijdSss');
var sheet = SS.getSheetByName('Data_2020'); // Above changed - 19 Mar 2020
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;
}
switch (parsedData.command) {
case "appendRow":
var tmp = SS.getSheetByName(parsedData.sheet_name);
var nextFreeRow = tmp.getLastRow() + 1;
var dataArr = parsedData.values.split(",");
tmp.appendRow(dataArr);
str = "Success";
SpreadsheetApp.flush();
break;
}
return ContentService.createTextOutput(str);
}
else{
return ContentService.createTextOutput("Error! Request body empty or in incorrect format.");
}
}
function doGet(e){
var val = e.parameter.value;
var cal = e.parameter.cal;
var read = e.parameter.read;
if (cal !== undefined){
return ContentService.createTextOutput(GetEventsOneWeek());
}
if (read !== undefined){
var now = Utilities.formatDate(new Date(), "EDT", "yyyy-MM-dd'T'hh:mm a'Z'").slice(11,19);
sheet.getRange('D1').setValue(now); // mcb
sheet.getRange('C1').setValue(count);
return ContentService.createTextOutput(sheet.getRange('A1').getValue());
}
if (e.parameter.value === undefined)
return ContentService.createTextOutput("No value passed as argument to script Url.");
var range = sheet.getRange('A1');
var retval = range.setValue(val).getValue();
var datenow = Utilities.formatDate(new Date(), "EST", "yyyy-MM-dd'T'hh:mm a'Z'").slice(0,10); // mcb
var timenow = Utilities.formatDate(new Date(), "EST", "yyyy-MM-dd'T'hh:mm a'Z'").slice(11,19); // mcb
sheet.getRange('C1').setValue(datenow);
sheet.getRange('D1').setValue(timenow);
sheet.getRange('B1').setValue("Sterilizer:");
if (retval == e.parameter.value)
return ContentService.createTextOutput("Successfully wrote: " + e.parameter.value + "\ninto spreadsheet.");
else
return ContentService.createTextOutput("Unable to write into spreadsheet.\nCheck authentication and make sure the cursor is not on cell 'A1'." + retval + ' ' + e.parameter.value);
}
function GetEventsOneWeek(){
//Deleted - not used
}
//Logger.log(str);
return str;
}
Arduino NodeMCU 8266 Code:
//AA_Sterilizer_101_19Mar20
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
float x ;
float h;
float t;
String sheetHumid = "";
String sheetTemp = "";
String sheetData_2020 = "";
String sheetXXX = "";
String sheetYYY = "";
String sheetZZZ = "";
String datastring = "";
const char* ssid = "A47FB0"; //replace with our wifi ssid
const char* password = "96880104"; //replace with your wifi password
const char* host = "script.google.com";
String url = String("/macros/s/") + GScriptId + "/exec?value=Steri."; // Write Teperature to Google Spreadsheet at cell A1
// Fetch Google Calendar events for 1 week ahead
String url2 = String("/macros/s/") + GScriptId + "/exec?cal"; // Write to Cell A continuosly
String payload_base = "{\"command\": \"appendRow\", \
\"sheet_name\": \"Data_2020\", \
\"values\": ";
String payload = "";
String state = " "; // This compiled. Room for three characters??
HTTPSRedirect* client = nullptr;
void setup() {
delay(100);
Serial.begin(115200);
Serial.println();
Serial.print("Connecting to wifi: ");
Serial.println(ssid);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
// Use HTTPSRedirect class to create a new TLS connection
client = new HTTPSRedirect(httpsPort);
client->setInsecure();
client->setPrintResponseBody(true);
client->setContentTypeHeader("application/json");
Serial.print("Connecting to ");
Serial.println(host); //try to connect with "script.google.com"
// Try to connect for a maximum of 5 times then exit
bool flag = false;
for (int i = 0; i < 5; i++) {
int retval = client->connect(host, httpsPort);
if (retval == 1) {
flag = true;
break;
}
else
Serial.println("Connection failed. Retrying...");
}
if (!flag) {
Serial.print("Could not connect to server: ");
Serial.println(host);
Serial.println("Exiting...");
return;
}
// Finish setup() function in 1 second since it will fire watchdog timer and will reset the chip.
//So avoid too many requests in setup()
Serial.println("\nWrite into cell 'A1'");
Serial.println("------>");
// fetch spreadsheet data
client->GET(url, host);
// client->GET(url2, host);
//
Serial.print(client->GET(url, host));
//Serial.print(client->GET(url2, host));
Serial.println("\nGET: Fetch Google Calendar Data:");
Serial.println("------>");
// fetch spreadsheet data
client->GET(url2, host);
delete client;
client = nullptr;
} // End of setup
void loop() {
static int error_count = 0;
static int connect_count = 0;
const unsigned int MAX_CONNECT = 20;
static bool flag = false;
int T;
int H;
Serial.println("Start to assemble for sheet");
// orig: payload = payload_base + "\"" + sheetData-2020 + "," + sheetXXX ;
payload = payload_base + "\"" ;
// payload = payload_base ;
for (int j = 0; j < 20; j++)
{
T = (( 10 + j) * 10 + 100); // Actually will be temperaure.
H = (j % 3) + 1; // H is used here ONLY for phony state value!
state = " WU";
CDC[j] = String( T ) + " " + state; // T = temperature
String Timedate = "";
payload = payload + "," + CDC[j] ;
Serial.print(payload);
} // end of for loop
payload = payload + "\"}"; // to finish it up.
Serial.print(payload);
Serial.println("End of For Loop");
Serial.println("Payload is complete here");
if (!flag) {
client = new HTTPSRedirect(httpsPort);
client->setInsecure();
flag = true;
client->setPrintResponseBody(true);
client->setContentTypeHeader("application/json");
}
if (client != nullptr) {
if (!client->connected()) {
client->connect(host, httpsPort);
client->POST(url2, host, payload, false);
Serial.print("Sent : "); Serial.println("Temp and Humid");
}
}
else {
error_count = 5;
}
if (connect_count > MAX_CONNECT) {
connect_count = 0;
flag = false;
delete client;
return;
}
if (client->POST(url2, host, payload)) {
;
}
else {
++error_count;
}
if (error_count > 3) {
delete client;
client = nullptr;
ESP.deepSleep(0);
}
delay(3000);
}
} // loopend