How to post data to Google sheets using ESP8266

Soil Moisture to Google Sheets

In the past couple years, the ESP8266 platform has flourished dramatically and emerged as one of the most popular hardware tools among electronics hobbyists and IoT enthusiasts. Packed with a 32-bit RISC CPU running at 80 MHz, a fully integrated WiFi radio with TCP/IP protocol stack, serial peripherals (I2C, SPI, and UART), an ADC channel, and general purpose I/O pins, the ESP8266 is the most integrated and affordable WiFi solution available in the current IoT market space. An ESP8266 hardware, like NodeMCU and ESP-01, can directly interface with sensors using its peripherals and upload the sensor measurements to a local or a remote web server via internet. Right now, there are already quite a bit of cloud IoT platforms (ThingSpeak, thinger.io, TESPA.io, Xively, … the list is getting bigger everyday) that provides APIs and tools to allow the ESP8266 users to directly upload their sensor readings online for real-time visualization and global access. If you are a regular user of Google Drive, like me, you would find a Google sheet more approachable than all those IoT cloud platforms. In this tutorial, I will describe a method of connecting the ESP8266 device directly to a Google sheet for storing the sensor data without using any third party plugin. For illustration, I am using a NodeMCU board that reads the analog output from a soil moisture sensor inserted into one my flower pots and directly connects to a spreadsheet on my Google Drive for storing the data.

Soil Moisture to Google Sheets

ESP8266 data logging to Google spreadsheets

Related Posts

Check out How to send email and text messages using ESP8266.
Check out our weather web server project for local web hosting of the sensor readings using ESP8266.
Check out our ThingSpeak temperature and humidity logger project for remote server data logging example using ESP8266

Basically there are two parts to this project. The first part is setting up the ESP8266 hardware to read the soil moisture sensor output. The second part involves creating a Google sheet and configuring it to accept the sensor data sent by the ESP8266 module over the internet using a Google App Script that is provided with this tutorial.

Hardware setup

This project uses a very simple and inexpensive soil moisture sensor (one you can buy on eBay or Aliexpress for $1) kit consisting of two exposed metal pads and an instrumentation circuit board. The exposed pads act as probes for sensing the moisture level within the soil. The more water in the soil means higher conductivity between the two pads, and vice-versa. The accompanying instrumentation board provides an analog output voltage that varies with the moisture level of the soil. The instrumentation circuit is powered with a 3.3V supply and its output is fed to the analog input channel (A0) of the NodeMCU.

Soil moisture sensor consisting of exposed metal pads and an instrumentation circuit

Soil moisture sensor consisting of exposed metal pads and an instrumentation circuit

Note: The ESP8266 analog input can handle the maximum input voltage of 1V. However, the NodeMCU implements a voltage divider circuit on board to expand the range of input voltage to 3.3V.

Software

Let’s first try to understand what really happens when we try to send data to Google’s end. We will obviously be sending data using a GET request over an URL which has a pattern of https://script.google.com/….. When you enter this URL in a web browser, the Google’s server responds back asking the browser to redirect to another URL with domain script.googleusercontent.com with a new GET request. For a web browser, the URL redirection is a very common thing and it works without any problem. However, for ESP8266, this is not straight forward. The ESP8266 needs to correctly decode the header information received from the first server to extract the redirect URL and make a second GET request to the new server. To make this redirection simpler, Sujay Phadke, aka electronicsguy, has shared on GitHub a beautiful piece of code in the form of Arduino Library named HTTPSRedirect. There are other examples of Arduino posting data to Google spreadsheets using a third party API service, like pushingbox, for handling Google’s https requirement and URL redirection. The use of HTTPSRedirect library makes the task much simpler by avoiding the need of any third party service. So, the first thing you need to do is to copy the HTTPSRedirect library files from GitHub and install into your Arduino libraries folder. I have also posted the zipped library files at the following link for convenience.

Download HTTPSRedirect Library

