Home / How To's / Log Data with NodeMCU and Google Sheets
nodemcu ajax dynamic sensor data

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!

 

 

 

Check Also

How to Use the MQ-7 Carbon Monoxide Sensor

The MQ-7 carbon monoxide sensor is especially designed to be sensitive to carbon monoxide (CO) …

7 comments

  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.

  2. Thanks for the tutorial. this tutorial is based on just one input field, like in mine i need to save date for both temperature and humidity and when i get to the entry ,it has different entry id for both fields. please how will i go about that

  3. BHAGYASHREE REDKAR

    if we want to read tag and information from rfid tags and send it to google sheets then what changes need to be made in the code?

  4. Fantastic!!! I started from scratch in midmorning and by evening (after going to the gym, shopping and dinner) I was logging data to my new Google spreadsheet... with no errors along the way. Thanks due to your detailed, accurate and complete instructions. I converted the .ino script to Nodemcu .lua to run on my 8266 Huzzah Feather. I still can't believe that I actually finished a project in one day. Thanks again.

  5. I am getting Bad DevID
    please help

Leave a Reply

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