HOW-TO Store data in Google Sheets directly from RM5

Wanted to pop in here and say think you for doing this. Loved the detailed instructions...made it super easy to follow along. Just an FYI, Google has updated their Forms page and now you have to go to "Responses", then the three dots, then "Select response destination" to choose where to save the spreadsheet, instead of "Settings".


Hey @claywhitenack, would love for you to review how I'm setup from above and see what I might be missing, can't get it to work so far

It will populate the sheet if I use the URL directly, but not when using the Send GET command

1 Like

Sure. I'd be happy to. When I get home I'll compare my settings to your screenshots and see if I see anything out of whack.

I see you have tried copying and pasting the URL from RM. Try doing that while in Incognito Mode, if you haven't already. The fact that you can get it to work with the URL in the logs makes me think it is an issue on Google's side, not Hubitat.

hey @toy4rick , did you ever try sending that GET from an incognito browser? I can't find anything wrong with the code you are using. And the fact that you can successfully send the code that is generated in the logs tells me it is not on hubitat's end.

@claywhitenack, yes, did that 2 weeks ago, it was successful, just can't get the Send GET to work from the Rule

The reason I asked about sending from an incognito browser was to make sure Google would receive the GET from outside your google account. That really is a headscratcher. Have you tried creating a new rule, copying the log code into an action, then trying to run that action manually?

It might be the incognito mode- certain things are 'stripped' in the xhr and the cache is also torn off. Try opening a different browser maybe?

Just tried Chrome (non Incognito) on a different computer as well as Edge, both worked to update the Google Sheet


1 Like

Just tried myself and can confirm the issues above - copy/pasting the link submits the form, but hubitat cannot directly.

Right now I just store data to a local comma seperated file, and that's easily imported into sheets (and whatever else). You might be able to make it accessible remotely with port forwarding of some sort?

Bizarre! Thanks for confirming. I haven't wanted to chime in since I truly have no insights as to what the underlying cause could be, aside from Hubitat suddenly (in some recent firmware update) beginning to URL-encode its outbound POSTs.

Is anyone able to "watch" the actual traffic? Maybe not using a URL specific to this thread, but any POST sent to a waiting external client ... capture and examine for differences??

I followed this excellent post about using Rule Machine to post to a Google Form only to find it didn't work, much like the final commenters before that post was closed.

Like those final commenters, the URL worked in a browser (normal or incognito), but the Rule Machine wouldn't post, and the logs gave no hint of what went wrong.

But I found that a dummy rule that just used the URL directly did work. Which got me to thinking: was it the "%getURL%" that was the problem?

Sure enough, when I modified my rule to not use a variable for the URL but instead included it literally (with variables only in the query string), it worked.

I suspect that something in the rule machine is trying to look at the URL before it's interpolated (maybe to see if it's going to be HTTP or HTTPS?) and getting tripped up.

So as long as you don't need to vary the URL, this seems to fix it.

1 Like

Thanks for the update!