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.)

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.

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

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.)

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


  • 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.


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.

Thanks for the information in your post, I have a use for this and was wondering if you had any example screenshots available yet? Particularly creating the rule in Rule Machine.

Hello, I managed to get this working however only seems to import the top 4-5 lines of the sheet. I would like to extract the last line from the sheet which is periodically updated from another source that adds a new line of data at the end. Would you have any suggestions.
Many thanks

I'm struggling to get this to work. As a basic first step, I've populated the spreadsheet you suggest ("Animal", "Age" in row 1, "Cat", "6" in row 2).

I've followed the other steps, but the rule is showing this:

Presumably the value shown for the Extract variable ought to be "6", i.e. the value in cell B2 of the spreadsheet? Any ideas why this isn't working? I suspect it's a fairly basic issue, but my knowledge on this is zero. Thanks!

To answer my own question, there appears to be a bug in Hubitat but this thread solved the issue:

Setting String variable from HTTP GET gives - :triangular_ruler: Rule Machine® - Hubitat

The extract variable is indeed now showing a value of 6.

Thanks for the tutorial. My plan is now to create a number of rules by using IFTTT to populate a spreadsheet on a daily basis.

1 Like

Is there a way to display the Extract Variable on a dashboard? I can only see my global variables on the dashboard, and setting a global variable using your method doesnt seem to be an option

Found my own answer I had to change my Hub Variable to a string variable instead of decimal

1 Like