Log data from an ESP8266 device directly to Google Sheets without a third party service. Log sensor data, send data by pressing a button, and receive data from a Google spreadsheet. (NodeMCU, Wemos D1 mini, Adafruit Feather HUZZAH, etc)
Below are step by step instructions to begin logging data using an ESP8266 module without the need for a third party service. This will publish random number values, so no additional hardware or sensors are required for the script to run.
Create new Google Sheets document, and give it a name.
Type the following text into each of the following cells:
Date
Time
value0
value1
value2
Get the Spreadsheet ID from the URL.
For example, if the URL is:
https://docs.google.com/spreadsheets/d/1sqp9hIM5VvDGEf8i9H-W1Z72lm0O5-ZxC16sMMS-cgo/edit#gid=0
Then the Spreadsheet ID is:
1sqp9hIM5VvDGEf8i9H-W1Z72lm0O5-ZxC16sMMS-cgo
From the Google Sheets menu, go to Extensions > Apps Script
Delete all of the default text in the script editor, and paste the GoogleScripts-example.gs code.
Update the Spreadsheet ID (line 9) with the ID obtained in step 3, and click Save
.
Note: The Spreadsheet ID must be contained in single quotation marks as shown in the example code, and the script must be saved before continuing to the next step.
Click the blue Deploy
button at the top right of the page, and select New Deployment
.
Click the gear
icon next to Select Type, and select Web App
and modify the following:
Me
Anyone
(note: do not select Anyone with a Google Account
- you must scroll down to the bottom to find Anyone
)
Click Deploy
Click Authorize access
then select your Google account.
On the "Google hasn't verified this app" screen, select Advanced
> Go to Untitled project (unsafe)
> Allow
Copy and save the Deployment ID
for use in the ESP8266 code, and click Done
.
From the script editor, click Save
and then Run
.
Note: Nothing will happen when you click run but you must do that once before continuing.
In the Arduino IDE, paste the "ESP8266-example.cpp" code into a blank sketch. Overwrite any existing code that was there.
Update the following info:
Note: The Deployment ID must be contained in quotation marks as shown in the example code. The Deployment ID can also be found by clicking Deploy > Manage Deployments
.
Install the HTTPSRedirect library from here:
https://github.com/electronicsguy/HTTPSRedirect
Click on the green "code" button and "Download ZIP"
Unzip the file and move the HTTPSRedirect folder to your library directory
Note: For Arduino IDE 2.0 and above, the library directory is a subfolder named "libraries" at the path shown under File > Preferences > Sketchbook location. Create the "libraries" folder if it does not already exist (do not capitalize the L in the "libraries" folder name)
Delete the config.cpp file from the HTTPSRedirect folder.
Note: It must be deleted, not just renamed as it may cause errors when compiling the sketch.
Upload code to your ESP8266 module and watch data get published to your sheet!
When making changes to the Google Scripts code, you will need to click Save
then Deploy > New deployment
for any new changes to take effect. You will be given a new Deployment ID that you will have to update in the ESP8266 code each time (each new deployment is given a new Deployment ID).