HOW-TO: Set Hub Variables from Google Sheets Spreadsheet

In an earlier installment (here), I promised a brief tutorial on pulling values from cells in spreadsheet back into Hubitat. Turns out that is not an easy proposition, possibly only doable using Groovy script.

So instead, I'm going to outline a simple way of pushing data directly from Google Sheets into one or more existing Hub Variables on HE, by way of variable connectors.

STEP 1A – CREATE A HUB VARIABLE
This is the easy part. Simply go into Settings ► Hub Variables and create a suitable variable (e.g. varAvg) of whatever type, to contain the data we will be sending to it.

STEP 1B – CREAT A CONNECTOR
Click "Create" under "Connector" and select "Variable" in the "Select Connector Type" drop-down. Jot down its Device ID by going to Devices ► "Variable Connectors" and click on the matching connector (e.g. varAvg); find the device number at the end of the URL in your web browser's address bar (e.g. http://192.168.86.38/device/edit/139 yields 139).

STEP 2A – INCLUDE THE CONNECTOR IN MAKER API
With the Maker API app installed under "Apps", click Maker API, then click to edit its list of devices under "Allow Endpoint to Control These Devices". Check the box next to the needed connector(s) (e.g. varAvg).

STEP 2B – COPY THE CLOUD URL
Under "Cloud URLs", copy the entire "Send Device Command" web address onto the clipboard and/or paste it into Notepad for safekeeping. Scroll down and click DONE.

STEP 3 – EDIT YOUR SPREADSHEET IN GOOGLE DRIVE
Create or open an existing spreadsheet from which you wish to send data. Click into any empty cell (e.g. B2). Change the cell's text wrapping mode to "Clip", either by clicking Format ► Wrapping ► Clip from the menu, or by clicking as shown:
send_data_03

STEP 4 – CREATE THE 'IMPORTDATA' FORMULA
With the long URL from Step 2B still in the clipboard, construct the following formula:

=LEFT(IMPORTDATA("<paste_url_here>"),10)

then immediately edit the URL in the following ways:

  • Replace [Device ID] with the number found in Step 1B
  • Replace [Command] with setVariable
  • Replace [Secondary Value] with " & A2 & " if you would like to set the variable's value dynamically from cell A2 within the same spreadsheet.

The modified formula should then read, in part, as shown here:


Press ENTER or click the green checkmark to finalize your formula entry.

STEP 5 – SEND THE DATA TO HUBITAT
Immediately after entering the formula, your Hub Variable will be updated with the value of cell A2. Thereafter, it will update again every time the value in A2 changes.
TIP: To avoid passing null or error values, make sure that A2 will only contain valid data – integers for Number, decimal numerics for Decimal, TRUE or FALSE for Boolean, alphanumeric characters for String, etc. – that comports with the variable type chosen in Step 1.

You can visually confirm the updated value(s) back in the Hubitat UI under Settings ► Hub Variables, and utilize these variables freely in Dashboards, RM rules, webCoRE pistons, etc.

2 Likes

I like what your doing here - In my case I was using the bidirectional capability of makerapi.
If you create a Virtual device and give it 100 attributes - you could 'push' back into the attributes the values - I think. I haven't done it fyi but that was my intent on the Battery monitor app so I could create tiles....

Among the purposes I envision people using this technique for:

• Performing complex variable arithmetic beyond the ability of either RM5 or webCoRE;
• Pulling in other data sources (e.g. XML or HTML) from external web data silos;
• Keeping a running total (e.g. data points in a column, or # times something triggered);
• Assembling multi-part text into a string (via CONCAT() ) to push to Dashboard;
• Pulling timestamps or other sophisticated Date/Time formatting, or computing elapsed time;
• Send RSS feed items (by subject line) or web images (by URL) to Dashboard;

I happen to love working in Google Sheets, so am pleased others might use it in new and imaginative ways as part of their home automation routines.

1 Like

Nice work! Can you also do the opposite and update a cell based on an HE attribute/connector value?

That should be simple to do, using the parse function instead of setVariable, but I must (for now) leave that as an exercise for the dedicated reader. I'm still recovering from writing the first article (linked at top of OP) showing how to send data from HE to a spreadsheet using form fields.

@librasun has been paving the way, and I don't mean to hijack this in anyway - it's almost been a 2 way dialog between us as he gets ideas then I do, then we share...
Anyways - his way works - I use 2 other methods.
You can push a 'write' to text file into hubitat file. then grab that file from an external web read.
Another way that I've been having fun with is to simplify it -
Push your variable into an attribute of a virtual device .
Setup Maker API and add that device to the devices list.
Using google sheets you can read the json right into a spreadsheet. works really nicely! I push all my battery capability devices out to Google Sheets this way, then am running a script to track the battery types and dates of installs. I even ran the webapps and created a mobile app that lets me see/edit the values!
Maybe play in either his or my sandbox idea? YMMV

2 Likes

so - round robin - sorta working! I created an app that lets me select all battery capable devices - I push the list of devices out to Google Sheets (using JsonImport). I have a mobile app that runs against the google sheets. If I update the data using the app on my phone, the updates appear back in HE!
I take a virtual tile from the data and using Hubitat Dashboard for Android I get:
image

Will take a bunch more work to clean this up - but thanks @LibraSun !! couldn't have done it without you!

2 Likes

Well, that's just neato burrito right there. Glad you were willing (frankly, I was not) and able to take the reins with all that hard work, showing proof of principle. This has turned out to be a good recipe for future readers, whom I hope will be inspired by your efforts.

2 Likes

Very cool what you guys have done. Respect.

Download the Hubitat app