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

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

1 Like

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.

This thread was one of a couple that helped tremendously in being able to automate the installation of temporary codes in door locks at my AirBnBs. I'm using Google App Script to pull reservation/guest information and send it to the Hubitat, then Rule Machine rules to schedule the addition and deletion of codes in the locks.

I have been having a difficult time in verifying that the proper codes are in the locks at the proper times and I discovered that part of the issue is that the values stored in my Hub Variables don't always match the values reported by the corresponding Variable Connectors. Have you experienced that at all?

2 Likes

Very good instructions!

1 Like

I use IFTTT to update cell value from webrequest. I think you could also use zapier or any other...I have not tried but I think that with the google api you could post a value directly.

I get an error (ressources not found for the url) do you know why? I made all the step... Thanks!

As the original post was closed, I wanted to ask here if the latest discussed problem was solved. The problem to send the geturl command to google forms. As for me I just tried and it doesn’t work.

Sorry, playing for 1 hour and nothing works. Then when writing a comment it suddenly is fine.

The value %text% of my virtual switch triggering the action contained a space. “Switch1 was turned on”

I still keep the comment. Maybe someone can help me how to change values with unsupported signs…

Hi,
I use this setup to get a cell value inside hub variable, but I get something weird. The cell value is some events from my calendar (I have a macro that extract events from calendar and sort it). It works well but for some events, the end of the event is in the cell but not transmitted to the variable.
I use formula to get the content as: Start:time, end:time, duration:time,title:event title,calendar: calendar name

For an event in particular, the cell contain all that information but the variable countain : start, end, duration

Why for some event its ok and for another one it only takes the half of the text?
Any ideas?
Thanks!

Hi,
I have created a second variable with connector, like you said. I just copy the formula from my first google sheet and paste it to another google document and changed the device id. My first variable works well but the second one is not updating at all... Do you know what it could be? Its another google account, maybe it takes another auth somewhere? I check everystep and its the same so I dont understand why it wont update!
Thanks!

here the log:

groovy.lang.MissingMethodException: No signature of method: ConnectorVariable.setVariable() is applicable for argument types: (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)

It looks like my value is not considered as string but it is... If someone could help forcing the cell to be string it would be appreciated, I tried the format and even text formula but its not working!

looks like a coma inside concatenate formula return something that is not considered as text...