HOW-TO: Import Hub/Device Data Into Google Spreadsheet

[LibraSun]

Before I embark on writing up this process, I'd like to know if any audience members are excited (or even interested) by the prospect of pulling information from your Hubitat C7 directly into Google Sheets, using a simple, automated process.

I'm hoping to craft this tutorial in the same vein as my well-received HOW-TO Store data in Google Sheets directly from RM5 – let's call that the "push" method – only this time using function calls from within the Spreadsheet itself (the "pull" method).

[Vincent_The_Staat]

I would be highly interessted. Please tell me more.

[djh_wolf]

I'm fairly excited by this. Sounds ace!

[LibraSun]

THIS POST WILL EVOLVE IN "CHAPTERS" SINCE IT IS A LEARN-AND-SHARE PROJECT

THEORY

• Hubitat can publish information about its Devices through the Maker API.
• The information is shared via private (hub-specific) URLs in JSON format.
• Google Sheets has the ability to import and parse data from a given URL.
• It is possible to refresh the imported data on a prescribed time schedule.
• A custom Apps Script can be employed to format JSON data into a table.
• Users can make appropriate selection(s) from that table to build reports.
• Advanced users can easily log, chart, graph, export or send notifications.

[jlv]

Just to mention, there are two apps that exist to do this. Both were developed on SmartThings and ported over to HE.

Chuck Schwer's Google Docs Logger

Activity monitor - logs in google sheets

I believe the problem is that someone converted it to a parent/child app in one which is not supported in Hubitat. Edit: Quick and dirty change to make it back into a single app, did not have time to test beyond a single Temperature report. Edit 2: just realized I had put this in my repo a while ago:

@krlaframboise's Simple Event Logger
Original ST thread: [RELEASE] Simple Event Logger - Community Created SmartApps - SmartThings Community
Hubitat version with installation instructions: krlaframboise-Hubitat/apps/krlaframboise/simple-event-logger.src at simple-event · loverso-hubitat/krlaframboise-Hubitat · GitHub

[LibraSun]

Well, that's refreshing (and I dare say not at all surprising) to hear! I'd like to assume -- without actually installing and testing that user app myself -- that it exposes all of a given (or multiple) device's attributes, so they can be hand picked cell-by-cell for use in formulae, reports, etc. If so, that's essentially the same road I was heading down (albeit with nothing needed to be installed on the HE side, since I was aiming for a "pull" solution vs the "push" method this app surely employs).

[nrus2222]

Hi @LibraSun.

I've been using your push method at HOW-TO Store data in Google Sheets directly from RM5, as well as @krlaframboise's app.

Did you ever get the "pull" method working from google sheets?

[LibraSun]

Yes and no (thanks for asking).

The weak link in the process turns out to be Rule Machine 5.1, who's basic action GET HTTP refuses to populate a variable (for me), despite extensive testing. To date, nobody has been able to show me a working example, so I cannot incorporate it into my planned tutorial about extracting values from Google spreadsheets.

So I gave up trying after a while.

[nrus2222]

Bummer. Thanks for the update.

[marktheknife]

@LibraSun

some of the ambassadors dissuaded me from further inquiry

I wonder if that’s the right term?

Inquiry is rarely, if ever, dissuaded here. It’s not like this online forum dedicated to home automation is the CIA, or the Nixon administration or something.

[djh_wolf]

@LibraSun

Yes and no (thanks for asking).

The weak link in the process turns out to be Rule Machine 5.1, who's basic action GET HTTP refuses to populate a variable (for me), despite extensive testing. To date, nobody has been able to show me a working example, so I cannot incorporate it into my planned tutorial about extracting values from Google spreadsheets.

So I gave up trying, especially when some of the ambassadors dissuaded me from further inquiry after I'd posted the question in 3-4 different online forums.

webcore route ?

[LibraSun]

Finally published the sequel to this thread
here, for pulling data out of Sheets into RM. Expect me to revise and add screenshots once able.

Enjoy!