Google Sheets API

Feed offline sensor data from Google Sheets to your IoT platform

Replay offline sensor data stored in google sheets in real time to your cloud platform via network simulator.

Majority of the installations in IoT are currently brownfield, i.e. scenarios where a lot of equipment is already installed and need to be connected to the internet. Many of these existing older devices already collect some data, however, the data captured by sensors may be stored in offline log files such as CSV or excel. If you are an organization building an IoT solutions and already have a lot of such data available, you may want to replay the offline data in real time through our virtual sensors, therefore creating a pseudo-real IoT sensor environment.

In this tutorial, we will focus on replaying sensor data stored in google sheets via the network simulator. The purpose of this tutorial is to showcase the flexibility of the network simulator in consuming data from various online and offline sources as well as generating intelligent synthetic datasets for your testing. Let’s get started.

Step 1: Enable Google Sheet APIs

Let’s upload your existing sensor data to the google sheets. Then, we need to enable Google sheet APIs through which you could access the data. In order to do that, go to the google cloud console admin page Dashboard here. If you have not previously created a project, you may need to create one.

Once in the dashboard, click enable API and services button and select Google Sheets APIs. Simply enable the API via the enable button and then go to Credential settings.

Create a new API key which would now be used to access the google sheet APIs. Please note that it may take a couple of minutes before the API becomes available.

Once you have the API keys, next step is to enable link sharing of the Sheet so that IoTIFY template could read from it.

Step 2: Populate Google sheet and prepare for sharing

Let’s put all the data we have in the google sheet and enable sharing with View access. To share this, simply click on the Share button on the right hand side of the sheet and change the share settings to “Anyone who has the link can view” Remember, we are only going to read from the sheet at the moment.

Make sure you populate first row in the sheet as the header, i.e. containing the name of the columns.

Now extract the sheet ID from the URL as follows

https://docs.google.com/spreadsheets/d/[YOUR_SHEET_ID_HERE]/edit

The sheet ID is the alphanumeric string between d/ and edit keyword as shown above.

Now you have your sheet ID and API key ready, its time to create the network template in the Network Simulator.

Step 3. Prepare the template

Now you could prepare a network simulator template and fill the settings to connect with your IoT platform. Note that this process is independent of the connectivity protocol chosen and cloud provider. The content of the template below should be copied to the Message function in any template

Remeber to replace your API Keys above in the template as obtained from Step 1.

The template code will populate the current header rows and use them as JSON object keys. Afterwards, on every iteration, it will read a row and construct a JSON object from the values. This JSON object will be sent to your cloud platform as a string. You could offcourse modify these value and change anything which you need.

Once all the rows has been read, the code will reset the cursor to the beginning of the sheet. You could change this behavior as well by not incrementing the state.cur_row and keep it fixed to the last element.

Remember that sheet should contain header row which contains the name of the column value. Donot include any space in the header because they will be used as the key to JSON object.

Simulating multiple sensors from the same sheet

In order to simulate multiple sensors from the same sheet, you could introduce some variation in the read sensor values. E.g. while constructing the data payload, you could add a variance in the numbers.

Troubleshooting

Facing some issues? Make sure your sheet ID is correct and API key is working. To ensure that simply go to your browser and paste following text in the address URL https://sheets.googleapis.com/v4/spreadsheets/[SHEET_ID_HERE]/values/Sheet1!A0:D0?key=[API_KEY_HERE]

If everything is correct you will see following response in the browser

In case your APIs don’t work, you will see the error cause above.

Hope you found this guide interesting. We would welcome your feedback and comments in the discussion board below.