In order to install it on your machine, simply download the above zipped file, unzip it, and move the folder named HTTPSRedirect into your Arduino’s libraries location. On Windows PC, it typically goes to C:\Users\\Documents\Arduino\libraries\ .

Arduino IDE Library Path

Arduino IDE Library Path on a Windows PC

Make sure both HTTPSRedirect.cpp and HTTPSRedirect.h files exist inside the copied library folder as shown above.

Program ESP8266 to send data to Google Sheets

Posted below is the ESP8266 code written using Arduino IDE for reading the soil moisture sensor output and posting it to a Google Sheet on your Google Drive. In order to make it work for your case, you will need to update the ssid and password matching to your WiFi network. Also, you will also need to get the *GScriptId, which can only be obtained after publishing the required Google App Scripts. The process of obtaining the *GScriptId is explained towards the bottom of this tutorial. The sensor data are posted to the Google sheet every 15 minutes.

// BY: Akshaya Niraula
// ON: Nov 11, 2016
// AT: http://www.embedded-lab.com/

#include <ESP8266WiFi.h>
#include “HTTPSRedirect.h”

const char* ssid = “–Your-SSID–“;
const char* password = “–Your-Password–“;

// The ID below comes from Google Sheets.
// Towards the bottom of this page, it will explain how this can be obtained
const char *GScriptId = “—Your-Google-Script-ID–=”;

// Push data on this interval
const int dataPostDelay = 900000;     // 15 minutes = 15 * 60 * 1000

const char* host = “script.google.com”;
const char* googleRedirHost = “script.googleusercontent.com”;

const int httpsPort =     443;
HTTPSRedirect client(httpsPort);

// Prepare the url (without the varying data)
String url = String(“/macros/s/”) + GScriptId + “/exec?”;

const char* fingerprint = “F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD”;

// We will take analog input from A0 pin
const int AnalogIn                 = A0;

void setup() {
    Serial.begin(115200);
    Serial.println(“Connecting to wifi: “);
    Serial.println(ssid);
    Serial.flush();

    WiFi.begin(ssid, password);
    while (WiFi.status() != WL_CONNECTED) {
            delay(500);
            Serial.print(“.”);
    }
    Serial.println(” IP address: “);
    Serial.println(WiFi.localIP());

    
    Serial.print(String(“Connecting to “));
    Serial.println(host);

    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…”);
    }

    // Connection Status, 1 = Connected, 0 is not.
    Serial.println(“Connection Status: “ + String(client.connected()));
    Serial.flush();
    
    if (!flag){
            Serial.print(“Could not connect to server: “);
            Serial.println(host);
            Serial.println(“Exiting…”);
            Serial.flush();
            return;
    }

    // Data will still be pushed even certification don’t match.
    if (client.verify(fingerprint, host)) {
            Serial.println(“Certificate match.”);
    } else {
            Serial.println(“Certificate mis-match”);
    }
}

// This is the main method where data gets pushed to the Google sheet
void postData(String tag, float value){
    if (!client.connected()){
            Serial.println(“Connecting to client again…”);
            client.connect(host, httpsPort);
    }
    String urlFinal = url + “tag=” + tag + “&value=” + String(value);
    client.printRedir(urlFinal, host, googleRedirHost);
}

// Continue pushing data at a given interval
void loop() {
    
    // Read analog value, in this case a soil moisture
    int data = 1023 analogRead(AnalogIn);

    // Post these information
    postData(“SoilMoisture”, data);
    
    delay (dataPostDelay);
}

You can also download the NodeMCU code from the following link.

Download NodeMCU code
Create Google Sheets

Create a Google spreadsheet in your Google Drive and name it something like DataCollector. Rename the current/active sheet to Summary and add a second sheet, call it DataLogger. From the URL address bar of the sheet, copy the string between d/ and /edit and save it somewhere. This is your spreadsheet’s unique sharing key, which will be needed in the Google Apps scripts later.

Spreadsheet URL

Unique sharing key for your Google spreadsheet can be found in the URL

