storageb/google-sheets-logging

Log data from an ESP8266 device directly to Google Sheets without a third party service. Log sensor data, send data by pressing a button, and receive data from a Google spreadsheet. (NodeMCU, Wemos D1 mini, Adafruit Feather HUZZAH, etc)

google-sheets-logging's Issues

Function not found: doPost

I implemented the code as recommendations, but after establishing wifi connection and connecting to host, publishing returns this error message. What could be causing this problem?

Connecting to Ypyeng 2g ...
......

Connection established!
IP address: 192.168.1.17
Connecting to script.google.com
Connected
Publishing data...
{"command": "insert_row", "sheet_name": "Sheet1", "values": "1,925,4191"}

<title>Erro</title><style type="text/css" nonce="rDYaOuvfXEeHBGg0FiPF0w">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style>

Script function not found: doPost

Hey I had this issue when I run the ESP8266.cpp file after importing the HTTPSRedirect lib

Hey I had this issue

'class HTTPSRedirect' has no member named 'setInsecure'

any idea what I'm doing wrong? Its pretty much the same code but with my credentials

ESP Stuck on Sudden Internet Failure

Hi,
Thank for the great library. I am facing a minor issue while upload the sensor data to google sheet.
In case of sudden internet failure ESP got Stuck in Following lines of Program.

Part of Code..

// Publish data to Google Sheets
 Serial.println("Publishing data...");
 Serial.println(payload);
 if(client->POST(url, host, payload)){ 
   // do stuff here if publish was successful
 }
 else{
   // do stuff here if publish was not successful
   Serial.println("Error while connecting");
 }

Serial Monitor Display
17:05:51.223 -> Publishing data...
17:05:51.223 -> {"command": "insert_row", "sheet_name": "Sheet1", "values": "28.00,30.00,14,SCAQI211101"}

Is there any way to set any time out for this command

if(client->POST(url, host, payload)){ 
   // do stuff here if publish was successful
 }

Any Assistance will be helpful.

"Error! Not connected to host." when using ISR

Hey,
I'm trying to create a kind of stopwatch and for the best responsibility, I use an interrupt, from which the subroutine to send the data is called.
(When I was trying the raw example it worked and I used as much as I could from the example)
According to the serial monitor, the values of my variables seem to be taken along correctly, but when the payload should get posted, it didn't work, and I got this response:

Publishing data...
{"command": "insert_row", "sheet_name": "Tabellenblatt1", "values": "4,406571,408355"}
Error! Not connected to host.
Error while connecting

This is my code:

#include <Arduino.h>
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"

#define ON_Board_LED 2
#define BUTTON_PIN 0

const char* ssid     = "myWiFi";
const char* password = "myPW";
const char *GScriptId = "my Script-ID";

// Enter command (insert_row or append_row) and your Google Sheets sheet name (default is Sheet1):
String payload_base =  "{\"command\": \"insert_row\", \"sheet_name\": \"Tabellenblatt1\", \"values\": ";
String payload = "";

// Google Sheets setup (do not edit)
const char* host = "script.google.com";
const int httpsPort = 443;
const char* fingerprint = "";
String url = String("/macros/s/") + GScriptId + "/exec";
HTTPSRedirect* client = nullptr;

// Declare variables that will be published to Google Sheets
volatile int run = 0;
volatile uint64_t time_start = 0;
volatile uint64_t time_finish = 0;

volatile bool running = false;
volatile uint64_t last_isr;

void ICACHE_RAM_ATTR ISR() {
  if (millis() - last_isr > 500) { //bounce-filter
    if (!running) {
      time_start = millis();
      running = true;
      last_isr = millis();
    } else {
      time_finish = millis();
      running = false;
      run++;
      last_isr = millis();
      sendData(run, time_start, time_finish);
    }
  }
}

void setup() {
  Serial.begin(9600);        
  delay(10);
  Serial.println('\n');
  
  // Connect to WiFi
  WiFi.begin(ssid, password);             
  Serial.print("Connecting to ");
  Serial.print(ssid); Serial.println(" ...");
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.print(".");
  }
  Serial.println('\n');
  Serial.println("Connection established!");  
  Serial.print("IP address:\t");
  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 for a maximum of 5 times
  bool flag = false;
  for (int i=0; i<5; i++){ 
    int retval = client->connect(host, httpsPort);
    if (retval == 1){
       flag = true;
       Serial.println("Connected");
       break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }
  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    return;
  }
  delete client;    // delete HTTPSRedirect object
  client = nullptr; // delete HTTPSRedirect object

  pinMode(ON_Board_LED,OUTPUT); //--> On Board LED port Direction output
  digitalWrite(ON_Board_LED, HIGH); //--> Turn off Led On Board
  attachInterrupt(digitalPinToInterrupt(BUTTON_PIN), ISR, FALLING);
}

void loop() {
  if (running) {
    digitalWrite(ON_Board_LED, LOW);
  } else {
    digitalWrite(ON_Board_LED, HIGH);
  }
}

