Get to Know ESP32 #10: Data Logging to Google Sheets

7 min readApr 26, 2020

In this project, we’re going to learn how to save data from sensor readings — in this case, from DHT22 — to Google Sheets. Here’s a graph to understand this project easily.

First, the ESP32 connects to Wi-Fi network, and then the sensor (might be any sensor, but in our case, again we’re going to use DHT22) reads temperature and humidity from environment. ESP32 then communicates with IFTTT Webhooks service which publishes readings to a spreadsheet in Google Sheets inside your account. After the readings are published, the ESP32 will take a deep sleep for a certain amount of time, then wakes up and repeats the cycle.

Preparation

Hardware

Okay, as always, we’re going to need some tools in our hands. In this project, the tools required are as written below:

1 NodeMCU ESP32-S with 30 pins (or any ESP32 development board)
1 DHT22
1 Type-A USB to Micro-USB cable
1 Laptop with Arduino IDE configured and installed
1 Breadboard
3 Female-to-Male wire jumpers
2 Male-to-Male wire jumpers

We’re going to use the DHT22’s built-in resistor for this project. If yours doesn’t have one, be sure to prepare a resistor!

IFTTT Account

For this project, we’re going to need an IFTTT account! For your information, IFTTT stands for If This Then That :’) It’s actually a really cute name, don’t you think? Oh and don’t worry, the account creation process is free. Here’s the website.

Libraries

There are several libraries that are needed for this project:

Adafruit_Sensor library
DHT_Sensor library

Circuit

For my circuit, I’m using a simple DHT22 circuit based on the schematic diagram below.

And here’s the circuit I managed to build.

Set Up

Creating Applet

Moving on.. We’re going to need a new applet. To get it, go to ‘My Applets’, and create a ‘new Applet’ button. In my case, I didn’t see the button, so I just clicked on the ‘this’ word.

Use the search bar to find Webhooks service.

Next, you’ll need to choose a trigger. For this project, we’re going to pick the ‘Receive a web request’ trigger. This trigger fires when the Maker service receives a web request to notify it of an event.

When you choose that trigger, there’ll be a box which asks for an event name. Name it whatever you like :)

Okay, another cute part! The ‘this’ icon will be replaced and now the highlight is on the word ‘that’! Aaaw, I actually smiled looking at this.

We meet again with this ‘Choose action service’ command. Again, use the search bar to look for Google Sheets.

It’ll show a blue button which asks us to connect to allow specific cells monitoring as well as creation of new rows or cell updates. Press the button.

There’ll be two options, to ‘Add row to spreadsheet’ or to ‘Update cell in spreadsheet’. Since we’re going to input new data readings every amount of time, we’ll choose the add row option. The update cell option will be more suitable if we only need 1 data which keeps changing value but we only need the last value.

Then, we’ll have to complete the action fields, such as giving name to the spreadsheet and creating Drive folder path.

Click finish.

Yay! You’re done creating an applet. Here’s how it’ll look like if you’ve successed creating one and connecting it.

Testing Applet

Next, we’re going to test if our applet already works. Go to Webhooks and click on the ‘Documentation’ button.

A page will show up, consisting our key and a section called ‘To Trigger an Event’. Click on the Test It button. If it works, it should pop up a message ‘Event has been triggered’. Here’s how mine looked like, sorry it got cropped :’) At least, my applet had been proven to work successfully.

Code

This code will post new readings to Google Sheets every 3 seconds.

#ifdef ESP32
  #include <WiFi.h>
#else
  #include <ESP8266WiFi.h>