Note: The name of the Google spreadsheet does not matter much because we will be using the sharing key of the sheet, which is unique, in the Google Apps Scripts to access it. However, the sheets name (Summary and DataLogger) have to match with what you put in your Google Apps Scripts (described later).

In the Summary sheet, type Last Modified On on cell A1, DataLogger Count on A2, and Next Read Time on A3. In cell B2, type a formula that will give us the data count ‘=counta(DataLogger!D:D) -1‘ In B3, type this equation, =B1 + TimeValue(“00:15”), which will simply add 15 minute to last modified date time. To support a gauge chart in Google Sheets, some information from A6 to B7 has been added, as showing in the image below

Goolge Sheets - Summary tag

Goolge Sheets – Summary tag

In the DataLogger sheet, type ID, DateTime, Tag, and Value in A1, B1, C1 and D1, respectively.

Google Sheets Data Logger Tab

Google Sheets Data Logger Tab

Google Apps Scripts

To create a Google App Scripts, go to Tools > Script Editor from the Google Sheets. In the code section, paste the code below. The code or script can be saved under any name.

// BY: Akshaya Niraula
// ON: 2016 November 12th.
// AT: http://www.embedded-lab.com/.....
 
 
// Steps are valid as of 2016 November 12th.
// 0) From Google spreadsheet, Tools &gt; Scriipt Editor...
// 1) Write your code
// 2) Save and give a meaningful name
// 3) Run and make sure "doGet" is selected
//    You can set a method from Run menu
// 4) When you run for the first time, it will ask 
//    for the permission. You must allow it.
//    Make sure everything is working as it should.
// 5) From Publish menu &gt; Deploy as Web App...
//    Select a new version everytime it's published
//    Type comments next to the version
//    Execute as: "Me (your email address)"
//    MUST: Select "Anyone, even anonymous" on "Who has access to this script"
//    For the first time it will give you some prompt(s), accept it.
//    You will need the given information (url) later. This doesn't change, ever!
 
// Saving the published URL helps for later.
// https://script.google.com/macros/s/---Your-Script-ID--Goes-Here---/exec
// https://script.google.com/macros/s/---Your-Script-ID--Goes-Here---/exec?tag=test&amp;value=-1
 
// This method will be called first or hits first  
function doGet(e){
  Logger.log("--- doGet ---");
 
 var tag = "",
     value = "";
 
  try {
 
    // this helps during debuggin
    if (e == null){e={}; e.parameters = {tag:"test",value:"-1"};}
 
    tag = e.parameters.tag;
    value = e.parameters.value;
 
    // save the data to spreadsheet
    save_data(tag, value);
 
 
    return ContentService.createTextOutput("Wrote:\n  tag: " + tag + "\n  value: " + value);
 
  } catch(error) { 
    Logger.log(error);    
    return ContentService.createTextOutput("oops...." + error.message 
                                            + "\n" + new Date() 
                                            + "\ntag: " + tag +
                                            + "\nvalue: " + value);
  }  
}
 
// Method to save given data to a sheet
function save_data(tag, value){
  Logger.log("--- save_data ---"); 
 
 
  try {
    var dateTime = new Date();
 
    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/..../edit 
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Your-Google-Sheet-ID--Goes-Here---/edit");
    var summarySheet = ss.getSheetByName("Summary");
    var dataLoggerSheet = ss.getSheetByName("DataLogger");
 
 
    // Get last edited row from DataLogger sheet
    var row = dataLoggerSheet.getLastRow() + 1;
 
 
    // Start Populating the data
    dataLoggerSheet.getRange("A" + row).setValue(row -1); // ID
    dataLoggerSheet.getRange("B" + row).setValue(dateTime); // dateTime
    dataLoggerSheet.getRange("C" + row).setValue(tag); // tag
    dataLoggerSheet.getRange("D" + row).setValue(value); // value
 
 
    // Update summary sheet
    summarySheet.getRange("B1").setValue(dateTime); // Last modified date
    // summarySheet.getRange("B2").setValue(row - 1); // Count 
  }
 
  catch(error) {
    Logger.log(JSON.stringify(error));
  }
 
  Logger.log("--- save_data end---"); 
}

