Sending ESP8266 sensordata to GoogleSheet

If you want to store sensor data coming from your ESP8266 (or ESP32) project ‘in the cloud’, then there is a hoist of options. One option is what most people will already have available, namely GoogleSheet.
There are a number of tutorials on how to set that up, and i will just describe how I did it here, as part of a larger project I will publish later.

What we will do is:

  • Set up a new Google Sheet
  • Add a script to that sheet that places data in the sheet
  • Make a function for the ESP8266 that calls that script and hands it the data we want stored.

During the course of creating the GoogleSheet and the GoogleScript, we will need to take note of two numbers: The “sheet ID” and the “script ID”. The latter is also referred to as the GAS_ID
The GAS_ID usually slooks something like: “AKfycbxp……………………”
We will put the GAS_ID in the ESP code calling the script we created.
We will put the sheet ID in the GoogleScript, so the script knows in what sheet to place it.

Creating the Google sheet and acquiring the sheet ID
Go to your google drive on “drive.google.com”
Select New-GoogleSheets
A new sheet will becreated, give it a meaningful name. If you then look at the URL that will look something like:
https://docs.google.com/spreadsheets/d/1UML3C_P1H……..hqCxos/edit#gid=0
The part between “/d/” and “/edit$gid-0” is the sheet ID. Copy that somewhere.

Creating the Google script and acquiring the GAS_ID
In your new GoogleSheet, go to “Tools-Script Editor”
Give the script the same name as you gave your Sheet

Paste the following code:

function doGet(e) { 
  Logger.log( JSON.stringify(e) );  // view parameters
  var result = 'Ok'; // assume success
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = '197ZGbUbbFmKtV-TPEtxYCXVRxt7YB00HQukSo-QwX14'; 		// Spreadsheet ID
    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();		// get Active sheet //getSheetByName('naam') voor ander blad
    var newRow = sheet.getLastRow() + 1;						
    var rowData = [];
    var sheet2=SpreadsheetApp.openById(sheet_id).getSheetByName('Sheet2');
    var newRow2=sheet2.getLastRow() + 1;
    var rowData2 = [];
    rowData[0] = new Date();    // Timestamp in column A
    rowData2[0] = new Date();
    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'temperature': //Parameter
          rowData[1] = value; //Value in column B
          result = 'Written on Column B';
          rowData2[1]= value;
          break;
        case 'humidity': //Parameter
          rowData[2] = value; //Value in column C
          result += ', Written on column 3';
          break;
        case 'moisture' :
          rowData[3]=value;
          result += ', Written in Column D';
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));
    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    var newRange2= sheet2.getRange(newRow2,1,1, rowData.length)
    newRange.setValues([rowData]);
    newRange2.setValues([rowData]);
  }
  // Return result of operation
  return ContentService.createTextOutput(result);
}
/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Change the spreadsheet ID into your spreadsheet ID

Next go to Publish-Deploy as Web App

Change the acces type to ‘anyone, even anonymous’ and deploy

After deployment, you will be shown a url. Copy that as it contains the GAS_ID


The URL will look like https://script.google.com/macros/s/AKfycbxPrTJNtUgXxqMX…
The Script ID  (GAS_ID) is AKfycbxPrTJNtUgXxqMX…
This is the ID we will use in the ESP8266 code.
Before you leave the Google Sheet, go back to the Sheet itself and make sure there is a sheet1 and a sheet2.

This is not an absolute necessity, but it allows me to show how you can write to different sheets in your main sheet. In your neck of the woods those might be called “Sheet1” and “Sheet2”

Setting “less secure apps”
A final thing you need to do is to tell Google it should allow your applications (in this case, a connection coming from your ESP). To do that go to the less secure aps setting, and switch that ON

Switch to “ON”

The ESP8266 code

String GAS_ID = "";//getactivespreadsheetID
const char* fingerprint = "46 B2 C3 44 9C 59 09 8B 01 B6 F8 BD 4C FB 00 74 91 2F EF F6";
const char* host = "script.google.com";
const int httpsPort = 443;

void sendData(int x, int y, byte z)
{
  Serial.print("connecting to ");
  Serial.println(host);
  gsclient.setInsecure();
  if (!gsclient.connect("script.google.com", httpsPort)) {
    Serial.println("connection failed");
    return;
  }

  if (gsclient.verify(fingerprint, host)) {
    Serial.println("certificate matches");
  } else {
    Serial.println("certificate doesn't match");
  }
  String string_x     =  String(x, DEC);
  String string_y     =  String(y, DEC);
  String string_z     =  String(z, DEC);
Serial.println(string_x);
Serial.println(string_y);
Serial.println(string_z);
  String url = "/macros/s/" + GAS_ID + "/exec?temperature=" + string_x + "&humidity=" + string_y + "&moisture=" + string_z;
  Serial.print("requesting URL: ");
  Serial.println(url);

  gsclient.print(String("GET ") + url + " HTTP/1.1\r\n" +
                 "Host: " + host + "\r\n" +
                 "User-Agent: BuildFailureDetectorESP8266\r\n" +
                 "Connection: close\r\n\r\n");

  Serial.println("request sent");

  while (gsclient.connected()) {
    String line = gsclient.readStringUntil('\n');
    if (line == "\r") {
      Serial.println("headers received");
      break;
    }
  }
  String line = gsclient.readStringUntil('\n');
  Serial.println(line);
  if (line.startsWith("{\"state\":\"success\"")) {
    Serial.println("esp8266/Arduino CI successfull!");
  } else {
    Serial.println("esp8266/Arduino CI has failed");
  }
  Serial.println("reply was:");
  Serial.println("closing connection");
}

