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

Hi @DanT ,
I know this is an old post, but an easier way of logging variables to Google Sheets in a readable form is to get IFTTT to do it for you! Call an IFTTT applet that receives an event from Apilio, and adds a row to a spreadsheet. You can pass up to three things (text, variables, whatever) from Apilio and have them written into columns–you can also write the current time, which is useful. I use this route for logging actions, and also (via an Apilio timer) for recording values of variables.

1 Like

Thanks for this Robin, good suggestion! DanT

1 Like

Hi - I would like to log data from room thermometers at regular intervals over this winter. I think this topic is discussing this. I can see how to use IFTTT to add a row to Google Sheets, what I cannot understand is how the event delivers the apilio variables. Please advise? Or should I be using the Script/REST API approach as suggested higher? Thank you :sunglasses: Jt

1 Like

Hi @JtGIlbert
@pebneter described the process here: IFTTT action – additional value parameters - Getting Started & How-To’s - Apilio Community

How exactly to do this depends on how your temperature data are being stored. If you have the values in Apilio variables then you just pass those variables to IFTTT as described. Where are you getting your temperature values from?

2 Likes

Hi - yes I have tuya temperature sensors for inside - and they have worked beautifully as you suggested [apilio variables for ifttt]( Don’t have a weather station yet for outside temperature. Other topics have discussed this. Am currently looking to see if “webhooks” from a website that reports the current temperature might be used… :wink: Jt

1 Like

You could add a “with” item to your IFTTT route that grabs the weather from Weather Underground, then write that into the Google sheet as well as the Apilio data…

1 Like

You can also bring the Weather Underground temperature into Apilio with an IFTTT applet, if it helps that all data is in Apilio.

1 Like