Important Note: In the above script, the sheet names should match to those we want to be populated.

    var summarySheet = ss.getSheetByName("Summary");
    var dataLoggerSheet = ss.getSheetByName("DataLogger");

Similarly, you would also need to edit the spreadsheet sharing key in the script to match with yours (one you copied from the spreadsheet URL earlier).

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Your-Google-Sheet-ID--Goes-Here---/edit");

Download Goolge App Scripts

Deploy the contents as Web app

Next step is to publish the script so that it is accessible through an URL. In order to do that, Publish > Deploy as Web App.

Note: Whenever you modify your code, you have to create a “New” Project version and publish it otherwise you will still be hitting the same old code.

Copy the Current web app URL and save it somewhere as we will need it for extracting the GScriptID. I usually save it in my code as a comment. The web app URL would look like this:

https://script.google.com/macros/s/–Your Google Script ID–/exec?tag=test&value=-1

The string between s/ and /exec? is your GScriptID.

Deploy As Web App

Deploy As Web App

During publishing, Google will ask to grant some of the permissions and you need to allow those.

Test the Progress

If things been followed properly, the following test should go successful. Copy the web app URL that you received during publishing, format it like shown above and paste this string in your web browser URL field. You should see the following information populated in the DataLogger sheet as shown below.

Test data posted to Google sheet

Test data posted to Google sheet

So far so good? If not, double check the steps. If the URL entered in your browser populated the values in your sheet then update your GScriptID in the ESP8266 code posted earlier.

Output

I added a line chart to visualize the time series of sensor data posted on the DataLogger sheet. The source range for the chart is big enough to cover any new data sent by ESP8266. You can also add a gauge to display the most recent soil moisture data.

Charts based on data from ESP8266 on Google Sheets

Charts based on data posted on DataLogger sheet by NodeMCU

Here’s a quick view of the DataLogger sheet. The moisture data are recorded with time stamps.

Google Sheets Data Logger Tag with data from ESP8266

Sensor data posted by ESP8266 with a minute interval

Related Posts

