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”

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:

2 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

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.