CayExcel - REST API Tutorial / Demo using Excel VBA


#1

This is an Excel spreadsheet with some VBA code, that that gives you simple access to all your Cayenne data inside a spreadsheet with just your username/password, and a few clicks. You can load hour/day/week data series of your sensors, send data to Cayenne which can control your devices, and build fancy displays with text and graphics containing current values and history of your sensors.

It essentially gives access to your data without going through the website.
It is just a spreadsheet, not a service, so your information is just on your computer, interacting with mydevices.com databases.

The program displays all the REST GETs and POSTs, and then the raw REST responses, and then pulls apart the responses into the spreadsheets. And it creates nice Excel format timestamps for your timezone.

You have to download the spreadsheet from github, and then say Yes to running macros.
Of course, you can say no to macros, and read the code to see what it does first.

The green areas are for data entry, the orange is responses from Cayenne.





If you build some nice displays, you could post a screenshot of your artwork :grinning:

James


View all history of channel REST API
#2

nice write up @jameszahary :+1:


#3

When clicking get_one_datapoint() it says object required even when my Arduino Mega is selected. :frowning:

It shows Arduino Mega Channel 3 as the only drop down selection. But my photoresistor doesn’t appear.


#4

Have a look at the Sensors tab - it should list all device/sensor names in column B and C, and then the device/sensor numbers in column D and E, and the channel number in column F.

On the Sheet1 tab, you should see the device sensor numbers written there at the top, and then inside the GET query, along with the long Authorization number.

There is only a dozen lines of code, so you can hit ALT-F11 (on Windows) and step through it.

Rev2 is underway with error checking, exception handling, and all that tiresome stuff. But also including the ancient history query, and hopefully the aws megaquery.

Looking for info on the MQTT subscription system … but perhaps there is no shortcut, and I have to learn all that. :grinning:


#5

Updated CayExcel at github - same github link as above.

Added more error checking for bad data inputs, etc.

Also added Step 9 - General History query to fetch and graph data for for a bunch a pre-calculated data intervals (last 12 hours, last week, …) or any dates you choose to type in. To my surprise my data from last December is still in the database!

Question: Can anybody send me the “Hour” version of this query? This returns Minutes, but there must be a version for Hours???

https://platform.mydevices.com/v1.1/telemetry/af749630-e884-11e7-848a-61efd1c01e7d/sensors/ddb68760-e889-11e7-8934-ff70c6ef636b/summaries?type=custom&startDate=1533270373870&endDate=1533443173880


#6

Updated CayExcel at github.

Version 3 adds the RAW data query to see the exact data (and time) your devices sent, rather than the 1 Minute summary in the previous general history query. Picture shows the four 15 second data points in the new query (step 10), versus the “1 Minute” summary (step 9)

Also added some stats on the previous 1 Minute query (step 9).


#7

Nice post! I’ll have to give this a try.