38 comments

  • Excellent! Thank you for your sharing.

  • I’m trying to implement the base code you provide, but I keep getting a missing library error:

    C:\Arduino\libraries\HTTPSRedirect/HTTPSRedirect.h:7:30: fatal error: WiFiClientSecure.h: No such file or directory
    #include

    GitHub search shows that library should be part of the ESP8266Wifi_master Lib, but I can’t include it…I’m obviously a noob, so a little help would be appreciated!

    • Not really sure…
      Compare this image with your folder…
      Library

      • can’t post images, but I have the same .cpp and .h files, and additionally:
        Folder named “Extra”
        An example sketch called GoogleDocs.ino
        and a README.md

        The Extra folder contains a shot of the creator’s google calendar in .jpg, and a file called code.gs…that is all!

        • Oh, and thank you for the super fast response! Sorry to have missed that!

          • I opened the HTTPSredirect.h, and here’s where it’s calling for that WiFiClientSecure.h library:
            /* HTTPS with follow-redirect
            * Created by Sujay S. Phadke, 2016
            * All rights reserved.
            *
            */

            #include

            class HTTPSRedirect : public WiFiClientSecure {
            private:
            const int httpsPort;
            const char* redirFingerprint;
            bool fpCheck = false;
            bool keepAlive = true;
            bool verboseInfo = false;

            public:
            HTTPSRedirect(const int, const char*, bool);
            HTTPSRedirect(const int);
            ~HTTPSRedirect();

            bool printRedir(const char*, const char*, const char*);
            bool printRedir(String&, const char*, const char*);
            String createRequest(const char*, const char*);
            void fetchData(bool, bool);

            };

            I can’t find a library of that name to include in my sketch, and don’t quite get why I’m the only one who is apparently encountering this…

            Thanks again!

        • Moderation is taking a long time, so sorry for these comments piling up. I’ve fixed the issue I was having previously with the missing library by upgrading the IDE, and re-assuring that the ESP8266 core was installed correctly. Since doing that, I can get your code to work just fine. However, if I try to modify it at all, I get the following on the serial window:
          Connecting to script.google.com
          Connection Status: 1
          Certificate mis-match
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…
          Connecting to client again…

          The modifications I’m attempting are to have it issue a string (“Pump 1 is ON”) when a digital pin is high, and (“Pump 1 is OFF”) when that pin goes low. I don’t need the ‘data’ varriable, so I just set the int data = 0, since I’m not doing any analog reading. I tested this in your code, and it worked, but on mine (a little more complex), it compiles, uploads, but cannot connect to the script. I can’t see anything that would matter, but this is completely new to me!

          • Ok mod- feel free to delete all my posts that are waiting, but do please pass through the one that said “THANKS” because I got everything working… here’s the scoop if you want to post:

            I discovered the “Connecting to client again…” messages were tied to the digital pins I’m using going high or low. If they switch, I got the message. That told me that something was causing the postData function to crash. The Strings I was trying to pass were typed out with spaces. I thought that’d be okay, but once I ditched the spaces (after noticing your tag was SoilMoisture), it is working perfectly now.

  • I followed all your instructions, I am not getting the test results written to the DataLogger spreadsheet. I double checked the ss URL.

  • Hello all,
    I am the author of the HTTPSRedirect library. In case you have any questions, please check out the github page and open a new “issue” there. I have also launced version 2 of the library. Please see the readme for the details and use this only in future projects. Thanks.

    github: http://bit.ly/1SopEug

  • I met a problem in arduino IDE

    This is the problem

    D:\????辣憭閱獢\source_code\data_to_google\data_to_google.ino:6:26: fatal error: ESP8266WiFi.h: No such file or directory

    compilation terminated.

    exit status 1
    Error compiling for board Arduino Nano.

    I try to download ESP8266WiFi.h , but the file still wrong

    I would like to know which side of the problem or where to download it?

    Thank you

  • Hi
    After modifying my esp code( increasing the delays ) and repositioning my router and esp to a clearer line of sight I am getting a much more reliable data transfer. For information it looks as if a bigger signal is emitted from the open end of the pcb antenna. If you hold the esp nodemcu with the usb connector towards you then the top right hand side of the board gives the strongest signal. You suggested sending from sheets the number of data items received. I do not know how to do that. If it is easier can you point me to a tutorial which explains the sheets script? Thanks for all your time and help.

  • How does the value ‘fingerprint’ get generated?

    • Hi Jeffrey, I searched quite a bit and bumped on to some of the sites however none of the generated fingerprints passed the test from ESP. Since it didn’t really stop me to post data to Google Sheets, I didn’t research on this topic in depth. If you find a solution, I will appreciate your time on sharing the finding. Thanks. Sorry, couldn’t help.

  • Hi
    After following ALL your instructions I got a single stream of data regularly posted to my sheet. Thanks. I am now trying to send more than one piece of data in a stream. I assumed I could send a tag and the data where the tag is the “title” at the head of the relevant column of the sheet. So far this does not work. Can you help or point me in the direction of a suitable tutorial. Any help would be appreciated.

    • Sorry about the delay hengis. If you post your data in JSON format from ESP then it will be a lot easier to parse in GAS. So construct your ESP data in JSON format like this: {“tag1″:”10”, “tag2″:”9”, “tag3″:”11”} then in GAS side you could something like this:

      var espData = JSON.parse(‘{“tag1″:”10”, “tag2″:”9”, “tag3″:”11”}’)
      then to get the tag1 value, espData.tag1.

      Hope this helps.

      • Thanks for your reply. I found a solution by manipulating the cells of google sheets. However I have another, interesting issue (I hope), when I try to use “pinmode output” and “digital write” within your exp8266 program I get no response. Can you fix of explain? I can control pins digitally in other programmes.

        • Can you post your code for digital write operation? It should be straightforward.

          • I am not sure what I was doing wrong but I now have the system up and running.. However an additional issue. It seems if my data stream is interrupted by say my dense head getting in the way of the transmission the whole data stream is stopped. I only seem to restart by resetting the esp source. Is there any wa to automatically resend the data if it is not received?

          • hengis – it’s always hard to troubleshoot when things work for the most past and at the same it doesn’t work. If I have to guess, there could be two things, 1) we might be sending too much of data (less likely as I have seen quite a heavy data been transmitted/received. 2) May be it is timing out.

            If there are data missing then send known number of data points and return a count from Google App Script. If the data count doesn’t match do the corrective action…

      • ANir, Using the HTTPSredirect is a pleasure. But I don’t understand the multiple data solution. I have to make 2 changes : One in the ino file and one in the script, right.

        In the ino : XXXXX {“sens1″:String(sens1value, “sens2″:String(sens2value, “sens3″:String(sens3value)}

        but what should be XXXXX ?

        • I missed some brackets :(
          XXXXX {“sens1″:String(sens1value), “sens2″:String(sens2value), “sens3″:String(sens3value)}

  • I have resent the serial monitor output without what I think is xtraneous information.
    “Connecting to script.google.com
    Connection Status: 1
    Certificate mis-match

    The requested URL /1.1 was not found on this server. That’s all we know.
    Does this help to show what I am doing wrong?

  • Guys, I was able to do the test post to my spreadsheet and it worked. But running the firmware side of things I get the following error, any thoughts ?

    Connection Status: 1
    Certificate mis-match

    Error 404 (Not Found)!!1

    this is followed by a lot more html code ending with:
    The requested URL /1.1 was not found on this server. That’s all we know.

    • Hi Joesgarage,

      Try to print the final URL that actually posts the data to GAS in serial and copy that to a browser where you are not signed in. My first suspicion is the final URLis not in the correct format such as it has some spaces. My second guess is towards how you published things on Google side, make sure it’s “Everyone even anonymous” or something similar.

      code (postData method):
      String urlFinal = url + “tag=” + tag + “&value=” + String(value);
      Serial.println(urlFinal); // < << Add this line and then check manually client.printRedir(urlFinal, host, googleRedirHost);

      • As if by magic i have got it working.

      • ANir,
        thank you for taking the time to respond. It was my fault I didn’t select “Everyone even anonymous”.
        Doh !
        It works – life is good. Nice job on the post BTW.

        -Joe

        • Google wants me to login to get access to the documents.
          The script works through a normal page, but not being incognito (and not logged in into my google account)
          I assume my device encounters the same problems.
          Even when the script is visible to everyone.

          Has someone an explaination and solution for this?

          • Hi Jo, when you say “visible to everyone” what do you mean? Are you saying you published you GAS script as web app like the screen below?
            Publish to GAS

            If you haven’t done like above then you need to do that. If you did publish as above then things don’t make sense to me as it doesn’t require you to be signed into your google account.

          • Found it, rather simple.
            I changed access already to “everyone”, but it has to be to “everyone even anonymous”.
            This option however only appears when I specify my account to execute the script
            Now it’s working as explained.

            Thanks for this nice example.

  • If I leave off the

    ?tag=test&value=-1

    at the end of my url, I get “undefined” for the Tag and Value cells.

    I would have expected the

    if (e == null){e={}; e.parameters = {tag:”test”,value:”-1″};}

    statement to set up the parameters for me.

    So then, if I set my data to

    ?tag=&value=

    I would expect e is forced to null, but the (e == null) test above still fails.

    Any thoughts on this?

    • if you took out ?tag=test&value=-1 and got ‘undefined’ then everything is working well. So, IMO you must be seeing right values when you put the full URL (with ?tag=test&value=-1). To force e==null, from GAS (Google App Script), Run > doGet. If you like you can also have a breakpoint after the e==null line and you can see the value while debugging within GAS. HTH

  • Thanks for this.

  • Pingback: ESP8266 sending data to Google spreadsheets - Electronics-Lab

Leave a Reply

Your email address will not be published. Required fields are marked *