void sendData(int run, uint64_t time_star, uint64_t time_finish) {
  static bool flag = false;
  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);
    }
  }
  else{
    Serial.println("Error creating client object!");
  }
  
  // Create json object string to send to Google Sheets
  payload = payload_base + "\"" + run + "," + time_start + "," + time_finish + "\"}";
  
  // Publish data to Google Sheets
  Serial.println("Publishing data...");
  Serial.println(payload);
  if(client->POST(url, host, payload)){ 
    // do stuff here if publish was successful
  }
  else{
    // do stuff here if publish was not successful
    Serial.println("Error while connecting");
  }
}

I'm grateful for any advice on a better solution, thanks

Hardware: Wemos D1 mini

how many cells can we use

I changed gscript code like that:
`
// Enter Spreadsheet ID here
var SS = SpreadsheetApp.openById('---------------id--------------);
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(), "Europe/Istanbul", "dd/MM/YYYY"); // gets the current date
var time_now = Utilities.formatDate(new Date(), "Europe/Istanbul", "HH:mm"); // 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
var value3 = dataArr [3]; // value2 from Arduino code
var value4 = dataArr [7]; // value2 from Arduino code
var value5 = dataArr [5]; // value2 from Arduino code
var value6 = dataArr [6]; // value2 from Arduino code
var value7 = dataArr [7]; // 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
    sheet.getRange('F2').setValue(value3);   // publish value2 from Arduino code to cell F2
    sheet.getRange('G2').setValue(value4);   // publish value2 from Arduino code to cell E2
    sheet.getRange('H2').setValue(value5);   // publish value2 from Arduino code to cell E2
    sheet.getRange('I2').setValue(value6);   // publish value2 from Arduino code to cell E2
    sheet.getRange('J2').setValue(value7);   // 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
    publish_array [5] = value3;   // add value2 from Arduino code to position 4 in publish_array
    publish_array [6] = value4;   // add value2 from Arduino code to position 4 in publish_array
    publish_array [7] = value5;   // add value2 from Arduino code to position 4 in publish_array
    publish_array [8] = value6;   // add value2 from Arduino code to position 4 in publish_array
    publish_array [9] = value7;   // 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.");
}
}`

My problem is: nodemcu gives no error. Insted it gives "Success". But I can't see any variable in H, I , J cells. There is no data there. I changed the values int to String, rearrange my code and functions but no luck. In my nodemcu code, I am working with 'insert_row' commad. I changed it to append_row to try but it did the same. Is there a limit?

i have a problem with Error with request. Response status code: 0 ?? have anyone found this issue?

14:31:42.381 -> {"command": "insert_row", "sheet_name": "Sheet1", "values": "40,195,7020"}
Error with request. Response status code: 0
14:31:42.519 -> Error while connecting

information

Hello, I'm trying to use the library to post values to google sheets.
Strangely I get error values but the first 4 send attempts are successful, after that it stops working altogether.
I don't understand why.

change a dot to a comma

Hello, i export tempearture by bme280 to google sheet. How can I change dots to commas? from 21.23 to 21,23

P.s.
changing the zone in the sheet doesn't help :)

Regards

How to change the time zone.

I tried GMT+6 instead of CST but it is not working

Google-Sheets-Logging/GoogleScripts-example.gs

Line 34 in 7d850ee

var date_now = Utilities.formatDate(new Date(), "CST", "yyyy/MM/dd"); // gets the current date

Push button to push a value to the google sheet.

Hi, I tried to replace the random number generator with a push button to push a value to the google sheet. After burning the code to the NordMCU, everything works fine as I push the button, a value is logged into the Google Sheet. But when I leave the button/ESP8266 idle for a while(more than a minute) and later press the push button, it fails to push the value to the GS. I get 'Error! Not connected to host. Error while connecting' in the serial monitor. But when I press the button again, it pushes the payload successfully. Is there any way to rectify this?

Hangup of the Logging when Internet connection is shortly interrupted

HI,
my logging goes in hangup occasionally (1-2 times /week), this is boring.
The Wemos gets stuck exactly during
if(client->POST(url, host, payload)){
It happens, when exactly during post the internet gets disconnected for a short while. My router makes each day a reconnect with interruption of connection to get a new IP. It is not so rare, that this is falling just into the post procedure, as this takes about 3 seconds, each 15seconds.
Before the post i get the info "Publishing ...", then nothing happens anymore, it stays forever, no return of Success or error message. (Before post Wifi and client was connected)
I need to reset the Wemos to restart.

Does anybody have an idea of rootcause and/or countermeasure?

This is my code of that part, like in your example:

// Publish data to Google Sheets

int retval = client->connect(host, httpsPort);
Serial.print("Client: ");Serial.println(retval);
Serial.println(WiFi.status());

Serial.println("Publishing...");
//Serial.println(payload);
if(client->POST(url, host, payload)){
Serial.println("successful");
}
else{
// do stuff here if publish was not successful
Serial.println("Error while connecting");
}

class httpsredirect has no member named "setPrintResponseBody"

When I upload the code to the ESP8266 I recieved this error

'class httpsredirect' has no member named 'setPrintResponseBody'

Do you have any idea's why?

Thanks