HOW-TO: Pull Data from Google Spreadsheet into RM

Here is my long-awaited follow-up to an old tutorial, where I demonstrate a simple method for pulling data out of one or more cells in a Google Spreadsheet, using Rule Machine 5.1.

(Will flesh it out with illustrations ASAP, I promise.)

STEP ONE
Create or select an existing Google spreadsheet containing static or dynamic data you would like to extract into Rule Machine.
For this exercise, assume cells A1:B2 contain "Animal", "Age", "Cat", "6" and that we only want to import the cat's age (namely, the contents of cell B2) into our HE workflow.

STEP TWO
File > Share > Publish to Web > Sheet 1 > Comma-separated Values (.csv) > Start Publishing
and leave "Automatically republish" checked.
Copy long link.

STEP THREE
On Hubitat, create or select a rule in RM for which you would like to import data.
Create two new local variables of type String, "Import"and "Extract".

Create a new action as follows:
Set Variable > Set Variable > select variable name: Import > Set from HTTP GET response > paste link into "Enter URL" box

Create a second action:
Set Variable > Set Variable > select variable name: Extract > Token > check "Use variable to get token from" > select Import > Delimiter: \n > Index: 1 (Row 1 = 0, Row 2 = 1, etc.)

Create a third action:
Set Variable > Set Variable > select variable name: Extract > Token > check "Use variable to get token from" > select Extract > Delimiter: , (comma) > Index: 1 (Col A = 0, Col B = 1, etc.)

STEP FOUR
Install rule (if new) and Run Actions to test it out.
The desired cell value should now appear in Extract.

Q.E.D.

  • Libra

P.S. Surely there are more efficient methods for doing this type of data extraction, but I enjoy rolling my own without writing (or borrowing) apps.

5 Likes

I have no immediate need for this.. but this is great to hear as it could open up a little more creativity in automations.

Thanks for posting this. It'll be in the back of my mind for sure.

What do you use it for out of curiosity?

My original intention was to pull data points out of a spreadsheet that was being populated by IFTTT. However, since learning that Hubitat offers integrations with some of those same devices (e.g. LG and Withings), I find less need for doing so, since I can pull the data directly into a dashboard or variable on the hub.

The shortest route is always the best!

In the back of my mind I was leading up to creating awesome looking graphs in Google Sheets and importing them into tiles on my dashboard, but even that process is made much more accessible by community apps like Hubigraph or WebCoRE.

On my old hub, I was accustomed to hand rolling solutions like this as a workaround to its many I/O limitations. With Hubitat I have no such worries anymore.

Download the Hubitat app