Exporting Apilio data into a Google Sheet at regular intervals for historic data storage

So it’s great to have the CSV export of the event logs, but I wanted a way to view and store my variables over a longer period of time to see how they changed. As spreadsheet should be perfect for this purpose, and I find Google Sheets pretty easy and powerful at the same time, I dug around a bit and found a Sheets script to call the Apilio REST API to retrieve specific data.

So the steps I followed are:

  1. Create a new Google Sheet, best to call it something relevant and then go to Tools > Script editor

  1. Delete everything in the script window so it looks like this:

  2. Paste in the following script, replacing the yellow-highlighted BLAHBLAHBLAHBLAHAuthorizationHeaderFromApilio with the actual Authorization Header from Apilio (see screen shot after the script to find it):

function callApilioREST() {

var headers = {

‘Accept’ : ‘application/json’,

‘Authorization’ : ‘Basic BLAHBLAHBLAHBLAHAuthorizationHeaderFromApilio’

}

var options = {

‘method’ : ‘get’,

‘headers’: headers

};

response = UrlFetchApp.fetch(‘https://api.apilio.com/api/v1/tuyaconditions’, options);

// Make request to API and get response before this point.

Logger.log(response.getContentText());

var json = response.getContentText();

var data = JSON.parse(json);

var fact = response.getContentText();

var sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);

}

  1. Showing where to find the Authorization Header in Apilio as the yellow-highlighted Authorization in the script must be replaced with your own personal Authorization Header from Apilio! Go to User Profile and scroll down to find the API access key.

  1. Save the Script project, giving it a relevant name
  2. Run the callApilioREST function or just press the Run button
  3. Your spreadsheet, from which you created the Sheets Script, should now be populated with the data requested from the Apilio REST API. In this example it will grab a list of your Tuya conditions but have a look at the Apilio API documentation for more details.

So now there’s the question of how to refresh the data on a regular basis. If you go to the Trigger menu in your script you can set a periodic refresh rate, like below, to keep the Sheet requesting updated data from Apilio. In the example below I have set a trigger to call the Apilio REST API every 5 mins.

So this gets my data into my Google Sheet but the format is a bit pants (it’s a JSON response apparently) for mortals like me to make much use of. So now I am looking for a way to extract the data into individual columns and rows. Will let you know how I get on OR any help gratefully received! DanT

4 Likes

Great post @DanT , thanks!
The yellow highlighting got lost, but it’s still easy to find the part authentication text that needs to be replaced.
To convert JSON to column, here’s quite a comprehensive overview (maybe we could provide a boilerplate Google Sheet script for the Apilio API?): https://hackernoon.com/3-best-ways-to-import-json-to-google-sheets-ultimate-guide-3k8s24ya
I think this is somewhat possible in Excel with the Power Query module: https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

2 Likes

Yes that’s the script I’m trying but it’s not doing much yet. Will seek some help from the Portuguese :mage:!

1 Like