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.
Follow the step by step:
1. Create a new Google Sheet, best to call it something relevant and then go to Tools > Script editor
2. Delete everything in the script window so it looks like this:
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!
4. Here’s where you find the Authorization Header in Apilio
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.
5. Save the Script project, giving it a relevant name and make sure to click run.
At this point it will add some new functions into Google Sheets that will make getting the REST API data super-easy.
6. Now you can return to the spreadsheet you created the script in
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:
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/boolean_variables”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/string_variables”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/numeric_variables”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/conditions”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/variableconditions”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/tuyaconditions”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/timeconditions”)
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/logicblocks”)
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.
=ImportJSONBasicAuth(“https://api.apilio.com/api/v1/boolean_variables/AddYourUUIDHere”)
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
Script you need to copy
The script can be copied from GitHub (direct link to code)
Go to the project homepage on Github for additional information