So in a previous article we discussed how to export REST API data from Apilio into Google Sheets. But the output was a bit erm difficult to interpret unless you are a machine. So in this follow on we share a way to retrieve the data in a format more suitable for sharing/graphing/analysing etc.
1. Create a new Google Sheet, best to call it something relevant and then go to Tools > Script editor
3. Copy the long script at the end of this article and then paste into the Google Sheets script window
The script can also be found here in Github replacing the BlinkingLongStringAPIAuthorizationHeaderCopiedFromApilio with the actual Authorization Header from Apilio (see step 4 to find your Authorization Header in Apilio). Make sure you copy the whole script and update your Authroization Header in the script or it won’t work!
as the bold-italicised text Authorization in the script must be replaced with your own personal Authorization Header from Apilio! Go to User Profile and scroll down to find and then copy the API Authorization Header. Then replace the bold-italicised text in the script with your own Header.
At this point it will add some new functions into Google Sheets that will make getting the REST API data super-easy.
and use the new import JSON functions that the script has added. For example you can paste ImportJSONBasicAuth(“https://api.apilio.com/api/v1/boolean_variables”) and this will grab a nicely formatted list of your variables. Here is a list of simple Sheets functions you can paste in to access different data:
If you want to get detail about a specific Apilio element then you can find its UUID (universal ID) from one of the functions above and paste that after the call using a forward slash “/AddYourUUIDHere” in the example below.
Here is an example of the output, showing my Tuya Conditions with the detail of a specific Tuya condition line below:
You can also grab the UUID from Apilio on the element page via the webhooks or REST API section.
Hope this helps with a quick view of your Apilio setup and would be fascinated to see how others might use it so as always please please please let us know of any comments/questions/thoughts! DanT