In an earlier installment (here), I promised a brief tutorial on pulling values from cells in a Google Docs 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:
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.