#endif
#include <Wire.h>
#include <DHT.h>#define DHTPIN 4     // Digital pin connected to the DHT sensor
#define DHTTYPE    DHT22     // DHT 22DHT dht(DHTPIN, DHTTYPE);DHT dht(DHTPIN, DHTTYPE); // The object representing your DHT22 sensor// Replace with your SSID and Password
const char* ssid = "<SSID>";
const char* password = "<Password>";// Replace with your unique IFTTT URL resource
const char* resource = "/trigger/<your_resource>";// How your resource variable should look like, but with your own API KEY (that API KEY below is just an example):
//const char* resource = "/trigger/<your_trigger>";// Maker Webhooks IFTTT
const char* server = "maker.ifttt.com";// Time to sleep
uint64_t uS_TO_S_FACTOR = 1000000;  // Conversion factor for micro seconds to seconds
// sleep for 3 seconds
uint64_t TIME_TO_SLEEP = 3;void setup() {
  Serial.begin(115200); 
  delay(2000);// initialize DHT Sensor
  dht.begin();
  
  initWifi();
  makeIFTTTRequest();
    
  #ifdef ESP32
    // enable timer deep sleep
    esp_sleep_enable_timer_wakeup(TIME_TO_SLEEP * uS_TO_S_FACTOR);    
    Serial.println("Going to sleep now");
    // start deep sleep for 3600 seconds (60 minutes)
    esp_deep_sleep_start();
  #else
    // Deep sleep mode for 3600 seconds (60 minutes)
    Serial.println("Going to sleep now");
    ESP.deepSleep(TIME_TO_SLEEP * uS_TO_S_FACTOR); 
  #endif
}void loop() {
  // sleeping so wont get here 
}// Establish a Wi-Fi connection with your router
void initWifi() {
  Serial.print("Connecting to: "); 
  Serial.print(ssid);
  WiFi.begin(ssid, password);
  int timeout = 10 * 4; // 10 seconds
  
  while(WiFi.status() != WL_CONNECTED  && (timeout-- > 0)) {
    delay(250);
    Serial.print(".");
  }
  
  Serial.println("");if(WiFi.status() != WL_CONNECTED) {
     Serial.println("Failed to connect, going back to sleep");
  }
  
  Serial.print("WiFi connected in: "); 
  Serial.print(millis());
  Serial.print(", IP address: "); 
  Serial.println(WiFi.localIP());
}// Make an HTTP request to the IFTTT web service
void makeIFTTTRequest() {
  Serial.print("Connecting to "); 
  Serial.print(server);
  
  WiFiClient client;
  int retries = 5;
  while(!!!client.connect(server, 80) && (retries-- > 0)) {
    Serial.print(".");
  }
  
  Serial.println();
  if(!!!client.connected()) {
    Serial.println("Failed to connect...");
  }
  
  Serial.print("Request resource: "); 
  Serial.println(resource);// Temperature in Celsius
  String jsonObject = String("{\"value1\":\"") + dht.readTemperature() + "\",\"value2\":\"" + dht.readHumidity() + "\"}";
                      
  // Comment the previous line and uncomment the next line to publish temperature readings in Fahrenheit                    
  /*String jsonObject = String("{\"value1\":\"") + (1.8 * bme.readTemperature() + 32) + "\",\"value2\":\"" 
                      + (bme.readPressure()/100.0F) + "\",\"value3\":\"" + bme.readHumidity() + "\"}";*/
                      
  client.println(String("POST ") + resource + " HTTP/1.1");
  client.println(String("Host: ") + server); 
  client.println("Connection: close\r\nContent-Type: application/json");
  client.print("Content-Length: ");
  client.println(jsonObject.length());
  client.println();
  client.println(jsonObject);
        
  int timeout = 5 * 10; // 5 seconds             
  while(!!!client.available() && (timeout-- > 0)){
    delay(100);
  }
  if(!!!client.available()) {
    Serial.println("No response...");
  }
  while(client.available()){
    Serial.write(client.read());
  }
  
  Serial.println("\nclosing connection");
  client.stop(); 
}

After you copy this code to your IDE, there are a few adjustments that you need to do.

First, Wi-Fi. You’re going to need SSID and password of the connection you’re currently using.

//Wi-Fi
char ssid[] = "<SSID>"; // Place your wifi SSID here
char password[] =  "<Password>"; // Place your wifi password here

Second, your unique IFTT URL resource.

// Replace with your unique IFTTT URL resource
const char* resource = "/trigger/<your_resource>";

Third, the sensor and pin you’re using. I’m using DHT22 as a sensor, and to connect to ESP32 I choose GPIO4 for this project.

#define DHTPIN 4      // digital of your ESP32 connected to DHT22
#define DHTTYPE DHT22 // exact model of temperature sensor DHT22 for the general library

Demonstration

As always, connect the circuit to the laptop by using Type-A USB to Micro-USB cable and upload the code. If it states ‘Done uploading’, open Serial Monitor with 115200 baud rate.