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 asTemp,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
SUBMITif 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
Networkpane - Fill out the live Form with sample data and click
SUBMIT - Click
formResponsein left column of theNetworkpane in devTools - With
Payloadhighlighted, scroll down toFORM DATA - Manually copy each field name listed in the form
entry.1234567in 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
getURLand 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 Request ► Send 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 thatdataA,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
FormResponsestab. - 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.
