Home / Tutorials / ESP8266 Tutorial / 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
click to view larger image

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
click to view larger image

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
click to view larger image

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
click to view larger image

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
click to view larger image

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

nodemcu ajax dynamic sensor data

NodeMCU Ajax: Dynamic Sensor Data on Web Page

Last time we managed to display sensor data to a NodeMCU web server. The problem …

15 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

    • Hello

      You can add as many input fields as you like and just use multiple variables on the URL of your pushingbox service.

    • You just need to make sure that whatever goes between the $ signs when you add the action in pushingbox, is the same field name you use when you construct the url in the sketch.

      So my pushingbox scenario contains:

      ?entry.2079504522=$data$ and
      ?entry.1627195063=$data1$

      Then in my Arduino sketch I have:

      url += "&data=" + String(reading);
      url += "&data1=" + String(reading1);

  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?

    • You need to merge your RFID sketch with this one, or vice versa, or put them both in a new sketch but with only one setup() and one loop(), then in the url use whatever variable name you used to capture the RFID, say it was myRFID, like this:

      url += "&data=" + String(myRFID);

      What I did with my sketch was use a couple of functions in loop(), that way I separated the reading of the pins into the variables from sending those off to pushingbox.

      So my loop() looks simply like this:

      void loop()
      {

      if (digitalRead(sendButton) == 0)
      {
      getReadings();
      updateGoogle();
      }
      }//loop

      updateGoogle() contains all the code from the original loop() here in the blog, except the part where it reads the analog pin

      getReadings() contains the analogRead (and other reads, since I have more than one sensor). It could just as easily hold the code to read the rfid tag.

      If you keep things apart like that, ie read the values and update the cloud, it makes it muuuuch simpler to get your mind round things. You can sort out the Google update stuff while just having a placeholder variable in the read function, without having to try to get two things working at once. Once the update works, put the "real" code into the other function.

  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

  6. Which microcontroller should be used to send data to google drive? I also need the link in which that microcontroller is used. I will be so thankful to you.

  7. Well _that_ was easy: worked out of the box. (Although I'm glad I had read the comments first and knew about that ? )

    One trivial change I made was to pinMode the button as INPUT_PULLUP, else with it floating it might have gone low on its own and used some of the 1000 attempts.

    Nice series of tutorials, thanks.

  8. I deleted the contents of the spreadsheet, pretending I had done my analysis and could discard them. I had hoped that subsequent updates would start from line 2 again, but alas they go into the spreadsheet at the line below where it previously was. It obviously needs to keep track of how fa down it had gone before, but do you know if there's a way to reset that and have it start at the top again?

Leave a Reply

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