Log temperature to Google Sheets

ESP32 IoT Starter Kit | Arduino IDE Compatible | Free Tutorials

$57.00

Optional

This lesson has free STL files. Print the parts yourself with one of these 3D printers.

Log temperature to Google Sheets – EEMENTOR STEM™ lesson

A spreadsheet is a great place for measurements: it stores thousands of rows, draws charts and works out averages for you. In this lesson the ESP32 sends the temperature and humidity to a Google Sheets spreadsheet every ten minutes. A short script that you add to the sheet receives each reading and writes it into a new row. After a day you can see when the heating comes on; after a week, how your room changes from day to day.

What you'll learn

How to receive data from a device with a Google Apps Script web app
How to send values in a web address (a query string)
Why a secret key protects your sheet
How to draw a chart from logged data

What you need

ESP32 board, expansion board and USB cable (ESP32 IoT Starter Kit)
DHT11 temperature and humidity sensor (ESP32 IoT Starter Kit)
A free Google account (ask a parent or teacher if you are under 13)
A 2.4 GHz Wi-Fi network
The printed parts from the 3D printing box below (radiation shield), or the cardboard version described there
Breadboard and jumper wires (ESP32 IoT Starter Kit)

3D printing · free STL files

Print a radiation shield

A thermometer in direct sunlight or next to a warm board reads too high. Weather stations hide their sensors in a white, louvred box called a radiation shield, which keeps the sun off but lets air through. This is a small one for the DHT11.

⬇ Radiation shield STL · 60 × 46 × 56 mm

Print settings and how to put it together

Print settings: White PLA, 0.2 mm layers, 3 walls, 15 % infill. Print it roof down, as it lies in the file. The louvres are angled so they print without supports.

Push the DHT11 module onto the peg under the roof, with the sensor facing down.
Lead its three jumper wires out of the open bottom.
Hang or stand the shield away from the ESP32, in the shade, where air can move around it.

No 3D printer? Put the sensor inside a white plastic cup turned upside down, with a few holes cut in the sides.

Wiring

From	To
DHT11 S (signal)	ESP32 GPIO 27
DHT11 +	ESP32 3V3
DHT11 −	GND

Prepare the spreadsheet

Go to sheets.google.com and create a blank spreadsheet. Call it Room log.
Rename the first tab (bottom left) to Readings. Type Time, Temperature and Humidity in cells A1, B1 and C1.
Open Extensions → Apps Script. Delete the code you see and paste the script below.
Change YOUR_SECRET_WORD to a word only you know, using letters and numbers only. Click the save icon.

const SECRET = 'YOUR_SECRET_WORD';

function doGet(e) {
  if (e.parameter.key !== SECRET) {
    return ContentService.createTextOutput('wrong key');
  }
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Readings');
  sheet.appendRow([new Date(), Number(e.parameter.t), Number(e.parameter.h)]);
  return ContentService.createTextOutput('OK');
}

Click Deploy → New deployment. Click the gear icon next to "Select type" and choose Web app.
Set Execute as to Me and Who has access to Anyone, then click Deploy.
Google asks you to authorise the script. Choose your account; if you see "Google hasn't verified this app", click Advanced and Go to … (unsafe). It is your own script, so this is expected.
Copy the Web app URL. It ends in /exec.
Test it: paste the URL into a browser and add ?key=YOUR_SECRET_WORD&t=21.5&h=40 to the end. The page shows OK and a new row appears in the sheet. Delete that test row.

The sketch

Replace YOUR_WIFI_NAME and YOUR_WIFI_PASSWORD with your own network details (2.4 GHz only).

Paste your web app URL into SCRIPT_URL and your secret word into SECRET. Install the DHT sensor library by Adafruit (and Adafruit Unified Sensor when asked).

#include <WiFi.h>
#include <WiFiClientSecure.h>
#include <HTTPClient.h>
#include <DHT.h>

const char* WIFI_NAME = "YOUR_WIFI_NAME";
const char* WIFI_PASSWORD = "YOUR_WIFI_PASSWORD";
const char* SCRIPT_URL = "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec";
const char* SECRET = "YOUR_SECRET_WORD";
const unsigned long EVERY = 10UL * 60 * 1000;   // one row every 10 minutes

DHT dht(27, DHT11);
unsigned long lastSend = 0;
bool sentOnce = false;

void sendReading() {
  float t = dht.readTemperature();
  float h = dht.readHumidity();
  if (isnan(t) || isnan(h)) {
    Serial.println("Sensor error, trying again next time");
    return;
  }
  String url = String(SCRIPT_URL) + "?key=" + SECRET + "&t=" + String(t, 1) + "&h=" + String(h, 0);

  WiFiClientSecure client;
  client.setInsecure();
  HTTPClient http;
  http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);   // Google answers with a redirect first
  http.begin(client, url);
  int code = http.GET();
  String answer = http.getString();
  http.end();
  Serial.printf("%.1f C, %.0f %% -> Google answered %d: %s\n", t, h, code, answer.c_str());
}

void setup() {
  Serial.begin(115200);
  dht.begin();
  WiFi.begin(WIFI_NAME, WIFI_PASSWORD);
  Serial.print("Connecting to Wi-Fi");
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println(" connected");
}

void loop() {
  if (!sentOnce || millis() - lastSend >= EVERY) {
    sentOnce = true;
    lastSend = millis();
    if (WiFi.status() == WL_CONNECTED) sendReading();
    else WiFi.reconnect();
  }
}

Draw a chart

Upload the sketch and open the Serial Monitor at 115200 baud. Within a few seconds you see "Google answered 200: OK", and a row appears in your sheet.
Let it run for a few hours. While testing, you can change EVERY to 60000 for a row every minute.
In the sheet, select columns A to C and choose Insert → Chart. Pick a Line chart. Google Sheets puts time along the bottom and draws one line for temperature and one for humidity.
Add a cell with =AVERAGE(B:B), =MAX(B:B) or =MIN(B:B) to see the average, warmest and coldest temperature.

How it works

When you deploy an Apps Script as a web app, Google gives it a web address. Every time something opens that address, Google runs your doGet() function, and anything after the ? arrives in e.parameter. The ESP32 builds an address like …/exec?key=apple42&t=21.5&h=40, and the script turns it into a new row with the time.

Because "Anyone" can open the web app, the secret key stops strangers from filling your sheet with fake rows: without the right key the script answers "wrong key" and writes nothing. Google first answers with a redirect to a second address, which is why the sketch tells HTTPClient to follow redirects.

client.setInsecure() keeps the connection encrypted but skips checking the server's certificate. That keeps the sketch short and is fine for a learning project. For a device you leave running for years, load the server's root certificate with client.setCACert() instead.

Try this next

Add a photoresistor and log the light level in a fourth column.
Use Format → Conditional formatting to colour rows red when it was warmer than 25 °C.
Put a second ESP32 in another room, add a room parameter and compare both rooms on one chart.

Troubleshooting

"Google answered 200: wrong key": the secret in the sketch and in the script must match exactly.
"Google answered 302" or "-1": check that the URL ends in /exec and that you deployed as a web app with access for Anyone.
You changed the script, but nothing changed: after every change choose Deploy → Manage deployments, edit the deployment and pick New version.
"Sensor error": check the DHT11 signal wire on GPIO 27.
It never connects to Wi-Fi: check the network name and password (both are case-sensitive) and make sure the network is 2.4 GHz.
A printed part is too tight or too loose: printers differ by a few tenths of a millimetre. Sand or file a tight hole, and fix a loose part with a drop of hot glue or a strip of tape.