Guide no. 62
Every ten minutes the ESP32 wakes up, reads the BME280, adds a row to a Google sheet with an Apps Script and goes back to sleep.
By Ada Intelisano · informaticait.it technical team
- An Apps Script deployed as a web app writes the rows into the sheet.
- The board calls the /exec address with the values and follows Google's redirect.
- Deep sleep: on waking it starts again from setup(); RTC_DATA_ATTR keeps the counters.
- The /exec address is a key: do not publish it.
Every ten minutes the board wakes up, reads temperature, humidity and pressure, adds a row to a Google sheet and goes back to sleep. From your phone you see the history and make charts with the sheet's own tools. It is free, and the board sleeps almost all the time.
1How it works
2Prepare the sheet and the script
- Create a new Google sheet; in the first row write Date, Temperature, Humidity, Pressure.
- From the Extensions → Apps Script menu open the editor, delete what is there and paste the code below. Save.
// Google Apps Script: receives the readings from the board and appends them as a row to the sheet. // Paste it in Extensions > Apps Script of the Google sheet, then deploy it as a web app. function doGet(e) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; const t = e.parameter.t; // temperature const u = e.parameter.u; // humidity const p = e.parameter.p; // pressure sheet.appendRow([new Date(), t, u, p]); // date and time, then the three values return ContentService.createTextOutput('ok'); }
3Deploy it as a web app
- Top right: Deploy → New deployment, type Web app.
- Execute as: Me; access: open to anyone with the link, so the board can call it without logging in.
- Authorize access to your sheet when Google asks, then copy the address ending in
/exec.
Test it right away from the browser: paste the address and add ?t=20&u=50&p=1013. It must answer “ok” and a row appears in the sheet.
- The /exec address is the key to your sheet: whoever knows it can write to it. Do not publish it.
4The circuit and the sketch
The sensor is connected as in guide no. 56. In the sketch, write your network name, password and the /exec address.
// Every 10 minutes the board wakes up, reads the BME280, sends the values to a Google sheet and goes back to sleep. #include <WiFi.h> #include <WiFiClientSecure.h> #include <HTTPClient.h> #include <Adafruit_BME280.h> const char* NETWORK = "your-network-name"; const char* PASSWORD = "your-network-password"; const String SCRIPT_URL = "https://script.google.com/macros/s/YOUR-ID/exec"; // from the web app (step 3) const uint64_t MINUTES = 10; // how often to measure RTC_DATA_ATTR int wakeups = 0; // stays in memory even during deep sleep Adafruit_BME280 bme; void sleepNow() { Serial.printf("Going to sleep for %llu minutes\n", MINUTES); Serial.flush(); esp_sleep_enable_timer_wakeup(MINUTES * 60ULL * 1000000ULL); // the time is in microseconds esp_deep_sleep_start(); // there is no coming back from here: on waking, setup() starts again } void setup() { Serial.begin(115200); wakeups++; Serial.printf("Wake-up no. %d\n", wakeups); if (!bme.begin(0x77)) { Serial.println("BME280 not found"); sleepNow(); } float t = bme.readTemperature(); float u = bme.readHumidity(); float p = bme.readPressure() / 100.0; WiFi.mode(WIFI_STA); WiFi.begin(NETWORK, PASSWORD); unsigned long start = millis(); while (WiFi.status() != WL_CONNECTED && millis() - start < 15000) delay(250); if (WiFi.status() != WL_CONNECTED) { // no network: try again at the next wake-up Serial.println("Wi-Fi not available"); sleepNow(); } WiFiClientSecure client; client.setInsecure(); // no certificate check: see the guide HTTPClient http; http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS); // Google answers with a redirect to follow String url = SCRIPT_URL + "?t=" + String(t, 1) + "&u=" + String(u, 0) + "&p=" + String(p, 0); http.begin(client, url); int code = http.GET(); Serial.printf("Response: %d %s\n", code, http.getString().c_str()); http.end(); sleepNow(); } void loop() {} // never reached: after setup() the board sleeps
setup(): that is why loop() is empty. Variables marked RTC_DATA_ATTR survive the sleep: wakeups counts the measurements.setFollowRedirects tells it to follow it.5Try it
- Response -1 or nothing: the network does not reach that far, or the /exec address is wrong.
- Response 302 or an HTML page instead of “ok”:
setFollowRedirectsis missing, or the deployment is not open to anyone with the link. - You change the script and nothing changes: every change must be deployed again (Manage deployments → new version).
6Two things to know
- Security: with
setInsecure()the board does not check Google's certificate. For home readings this is an acceptable compromise; for important data you load the root certificate withsetCACert(). - Power consumption: for the ESP32 chip alone Espressif states 10 µA in deep sleep, but the whole board draws more and DFRobot gives inconsistent figures: on batteries, measure it (guide no. 24).
7Challenge yourself
- Add a column with
wakeups, so you notice if a measurement gets lost. - Create a temperature chart in the sheet: it updates by itself with every row.
- an Apps Script deployed as a web app receives the data and writes it into the sheet;
- the board sends an HTTPS request and follows Google's redirect;
- with deep sleep the board sleeps between measurements and starts again from
setup().
Useful products
Other guides
- Resistor colour code and tolerances
- N-channel and P-channel MOSFETs
- Bipolar transistor (BJT) or MOSFET?
- Relays: how to drive them and how much current they draw
- THT and SMD/SMT components
- XIAO board pinouts
- LEDs: forward voltage and series resistor
- The multimeter: measuring voltage and current
- Wemos D1 mini: first steps
- MOSFET module with Arduino and ESP32
- Relay module with Arduino and ESP32
- 3D printing filaments: PLA, PETG and fibre-filled versions
- Analog and digital: the differences
- ADS1115: 16-bit converter with Arduino and ESP32
- From bits to real values: converting sensor readings
- DS3231 RTC clock with Arduino and ESP32
- Linear or switching regulator: which one to choose
- Step-down, step-up and buck-boost: choosing the right module
- Voltage divider: reading voltages higher than 5 V or 3.3 V
- Pull-up, pull-down and bounce: wiring a button properly
- 3.3 V and 5 V logic levels: getting an ESP32 and an Arduino to talk
- The I²C bus: addresses, pull-ups and the scanner
- Lithium batteries: voltages, charging and protection
- ESP32 power consumption and deep sleep: how long the battery lasts
- Temperature sensors compared
- ESP32: which pins to use and which to avoid
- Getting started with the ESP32 in Arduino IDE 2 and PlatformIO
- SSD1306 OLED display and 16×2 I²C LCD with Arduino and ESP32
- PIR motion sensor and HC-SR04 ultrasonic sensor
- Date and time from the Internet with the ESP32: NTP, Italian time zone and daylight saving time
- MicroSD data logger: saving measurements to CSV
- Solar-powered ESP32: panel, charger and the numbers for Italy
- Web server on the ESP32: control a relay from your phone
- Your first LED: wire it up and make it blink
- The button: let Arduino decide
- The potentiometer: turn a knob and read its value
- Soft light: adjusting the brightness of an LED
- The buzzer: playing notes and melodies
- The traffic light: three LEDs in sequence
- The thermometer: reading the temperature with the TMP36
- The servo motor: moving an arm with Arduino
- The night light: the photoresistor and your first decision
- The relay: switching on a light bulb with Arduino
- The LCD display: making Arduino write
- The electronic dice: your first complete project
- The RGB LED: every color from three lights
- The joystick: two-axis control with a shield
- The LED ring: twelve colors from a single pin
- The DC motor: driving it with a MOSFET
- Soil moisture: the plant that asks for water
- The stepper motor: turn exactly as far as you want
- The parking sensor: faster and faster beeps
- The 7-segment display: big, bright numbers
- Automatic watering: the plant that waters itself
- The keypad: a lock with a secret code
- Networked weather station: values and chart on your phone
- A Telegram bot on the ESP32: your home from your phone
- MQTT with the ESP32: the language of connected devices
- Updating the ESP32 over Wi-Fi (OTA)
- ESP-NOW: two ESP32 boards talking without a router
- WebSocket with the ESP32: the LED that follows the slider
- WiFiManager: choose the Wi-Fi from your phone
- Bluetooth LE with the ESP32: the phone without a network
- The camera in your browser with the ESP32-S3
- A portable Bluetooth speaker you build yourself
- Bluetooth desktop speakers, 2 × 20 W
- Stereo MP3 player with two buttons
- The shelf that plays music: a Visaton exciter
- How to solder well
- How much does it draw? Current and power with the INA219
- Load-cell scale with the HX711
- CO2 traffic light: when to open the windows
- RFID card door opener
- GPS: track logger on microSD
- LoRa: how far does it go? A range test with two boards
- Battery weather sign with an e-paper display
- Web radio with an ESP32 and an I2S amplifier
- Getting started with the Raspberry Pi Pico 2 in MicroPython
- Your first amplifier: the LM386 and a speaker
- Talking Arduino: the DFR0760 speech synthesis module
- The spectrum of your music on an LED matrix
- Drawing with 64 LEDs: the 8×8 matrix with the MAX7219
- A real voltage: the 12-bit MCP4725 DAC
- The heartbeat on Arduino: an ECG with the AD8232