[Tutorial] Collecting IoT Temperature Sensor Data in Google Spreadsheets Tutorial

First of all, you need a system that can take sensor information and translate it into data to be processed. There are multiple options for that, but in this tutorial, we will look at a simple and cost-effective solution called Gravio.

If you don’t have Gravio yet, you get it in three easy steps:

1. download your Gravio Studio from the MacOS App Store/iOS Store or Windows Store,
2. create an account, sign up for the basic account and
3. order your sensors from the sensor order portal.

In this tutorial, we learn how temperature sensor data is being recorded as time series records into a Google Sheet spreadsheet.

What do we need?

We need:

• Gravio HubKit 3.8 or newer installed on a computer
• Gravio Studio 3.8 or newer (downloaded from the macOS App Store/iOS Store or Windows Store)
• Some sensors and the Gravio USB Dongle that you will receive when you sign up to Gravio in the above app.
• A Google account with access to Google Sheets
• A web browser with which you can inspect the source code (we use Chrome in this example)

If you have all the above, we’re ready to go. If you don’t have any sensors, you may also use Gravio and a Software Sensor such as a network/USB camera and a machine learning visual recognition model. I will cover this in other chapters.

Step 1: Create a Google Form

The first step is to log in with your Google account and create a Google Form. Ensure the access right to this form/results sheed is public (i.e. editeable with link). Give it a meaningful name such as “Temperature Hallway” and create a new entry. In this case we called it Temperature and used the “Short Answer” as an input method:

Step 2: Retrieve Link

Now, click on the “Send” button on the top right and select the “Link” icon tab:

Copy the link into a text editor and identify the ID of the form, which is the part between /e/ and /viewform/ marked as xxxxxxx below:

https://docs.google.com/forms/d/e/xxxxxxxxxxxx/viewform?usp=sf_link

Open the URL in a browser and it should look similar to this:

Step 3: Finding the Question ID

Now we need to find the question ID. This requires a bit more skills because you need to inspect the source code of the form. The easiest way to do this is to use the Chrome browser.

Open the above URL in Chrome:

The question ID has the format entry.xxxxxxxxxx within the form. You can find it in the source code using the DevTools. Press F12 (PC) or press the key combination Option+Command+i on Mac to open the “Inspector”. You will get a view, similar to this, with either the code on the side, below or in a separate window (depending on your settings):

Now:

1. Click on the “Elements” tab to show the code
2. Click on the Pointer button to turn the mouse into a selector
3. Select the answer field of the form
4. search for the name=”entry.xxxxx” part

Note: Never versions of Google Forms contain the entry ID in a JSON object at the beginning of the form:

Now you have:

1. the Google Form ID
2. the Question ID

Remember those carefully.

Note: if you change the Google Form, either of those IDs may change, so be careful when doing so.

Step 4: Making an HTTP Request in Gravio

The next step is to take the sensor information from Gravio and HTTP Post it to the Google Form.

For this, create a Gravio Action. For details on how to use Gravio, you can consider the Gravio Documentation.

Gravio is available for Windows and macOS. In this tutorial, we use the macOS version.

Create a SendHTTPRequest Action and fill in the following fields:

URL:  https://docs.google.com/forms/d/e/<Form ID>/formResponse

HTTP Method:   POST

Content-Type:  application/x-www-form-urlencoded

Pre-Mappings:          cv.Payload = {"entry.<Question ID>": av.Data}

The +av.Data in the Payload adds the av (action variable) data (i.e. the temperature sensor data) to the string (concatenated via the + sign)

Once filled in, you can try the action by pressing the “Play” button at the top right:

It will submit any sensor data to the form, as the action has not been triggered by a sensor, but at least you can see if the data gets through. The bottom of the screen shows debugging information.

Thereafter you can open your form in edit mode again and switch to the “Responses” view:

You should see a “0” entry in the responses view or table:

When you now click on the “Create Spreadsheet” button, you will see a prompt to name the spreadsheet or add it to an existing spreadsheet. Select to create a new one:

Open the spreadsheet in Google Sheets and you will see your Time Series:

Step 5: Using the Temperature Data to trigger the Action

In Gravio Studio, you can now hook the Sensor to the Trigger that triggers the Action and the sensor data should come through automatically.

You can compare the “Data” tab in Gravio with the Google Spreadsheet. Data should come through in near real-time:

‍

Needless to say, you can make nice graphs from the data gathered:

If you have any ideas or suggestions, don’t hesitate to get in touch. We have a friendly Gravio slack channel, that you can join for free!

We're looking forward to see what ideas you can come up with!