Google Sheets with ESP32

Overview

Using only an ESP32 MCU leaf and 4-Sensors leaf, you can build an IoT environmental sensor and visualize the sensor data using Google Spreadsheet.

Preparing Google Sheets

Introducing an IoT service that displays sensor data sent by the ESP32 MCU Leaf in Google Sheets. You can check the data anywhere, if you have a PC or smartphone with browser access.
First, let’s configure the settings for displaying data in Google Sheets as follows.

1. Sign in to your Google account and create a new spreadsheet. If you do not have an account, please create one.

2. You can change the title of Google Sheets (default: Untitled Spreadsheet) as you like.
   You can also change the sheet number (default: sheet 1) as you like also.

3. The symbol sequence in the red frame in the Google Sheets URL section below will be used later to specify the Google Sheet, so please copy it to an appropriate location.

   • Here, 1o0cogc3aomAj58CRdMZARNJtoEmf8dB9ebY02jKV9tA.

4. Open the Script Editor from Tools and copy and paste the source code from Github.

   • Leafony/Sample-Sketches/ESP32_GoogleSheets/google_scripts_code.js - GitHub

5. Enter the information of the Google Sheets that you just created in the following variables.
   • google_sheets_id：The symbol string of the URL of Google Sheets that you just copied.
   • sheet_name：Name of the sheet to write to (default: Sheet 1)

6. This section describes the procedure for publishing Google Sheets.
   Click Publish->Deploy as a web application.

7. Then, change the access permissions to Anyone, even anonymous, so that anyone can view it.

8. Click the Deploy button to confirm the access permission from Script App to Google Sheets. Click Confirm Permissions.

9. Click on Details at the bottom left.

10. Next you will get a warning, click on Go to (insecure page).

11. Finally, allow the script to request access to the spreadsheet.

12. The following pop-up window will appear, and a URL specific to the web app you have created will be issued, so copy the URL and click OK.

（App-specific URLs ）

https://script.google.com/macros/s/AKfycbw-8Wx8Zf0alULuD3aLQdwnyVM8hSHAiDsrj49wA17ZqE7y-tP8/exec

Checking the operation of Google Sheets

1. This time, we will try to write Datetime, UniqueID=Leafony_AP02-1, Temperature=100, Humidity=100, Illumination=100 in Google Sheets.
   
   

2. If you add ?UniqueID=Leafony_AP02-1&temperature=100&humidity=100&illumination=100 to the app-specific URL, you will get the following

https://script.google.com/macros/s/AKfycbw-8Wx8Zf0alULuD3aLQdwnyVM8hSHAiDsrj49wA17ZqE7y-tP8/exec?UniqueID=Leafony_AP02-1&temperature=100&humidity=100&illumination=100

3. Paste the above URL into your browser and access it, and the data will be written to the configured Google Sheets.

The column names listed in the first line of Google Sheets are not automatically displayed, so please provide them in advance.

If there is a mistake in the script

1. Modify the script.
2. Click Publish->`Deploy as a web application``.
3. If you deploy with the exact same settings as the first time, the modified code may not be reflected properly, so when you deploy again, set Project version to New and click Update.
4. The URL of the web app running at that time can be found below.

Next