Google Sheets with ESP32
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
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.
- Sign in to your Google account and create a new spreadsheet. If you do not have an account, please create one.
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.
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.
Open the Script Editor from Tools and copy and paste the source code from Github.
- Enter the information of the
Google Sheetsthat you just created in the following variables.
google_sheets_id：The symbol string of the URL of
Google Sheetsthat you just copied.
sheet_name：Name of the sheet to write to (default:
- This section describes the procedure for publishing
Deploy as a web application.
- Then, change the access permissions to
Anyone, even anonymous, so that anyone can view it.
- Click the
Deploybutton to confirm the access permission from
Google Sheets. Click
- Click on
Detailsat the bottom left.
- Next you will get a warning, click on
Go to (insecure page).
allowthe script to request access to the spreadsheet.
- 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
（App-specific URLs ）
Checking the operation of Google Sheets
This time, we will try to write Datetime, UniqueID=Leafony_AP02-1, Temperature=100, Humidity=100, Illumination=100 in
If you add ?UniqueID=Leafony_AP02-1&temperature=100&humidity=100&illumination=100 to the app-specific URL, you will get the following
- Paste the above URL into your browser and access it, and the data will be written to the configured
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
- Modify the script.
Publish->`Deploy as a web application``.
- 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
- The URL of the web app running at that time can be found below.