Home » NodeMCU » Log Data with NodeMCU and Google Sheets

Log Data with NodeMCU and Google Sheets

Most of the time, the data from a sensor in an IoT application needs to be recorded. This data logging is important for later statistical analysis now most popularly known as data analytics. In this tutorial, we will record the data captured from the sensor to a spreadsheet. Let's build a NodeMCU Google Sheets data logger!

We will be using the same circuit we used in the last tutorial. The new things we need for this tutorial are

Pushingbox is an API that will help capture data from the NodeMCU to the Google Sheet. It is necessary because Google does not allow direct logging via HTTP ever since its move to HTTPS.

Step 1: Create a Blank Spreadsheet

Log-in to your Google Drive account. Create a blank spreadsheet and name it.

Step 2: Create a Form

Next, create a new form. Place a "short answer text" as an input field.  Then on the upper right corner, click the preview button. This will open a new window.

node mcu google sheet create form

Get the URL of the newly opened window. For example, mine's: https://docs.google.com/forms/d/e/1FAIpQLScKRqbOYnFFCG9vyyWqPQiwuviTqiUrmvI5BfNZcwGq2A2OSA/viewform. Then right-click on the input field you just created and choose "Inspect". On the "Elements" tab, you will acquire the name of the input field. Mine's entry.495151502 as pictured. We need this for later.

nodemcu google sheet inspect form

Step 3: Link the Form to the Spreadsheet

Go back to the previous window (where you edit the form) and click the "Responses" tab. You'll see the Google Sheet icon just above the "Accepting Responses" switch.  Click it and a window will pop out. Choose "Select existing spreadsheet" and click "Select". Choose the spreadsheet you created in Step 1.

nodemcu google sheet connect to form

Step 4: Configure Pushingbox

Now go to pushingbox.com and create an account using the same email you used for Google drive. Click the "My Services" tab and click the "Add a service" button. Select "CustomURL". Fill in the form that popped out. Name the service  then on the Root URL field, paste the Google Form URL BUT replace "viewform" with "formResponse". Example, using the url above, that would be https://docs.google.com/forms/d/e/1FAIpQLScKRqbOYnFFCG9vyyWqPQiwuviTqiUrmvI5BfNZcwGq2A2OSA/formResponse. Leave the Method field as GET.

nodemcu google sheet pushingbox 1

Next, go to "My Scenarios". On the text field, give a name for the scenario and click "Add". Then click "Add an Action". Click the "Add an action with this service" of the service you just created. On the field on the window that pops out, type in the name of the input field in the form plus the "=$data$". You will be given a device ID after that.

nodemcu google sheet scenario

Step 5: Write the NodeMCU Code

After getting the device ID, its time to write a code for the NodeMCU. Here's mine:

Don't forget to replace with your WiFi's SSID and password. Also, provide your device ID on line 58.

The data will be logged to the spreadsheet everytime pin D2 is grounded. If you decide to remove this control then the code will continuously send data to the spreadsheet. Note that pushingbox allows only 1000 requests per day.

If you have any questions regarding this tutorial, kindly drop a comment below and I'll respond right away. Try it now!

 

 

 

One comment

  1. Took a couple days to get this to work. Problem was the ? before entry.12345564. I believe if you could have made your pics larger or the ability to blow them up it would have helped a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *