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:
- Create a new Google Sheet, best to call it something relevant and then go to Tools > Script editor
-
Delete everything in the script window so it looks like this:
-
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]);
}
- 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.
- Save the Script project, giving it a relevant name
- Run the callApilioREST function or just press the Run button
- 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
!
Jt
Jt