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 solution 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, it's 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

{ 
    
  var sheet_id = "1Gv_GkPp3S8qTq1G4htCQaJTZl8Q9a9os5mur46Oed30";
  var key = "YOUR_API_KEY_HERE";
  var page_name = "Sheet1";
  
  // We are currently playing data from Col A to D. You could change it. 
  var col_begin = "A";
  var col_end =   "D";

  var prepareDataUrl = function(row) {
      var url = "https://sheets.googleapis.com/v4/spreadsheets/";
      url += sheet_id;
      url += "/values/"+page_name+ "!"+ col_begin+row+ ":"+ col_end+row;
      url += "?key="+key;       
      return encodeURI(url);
   }

  // populates the information about the sheet e.g. rows and columns.
  var getRowColumns = function() {
      var url = "https://sheets.googleapis.com/v4/spreadsheets/";
      url += sheet_id;
      url += "/?fields=sheets.properties";
      url += "&key="+key;       
      var prop = JSON.parse(get({url: url}));
      state.rows = prop.sheets[0].properties.gridProperties.rowCount;
      state.cols = prop.sheets[0].properties.gridProperties.columnCount;
   }

  // beginning of the simulation, retrieve the header names
  if (state.cur_row === undefined){
    state.cur_row = 1;
    var url = prepareDataUrl(state.cur_row);
    state.headers = JSON.parse(get({url: url})).values[0];
    getRowColumns();
  }
  
  state.cur_row++;

  var url = prepareDataUrl(state.cur_row);  
  var response = JSON.parse(get( {url: url}));
  var item; 

  // If retrieved row as valid data values
  if (response.values && response.values.length){
      item = response.values[0];
  }
  // Otherwise reverse back to first row
  else{
      state.cur_row = 2;
      url = prepareDataUrl(state.cur_row);  
      items = JSON.parse(get( {url: url})).values[0];      
  }
  // arrange rows into JSON object
  var data = {};
  state.headers.forEach(function (key, index){
      data[key] = items[index];
  });
  return JSON.stringify(data);
  
}

Remember 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 of course modify these values and change anything which you need.

Once all the rows have 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. Do not 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.

var data = {};
state.headers.forEach(function (key, index){
    data[key] = (1+Math.random())*items[index];
});

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.

Last updated