Projects / Log sensor data to Google Sheets

Log sensor data to Google Sheets

Beginner1 hGuided buildReviewed

Build it step by step

0 of 7 done
  1. ESP32

    Make the sheet

    In Google Sheets create a new sheet with headers Time, Temperature and Humidity.

  2. ESP32

    Add the script

    Open Extensions, Apps Script, paste the script from the comments and set your secret word.

  3. ESP32

    Deploy

    Deploy as a web app, execute as you, access for anyone. Copy the web app URL.

  4. ESP32

    Edit the sketch

    Put the URL, the secret word and your Wi-Fi details in the code.

  5. ESP32DHT2223.0°C

    Wire the sensor

    DHT22 on GPIO 4 with a 10 kΩ pull-up.

  6. ESP32

    Upload and watch

    Open the serial monitor. A reply code of 200 means the row was added.

  7. ESP32

    Chart it

    Select the columns in the sheet and insert a chart.

In short

How do you build a log sensor data to Google Sheets?

An ESP32 posts readings to a Google Apps Script, which adds a row to a sheet you can chart and share. You need ESP32 DevKit V1, DHT22 Sensor, Resistor 10 kΩ, 5 V 2 A USB adapter, USB A to micro-USB cable (data) and Jumper wires male to male (40 pcs, 20 cm). It takes 7 steps and about 60 minutes.

Level
Beginner
Time
About 60 min
Parts
6

Parts list

Tick what you already own. It saves to My Garage, so “Build with what I have” knows.

Wiring

FromToWhy
DHT22 VCC and GNDESP32 3V3 and GNDSensor supply
DHT22 DATAESP32 GPIO 4, with 10 kΩ to 3V3Pull-up
ESP32 powerUSB 5 V adapterRuns on its own

Code

Arduino IDE with the ESP32 board package by Espressif, board "ESP32 Dev Module". Install "DHT sensor library" and "Adafruit Unified Sensor". Anyone who knows the web app URL can post to it, so the secret word in the script rejects anyone else. Do not publish your URL or secret. Apps Script has daily limits, so one post a minute is well within them.

Runs on a virtual ESP32 Dev Module in your browser, with sliders for the sensors. Happy with it? Verify and upload to your real board from the same page (Chrome or Edge on a computer).

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

const char *SSID = "your-wifi", *PASSWORD = "your-password";
const char *SCRIPT_URL = "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec";
const char *SECRET = "choose-a-long-random-word";      // must match the script
DHT dht(4, DHT22);

void setup() {
  Serial.begin(115200);
  dht.begin();
  WiFi.begin(SSID, PASSWORD);
  while (WiFi.status() != WL_CONNECTED) delay(500);
}

void loop() {
  float t = dht.readTemperature(), h = dht.readHumidity();
  if (!isnan(t) && !isnan(h)) {
    WiFiClientSecure client;
    client.setInsecure();                                // skips certificate checking: fine for a hobby logger, not for secrets
    HTTPClient http;
    http.begin(client, SCRIPT_URL);
    http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);    // Apps Script answers with a redirect
    http.addHeader("Content-Type", "application/json");
    String body = String("{\"key\":\"") + SECRET + "\",\"t\":" + String(t, 1) + ",\"h\":" + String(h, 0) + "}";
    int code = http.POST(body);
    Serial.println(code);
    http.end();
  }
  delay(60000);                                          // one post per minute
}

// ===== Google Apps Script (Extensions, Apps Script in your sheet; deploy as a web app,
// execute as you, access: anyone) =====
// function doPost(e) {
//   var d = JSON.parse(e.postData.contents);
//   if (d.key !== "choose-a-long-random-word") return ContentService.createTextOutput("no");
//   SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([new Date(), d.t, d.h]);
//   return ContentService.createTextOutput("ok");
// }

What the serial monitor should show

Open the serial monitor at the baud rate in the code. This is what our simulator prints for a virtual ESP32 Dev Module with its sensors at their default values. Your numbers will follow your real readings, but the format should match.

200

Test checklist

What you should see at each check. If a reading is far off, stop and use the troubleshooting list.

CheckExpected
Serial monitorShows 200 after each post
SheetA new row every minute
Wrong secretScript answers no and no row is added
Breathe on the sensorHumidity row jumps

If it does not work

Serial shows 302 or -1

The redirect was not followed or Wi-Fi dropped. Keep setFollowRedirects.

No rows

The web app access is not set to anyone, or the URL has an old deployment ID. Re-deploy.

NaN readings

Check the DHT22 pull-up and wiring.

Rows stop after a while

Wi-Fi dropped. Add a reconnect check in loop().

Still stuck? Find the fault step by step with your multimeter →

Ask Lark AI

Common questions

What parts do I need for the log sensor data to Google Sheets?

ESP32 DevKit V1, DHT22 Sensor, Resistor 10 kΩ, 5 V 2 A USB adapter, USB A to micro-USB cable (data) and Jumper wires male to male (40 pcs, 20 cm).

How long does the log sensor data to Google Sheets take to build?

About 60 minutes for a beginner maker, in 7 steps.

How do I know the log sensor data to Google Sheets works?

Serial monitor: Shows 200 after each post. Sheet: A new row every minute. Wrong secret: Script answers no and no row is added.

Serial shows 302 or -1: what should I check?

The redirect was not followed or Wi-Fi dropped. Keep setFollowRedirects.

No rows: what should I check?

The web app access is not set to anyone, or the URL has an old deployment ID. Re-deploy.

NaN readings: what should I check?

Check the DHT22 pull-up and wiring.

Rows stop after a while: what should I check?

Wi-Fi dropped. Add a reconnect check in loop().