Log sensor data to Google Sheets
Build it step by step
0 of 7 doneMake the sheet
In Google Sheets create a new sheet with headers Time, Temperature and Humidity.
Add the script
Open Extensions, Apps Script, paste the script from the comments and set your secret word.
Deploy
Deploy as a web app, execute as you, access for anyone. Copy the web app URL.
Edit the sketch
Put the URL, the secret word and your Wi-Fi details in the code.
Wire the sensor
DHT22 on GPIO 4 with a 10 kΩ pull-up.
Upload and watch
Open the serial monitor. A reply code of 200 means the row was added.
Chart it
Select the columns in the sheet and insert a chart.
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
- Development BoardsBuyESP32 DevKit V1 × 1
- SensorsBuyDHT22 Sensor × 1
- PassivesBuyResistor 10 kΩ × 1
- PowerBuy
- Cables & WiresBuy
- PrototypingBuy
Tick what you already own. It saves to My Garage, so “Build with what I have” knows.
Wiring
| From | To | Why |
|---|---|---|
| DHT22 VCC and GND | ESP32 3V3 and GND | Sensor supply |
| DHT22 DATA | ESP32 GPIO 4, with 10 kΩ to 3V3 | Pull-up |
| ESP32 power | USB 5 V adapter | Runs 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.
| Check | Expected |
|---|---|
| Serial monitor | Shows 200 after each post |
| Sheet | A new row every minute |
| Wrong secret | Script answers no and no row is added |
| Breathe on the sensor | Humidity 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 →
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().
