Make Use of Google Spreadsheet as an IoT device database using Google App Script and ESP32

In this age of new and shiny open source data projects, something as old as a spreadsheet can seem boring. But sometimes boring is awesome, especially when it’s your database.

If you are a regular user of Google Drive, like me, you would find a Google sheet more approachable than all those IoT cloud platforms.

What Google Apps Script provides libraries and classes that allow you to work with objects such as spreadsheets, emails, calendars, slides, and more.

I’ve spent a lot of time over the last year working with small embedded systems doing IIoT applications. The Espressif Systems ESP8266 and the ESP32 modules have become my favourite boards for hacking out projects.

These are the items that are required for this project

1. Esp32 Dev module
2. DS18b20 Temperature sensor
3. 4.7k Ohm resistor
4. Breadboard and Jumper wires

Creating a Google Script in Google Sheet

Login to your google drive and create a new blank spreadsheet.

2. Rename the spreadsheet to any name you want.

3. Rename the sheet name from Sheet1 to any name. This name string is used while writing the Google script.

4. After renaming it’s time to create Google script.

5. Now go to ‘Tools’ and click on ‘Script editor’.

6. Rename the script name from “Untitled project” to any name you want like “Script_name”.

7. In the code section paste the code in spreadsheet.gs. Download the code using this link.

8. Paste the URL of the Google Sheets starting from https thru /edit.

9. Go to the spreadsheet tab and copy the URL.

10. Paste the URL as shown below.

11. Change the sheet name as you have given in the spreadsheet in ss.getSheetByName(“Sheet_name”);.

12. Save the file. Now we have successfully written the google script required to publish the data.

Publish the app script and get the Google script ID

1. Now go to ‘Publish’ and click on Deploy as a web app.

2. The “Project version” will be “New”. Select “your email id” in the “Execute the app as” field. Choose “Anyone, even anonymous” in the “Who has access to the app” field.

3. And then Click on “Deploy”.

4. You will have to give Google permission to deploy it as a web app. Just click on “Review Permissions”.

5. Then choose your Email ID here using which you have created spreadsheet.

6. Then click on ‘Advanced’.

7. Then click on ‘Go to your ‘Script_name’(unsafe).

8. Now click on ‘Allow’ this will permit to deploy the app.

9. Copy the ‘Current web app URL’ and paste it somewhere this link will be used while configuring the esp32.

10. Now we have published the google script successfully.

Programming ESP32 to send the sensor data

Posted below is the ESP32 code written using Arduino IDE for reading the Temperature sensor data and posting it to a Google Sheet on your Google Drive. In order to make it work for your case, you will need to update the SSID and password matching to your WiFi network. Enter the Google server credentials such as host address, Google script ID and port number. The host and port number will be same as attached code but you need to change the Google Scripts ID that we got from the above steps (from Current web app URL).

In this code, I have given a delay of 10 seconds, The sensor data are posted to the Google sheet every 10 seconds. You can change the delay time based on your requirement.

Schematic Esp32 and DS18b20 temperature sensor

Follow the schematic as shown below

Demonstration

After uploading the Arduino code to ESP32, You should see the temperature data in the spreadsheet as shown below.

Download the all related codes in my git using this link.

However, if you follow the documentation carefully, you can’t go wrong.

Thanks for reading!