Sensor Data Upload to Google Sheets Through NodeMCU

This tutorial will walk you through uploading sensor data on Google Sheets through NodeMCU.

An Updated Video Walkthrough (2020):

You can get the Library files from here:https://github.com/NishantSahay7/Nodemcu-to-Google-Sheets

Step By Step written guide (2019):

Step 1: Set Up NodeMCU for Arduino IDE

Note: To set up Arduino IDE for NodeMCU, visit here.

Step 2: Setting Up Google Sheets

Step 2.1: Open Google Drive and Create new Spreadsheet and name it, after that give the fields with the paramters you want to define.

• Note 1.1: Sheet ID is:

We will be using this sheet id in the script code.

Step 2.2: Now go-to Tools-Script Editor:

Step 2.3: Now Give the name same as that of Spreadsheet:

Step 2.4: Now paste the code from here to the Script Editor Window:

Note 1.2: Replace the var sheet_id with your Spreadsheet ID from Note 1.1

Step 2.5: Go to Publish - Deploy as Web App:

Step 2.6: Now change the access type to anyone, even anonymous, and deploy:

Step 2.7: Go to Review Permissions:

Step 2.8: Choose Advanced:

Step 2.9: Choose Go to (file name) and then allow:

Step 2.10: Copy the current web app URL and click OK:

• Note 1.3: The URL copied will be something like: https://script.google.com/macros/s/AKfycbxZGcTwqeDgF3MBMGj6FJeYD7mcUcyo2V6O20D6tRlLlP2M_wQ/exec
• The above link is in the form of: https://script.google.com/macros/s/<gscript id>/execSo here gscript id is:

AKfycbxZGcTwqeDgF3MBMGj6FJeYD7mcUcyo2V6O20D6tRlLlP2M_wQ

Now we will using the above link to push the data to our Google Sheets:

Example: https://script.google.com/macros/s/AKfycbxZGcTwqeDgF3MBMGj6FJeYD7mcUcyo2V6O20D6tRlLlP2M_wQ/exec?A=1&B=2&C=3

• Paste the above link to the new window and hit enter:

• The you should see message like this. If this appears, then your cells are updated with the value you gave:

• Now that the Google Sheets part is done, we will move on to the code that has to be written in order to call the api created.

Step 3: Code

• Below is the Arduino Code, download the same from here.

#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>
String readString;
const char* ssid = "SSID";
const char* password = "Password";
const char* host = "script.google.com";
const int httpsPort = 443;
WiFiClientSecure client;
const char* fingerprint = "46 B2 C3 44 9C 59 09 8B 01 B6 F8 BD 4C FB 00 74 91 2F EF F6";
String GAS_ID = "Put your GAS ID here";  // Replace by your GAS service id
void setup()
{
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) 
  {
    delay(500);
    Serial.print(".");
  }       
    sendData(113,125);  // Send test data
}
void loop()
{
   int a,b;
   a=1;
   b=1;
   sendData(a,b);
}
void sendData(int x, int y)
{
 client.setInsecure();
 Serial.print("connecting to ");
 Serial.println(host);
 if (!client.connect(host, httpsPort)) {
   Serial.println("connection failed");
   return;
 }
 if (client.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 url = "/macros/s/" + GAS_ID + "/exec?Value1=" + string_x + "&Value2=" + string_y;
 Serial.print("requesting URL: ");
 Serial.println(url);
 client.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 (client.connected()) {
 String line = client.readStringUntil('\n');
 if (line == "\r") {
   Serial.println("headers received");
   break;
 }
 }
 String line = client.readStringUntil('\n');
 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("==========");
 Serial.println(line);
 Serial.println("==========");
 Serial.println("closing connection");
}

Did you like my work?

You can support my work by buying me a coffee over here.