This function takes 3 arguments that will be placed in  the Google sheet.
Back to the Script

The Lines:

var sheet_id = '1DdQ-dc6bpsP7EtvtvBHvl-cZai1qUC6QfNtaxTnh6w0'; 	
var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();

go to the proper sheet and open the sheet that is active.
The lines:

var newRow = sheet.getLastRow() + 1;						
    var rowData = [];
    var sheet2=SpreadsheetApp.openById(sheet_id).getSheetByName('Sheet2');
    var newRow2=sheet2.getLastRow() + 1;
    var rowData2 = [];

Create a new Row in the first sheet and create a new Row in the second sheet. Beware ‘Sheet2’ is the NAME ofyor second sheet. if you have given it a different name, that shoud be reflected here
The lines:

rowData[0] = new Date();    // Timestamp in column A
rowData2[0] = new Date(); //Timestamp Sheet2 column A

insert a timestamp in the first column of both sheets
The lines:

for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'temperature': //Parameter
          rowData[1] = value; //Value in column B
          result = 'Written on Column B';
          rowData2[1]= value;
          break;
        case 'humidity': //Parameter
          rowData[2] = value; //Value in column C
          result += ', Written on column 3';
          break;
        case 'moisture' :
          rowData[3]=value;
          result += ', Written in Column D';
          break;
        default:
          result = "unsupported parameter";
      }

insert the 3 parameters that are received in the first sheet and the second sheet. The sole reason I create two sheets that get the same data is to demonstrate how to use two sheets.
The example is only logging 3 values, but when looking at the code it should be quite obvious on how to add more data.
One warning: when you edit the script, the previous versions remain, when done, you need to select the right version of the script to use:

18 thoughts on “Sending ESP8266 sensordata to GoogleSheet”

  1. Very nice. I did something like that for a client (not with IOT data) just for them to make neat sorts, filters, graphs without jumping through complex browser-to-excel-ish interfaces etc. It works really well.

    1. Thanks Jeroen, it is I deed a simple solution to make data available to a hoist of presentation possibilities.
      I am also sending data to MariaDB, but you need more maintenance of that coz of the server it is on. InfluxDB/Grafana works well too, but GoogleSheet the easiest and you don’t need to bother with backups

  2. Thanks a lot for sharing this tutorial! I am working on a data logger and this post is really helpful for me. I am going to use an Ammonia gas sensor along with temperature and humidity data to store for a month.

  3. Good explanation. however i have really many difficulties to make the ssl connection. I get error 401. I think i cannot connect to the port, but dont know if its my fingerprint which is no good.- how did you generate your fingerprint ?

    1. The 401 error is an unauthorized HTTP request. The Fingerprint was a general GoogleSheet fingerprint, but at the time it probably was not even needed as I use setInsecure();.It seems that since mid january google has changed something and I am not sure if it is refusal of insecure connection or a mandatory fingerprint.
      I have not tested it myself for a while so I will do that in order to see if there is a general problem or that it is just you (Spoiler: it is just you, see my 2nd reply).
      A new fingerprint can be generated. See under section IIId here https://github.com/electronicsguy/ESP8266/tree/master/HTTPSRedirect

      1. Hi Ed, You are the MAN, thanks for the tip. It was the setting under my account ! I had to change, and it worked just perfect. I was almost going insane and had the worst weekend ever… when i thought all my plans of using google sheets for data loggin in many different ways were going down the drain !- Now all is good again 🙂 Thanks again !!

    2. OK I checked my GoogleSheet and data is still being uploaded. For me ofcourse that is a good thing but that leaves the fact it is not working for you.
      One thing I think off is that I have set my google account to accept less secure apps. I did that to be able to use an ESP to send mails through my gmail account, and it is well possible that affects GoogleDocs too (it sets an OAuth 2.0 token I believe). So I suggest to try that in your settings: https://myaccount.google.com/lesssecureapps

  4. Thank you for the tutorial but I got an error.

    gsclient.setInsecure(); was not declared in this scope. Could you please help me?

      1. Thank you so much. Yes, I change it to gsclient and it works. Thanks again for your help

      1. The code snippet I gave is a “routine” / “procedure” that needs to be called from your main program.
        Your main program needs to do a couple of things, such as declare variables, include libraries, setup objects and make the WiFi connection.
        One of the libraries you need to include for that is the WiFiClientSecure.h. The object of the WiFiClientSecure.h library needs a name. Name that ‘gsclient’. Like so:

        #include “WiFiClientSecure.h”
        WiFiClientSecure gsclient;

        You need to replace the quote signs with fishhooks ofcourse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.