Log and Visualize ESP32 Sensor Data in Google Sheets

Overview

Add a temperature and humidity sensor to ESP32 Wi-Fi Kit 2 to build an environmental sensor. Connect it to the internet to record and visualize readings in Google Sheets.

What to prepare

• ESP32 Wi-Fi Kit 2 (Japanese)
• One of the following sensors:
  • DHT22: temperature and humidity.¹
  • 4-Sensors: temperature, humidity and illuminance.
• Three AAA nickel-metal hydride batteries
• A development computer
• A Wi-Fi router

Demo video

Set up Google Apps Script

Use Google Apps Script to create the web app that receives readings from Leafony.

1. Open Google Sheets and create a spreadsheet.
2. In the spreadsheet, select Extensions → Apps Script and paste the code below. Replace spreadsheetId with your spreadsheet ID, as described in the Google Sheets concepts guide. Replace sheetName with the name of the sheet tab; use its actual name, such as Sheet1 or シート1.

function doGet(e) {

    let id = 'spreadsheetId'; //Insert spreadsheetId
    let sheetName = 'sheetName'; //Insert sheetName
    var result;

    // e.parameter has received GET parameters, i.e. temperature, humidity, illumination
    if (e.parameter == undefined) {
        result = 'Parameter undefined';
    } else {
        var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
        var newRow = sheet.getLastRow() + 1;  // get row number to be inserted
        var rowData = [];

        // get current time
        rowData[0] = new Date();
        rowData[1] = e.parameter.UniqueID;
        rowData[2] = e.parameter.temperature;
        rowData[3] = e.parameter.humidity;
        rowData[4] = e.parameter.illumination;

        // 1 x rowData.length cells from (newRow, 1) cell are specified
        var newRange = sheet.getRange(newRow, 1, 1, rowData.length);

        // insert data to the target cells
        newRange.setValues([rowData]);
        result =  'Ok';
    }

    return ContentService.createTextOutput(result);
}

Deploy Google Apps Script

Deploy the script to obtain the web app URL.²

1. Open Extensions → Apps Script from the spreadsheet.
2. Select Deploy → New deployment at the top right.
3. Under Select type, choose Web app.
4. Leave the description empty if desired, set Execute as to Me, and set Who has access to Anyone. Select Deploy.
5. Select Authorize access to grant access to your data.
6. When deployment finishes, copy both the Deployment ID and Web app URL. The sketch uses the deployment ID; the URL is used to test Google Apps Script.
7. Select Done.

Assemble Leafony

With DHT22

Take ESP32 Wi-Fi Kit 2 out of its box and connect DHT22 to the 29pin header as follows.

DHT22	29pin header
VCC-power supply	1
DAT-signal	15
GND	27

With 4-Sensors

Remove the RTC & microSD leaf from ESP32 Wi-Fi Kit 2 and install 4-Sensors.

Source code

Set up the development environment

Configure either Arduino IDE or PlatformIO IDE.

Download the source code

Download the project for your sensor and IDE to your development computer.

IDE	DHT22	4-Sensors
Arduino IDE	Arduino DHT22 source	Arduino 4-Sensors source
PlatformIO IDE	PlatformIO DHT22 source	PlatformIO 4-Sensors source

The DHT22 example uses the DHT sensor library.

Edit the source code

For a personal or home Wi-Fi router using WPA2 Personal,³ set:

• ssid: the router’s SSID.
• password: the router’s password.
• google_scripts_key: the Google Apps Script deployment ID.

For a university or company network using WPA2 Enterprise,⁴ set:

• Remove // from //#define ENTERPRISE.
• identity: your user ID.
• password: your user password.
• ssid: the router’s SSID.
• google_scripts_key: the Google Apps Script deployment ID.

Upload the source code

After editing, connect Leafony to your computer and upload the sketch.

Results

1. In the first row of the spreadsheet, enter the column headings Datetime, UniqueID, Temperature, Humidity and Illumination.⁵
2. Press the reset button on the ESP32 MCU leaf to run the program. Sensor readings are written to the spreadsheet.

Tips

Test Google Apps Script

1. Append ?UniqueID=Leafony_A&temperature=10&humidity=20&illumination=30 to your web app URL and open it in a browser.
2. Check that a row containing the date and time, Leafony_A, 10, 20 and 30 appears in the spreadsheet.

Edit Google Apps Script

1. Open Extensions → Apps Script from the spreadsheet.
2. Select Deploy → Manage deployments.
3. Select the edit icon, choose New version, and select Deploy.

Plot the spreadsheet in Grafana

Grafana can plot the readings stored in Google Sheets. Follow the Windows installation guide, start the server, and open http://localhost:3000/. For the first sign-in, use admin as both username and password, then change the password.

1. Install the Google Sheets data source plugin. Follow the official configuration guide to find Google Sheets under Connections and add the data source.

2. Choose authentication to match the spreadsheet’s sharing settings: API Key for a public sheet, or a service account with Google JWT File for a private sheet.⁶ Select Save & test to check the connection.

3. Add a visualization panel to a dashboard and select the Google Sheets data source. Follow the query editor guide to set:

   • Spreadsheet ID: the spreadsheet ID or URL.
   • Range: for example, Sheet1!A:E to include the columns from date/time through illuminance. Replace Sheet1 with your actual tab name.
   • Cache Time: the default is 5m (five minutes). Set 0s to disable caching.
   • Use Time Filter: enable this to filter the date/time column using the dashboard’s selected time range.

Menu labels may vary with the Grafana and plugin versions.

Footnotes

1. See the DHT22 documentation and example code (Japanese). ↩

2. Deployment makes software available for use. For Google Apps Script, it makes the web app executable and gives it a URL. ↩

3. WPA2 Personal is commonly used in homes and small offices, with a password configured on each device. ↩

4. WPA2 Enterprise is intended for larger networks, with an ID and password registered for each user. ↩

5. Illuminance readings are only available with 4-Sensors. ↩

6. API key authentication in Grafana requires a publicly shared spreadsheet. Use service account authentication for a private sheet. Enable the Google Sheets API in Google Cloud, configure credentials and grant access to the sheet as described in the official authentication guide. ↩