HOW-TO Store data in Google Sheets directly from RM5

Inspired by a YouTube video entitled "Post HTML Form Data to Google Spreadsheet", I wanted to create a brief tutorial on how to send data directly from Rule Machine (RM5) on Hubitat to a spreadsheet in Google Drive.

This method is very straightforward, not too challenging to set up, and involves no programming skill. It also avoids installing a purpose-built database like MongoDB or subscribing to paid services like IFTTT or Apilio (both of which also offer "Variables" stored in the cloud.)

Furthermore, this setup leverages the inherent power of Google Sheets' formulas, email notifications, graphing and other features, enabling you to produce presentation-ready charts and reports summarizing your home automation processes.

STEP 1: Create the Form

  • Visit drive.google.com and click NEW > Form
  • Give your Form a title and populate it with one or more "Questions" of type "Short Answer".
  • I will refer to the question titles as Field A, Field B, etc. for brevity, but you can name them anything, such as Temp, Lux, etc.
  • Click `RESPONSES" and choose "Select response destination" > "New Spreadsheet" (it will be automatically created in Google Drive), or just click the green "Spreadsheet" icon.
  • Tip: Be sure "Accepting responses" slider is set to ON or data will not be logged.
  • Optional: Turn on email notifications for each SUBMIT if you wish to be alerted every time new data posts.

STEP 2: Generate the GET URL

  • Click Preview ('eye' icon) to open a live copy of the completed Form
  • Copy-and-paste the Form URL – ending in /viewform – into a text editor, like Notepad
  • Press F12 (in Chrome) to open devTools and click on the Network pane
  • Fill out the live Form with sample data and click SUBMIT
  • Click formResponse in left column of the Network pane in devTools
  • With Payload highlighted, scroll down to FORM DATA
  • Manually copy each field name listed in the form entry.1234567 in order (paste them in text editor)
  • Replace /viewform in the copied Form URL with /formResponse
    NOTE: This will serve as your GET base URL in Step 3.

STEP 3: Prepare a Rule in RM5

  • Create or edit a rule in Rule Machine
  • Create new local String variable(s) fieldA, fieldB, etc. – one for each question appearing on your Form – and set their default value(s) to the string (e.g. entry.1234567) you copied from line ◘ in Step 2, above.
  • Create a matching set of local variables dataA, dataB, etc., setting each one's type to suit your needs, with default value 0, null or false where appropriate.
  • Create a local String variable getURL and paste in the modified URL from Step 2.
  • Click "Done with Local Variables" to exit. The finished set of local variables should resemble this screenshot:

STEP 4: Send your data

  • Add an Action to the rule of type
    Send, Speak or Log a Message, Send HTTP RequestSend HTTP Get, and in the Enter URL to send request to field, type:
 %getURL%?%fieldA%=%dataA%&%fieldB%=%dataB%

NOTE: This example sends two fields, but you can reduce or expand as needed to include &%fieldC%=%dataC%, etc.

  • Click "Done with action* to complete.
  • IMPORTANT: If you intend for the data to come from another source (e.g. device parameter) then your rule must set dataA, dataB, etc. at runtime. And if another rule will be used to set those values, make sure that dataA, dataB, etc. are all Hub Variables, not local ones.
  • When triggered, the Rule will post the current values of variables dataA, dataB, etc. to a new row of the assigned spreadsheet, using the Google Form as an invisible pass-through. Each dataset will appear on a new row with its timestamp automatically entered in Column A.

STEP 5: Review your data

  • To view the stored values, simply open the receiving Spreadsheet in Google Drive and examine its FormResponses tab.
  • Columns will be named Timestamp, Field A, Field B, etc.
  • The timestamps in column A reflect when RM5 posted data.
  • You can easily use this data to generate graphs, calculate complex formulae, export as a .CSV file, etc.

Enjoy! I may one day post (UPDATE: DONE) a follow-up tutorial on extracting values from Google Sheets and storing them in a Hub Variable for use in Rule Machine. For now, I hope this serves as a fun, makeshift and FREE database for storing your important data directly from RM5.

PRO TOP: If your entries come up blank (shaded in blue, below) then double-check that you've entered the values of fieldA, fieldB, etc. correctly in the form entry.1688513225 without quotes or spaces.


Note that my Form field labels – which get auto-transposed into column titles – are Parameter, Value and Notes since those happen to suit my particular workflow. FYI, Rows 5 and beyond were all populated using this basic technique in Tasker for Android. And lastly, while the latest dataset row would normally appear at the bottom of the spreadsheet, I've used the Google Sheets function

=SORT('Form Responses 1'!A:D,1,FALSE)

in cell A1 of a separate worksheet to transpose entries, sorted by time, so they read chronologically from top to bottom. I've also added the formula

=(NOW()-A2)

in Column E to calculate how long ago each entry was made. There is no practical upper limit on the number of rows your 'database' spreadsheet can contain (mine is almost up to 11,000 rows!), and Google does not throttle this API in any way.

23 Likes

Very cool work! I'm was recently thinking how to write an app that would merge devices that have batteries with an external sheet so as to track the associated battery type, change date, install date... maybe a changed quantity value even an expected price per unit so as to monitor the associated cost over say a year... then be able to display it in a tile. Hubitat Dashboard supports URLs in tiles, it should be a cinch!
Lunch first ...

2 Likes

uhoh! Google appsheet is going to assimilate me... wow. all I have to do on the hub side is track the DNI's of battery devices and maybe the names...

You made me recall that behind the scenes, Google also offers a robust scripting API called Google Apps Script, which could (in theory) react to the data you send to the sheet. That reaction could be almost anything you can code for in that environment, from managing documents in your Drive account, sending email notifications, adding items to your Google Calendar, making further POST/GET calls, you name it.

1 Like

and so it begins:
image

1 Like

Very cool and out-of-the-box thinking.
Opens a big door for a lot of things.

Wonder how many other cloud spreadsheet environments would allow for the same.

Microsoft ?
Zoho ?

So.... then this happened. I wrote an app for the phone that manages the battery data (thats external to HE). I only pull the HE list of battery devices, their DNI, name and type. Still a lot to do - like a pick list for the batteries, cleanup images/icons etc. Currently the app is a private app - not sure if it should be published or not ... @LibraSun tell me what you think bud!

I don't think I can post video here so this is a link to my google drive.

https://drive.google.com/file/d/1lWUU5dS1-C1-zXlJnEby0eV5evm6waP1/view?usp=sharing

Also - no idea if anyone outside me can see this but heres the web version:

1 Like

Now that's some mega-cool shiznel right there! Nice work. Always fun to see things get extended in the direction of practical uses for automation, eh?!

1 Like

Yeah, very cool.

I'm only gonna say this - given that device battery management & monitoring is one of the biggest system management hassles/concerns discussed...you have provided a template for how this could/should be implemented on the HE Cx platform in the future (without resorting to an outside/cloud resource).

1 Like

Thanks for the vote of confidence - it's just for personal use ATM but it has some teeth I think. Right now struggling getting the HE list connected and managing the records in the sheet using DNI. Still working on the HE If I delete an device from the pick list etc.
I've reworked the template to include a battery manufacturer and a battery qty. as I surveyed my environment I realized some devices has multiple batts... like 2 AAA (philips hue motion sensor) or 4 AA (Bosch PIR EG:). Anyways. The external sheet has now been converted to an app that can be put on a mobile device, as well as run as a web app in a browser and it's holding it's own. Some layout glitches I'd like to fix - but the pick list for the battery type is nice. I also added graphs by manufacturer and shortly I'll add graphs of date changes someone else suggested but that a bit farther off.
Now, I'm concentrating on the HE side. I've got the initial app started and I can select from a list of devices that report battery capability but I've not yet learned how groovy will let me get selective over the array of devices - some of them are virtual but report battery such as Owntracks (which reports the cell phone battery). I sorta wanted the user to have the abiliity to not have to select devices, it would just crank out a list and build out / update the table. I asked if anyone could help me create an exclude but so far I can't find any examples of how thats done.
Thats the status! I'll be working on this off and on through the week and post up if I can make this usable for others. right now, it's working well for my stuff!

Here's an updated video of my app on the phone so far.
https://drive.google.com/file/d/1mz-8yw5XWKtzRkO_bkcsM0t4-TjV1VeV/view?usp=sharing

I just wish I could offer you guys a convenient method for round tripping values out of the cloud and back into the Hubitat. Unfortunately, unless you use Groovy, there is no direct way to pull values via HTTP request and store them in a variable.

1 Like

So much individual effort here, and useful. Hats off to you guys. But what if.....

2 Likes

@LibraSun many thanks for your effort here. That said, try following your step-by-steps to you see if every detail is accurate as you have them listed?

1 Like

I proofread it carefully and made a few tweaks, but think the step-by-step outline holds together. You'll let me know if I omitted something important or worded it weirdly?

@LibraSun, is there an easy way at the set variable step to deal with spaces and other symbols that break the exporting of data to gSheets? Energy duration has that space between 3 and upper case D.
image

I can see how that might be a problem, since HE doesn't have built-in string substitution (e.g. putting underscores _ for spaces ' '). Although webCoRE may? Still, you're so close, perhaps you'd be willing to just do a quick-and-dirty wholesale replacement of all whitespace with underscores or plus signs '+' in your RM actions.

PRO TIP: See judicious use of URLEncode method, below.

yep, thanks @LibraSun

1 Like

Also worth noting how this is also in RM.

1 Like

Much better! Didn't know that operator exists. Thanks for posting that alternative, which is far superior.

1 Like

@bertabcd1234 you were kind enough in other threads to comment about the use of State and RM. Has anything changed since you last mentioned this in other threads? Webcore does provide access to State for this use case where data is written out to Google Sheets but I'm not seeing how to use State along with RM to accomplish this.

Download the Hubitat app