Using Google Forms to Store Data from MSR
-
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 Multi-System Reactor (MSR) to your own spreadsheet.
This method is very straightforward, not too challenging to set up, involves no programming skill, and avoids installing a purpose-built database like MongoDB or subscribing to paid services like IFTTT or Apilio (both of which 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 modify them to suit your purposes. - Click
SETTINGS
and choose "Save Responses to > New Spreadsheet" (it will be automatically created in Drive) - Optional: Turn on email notifications for each
SUBMIT
STEP 2: Generate the GET URL
- Click
Preview
('eye' icon) to open completed live Form - Copy-and-paste (into a text editor, like Notepad) the Form URL (ends with /viewform)
- Press F12 (in Chrome) and click
Network
pane - Fill out Form with sample data and click
SUBMIT
- Click
formResponse
in left column ofNetwork
pane - With
Headers
highlighted, scroll down toFORM DATA
- Copy (into text editor) each field name of the form
entry.1234567
in order - Replace the /viewform in Form URL with /formResponse
NOTE: This will serve as your GET base URL in Step 3.
STEP 3: Prepare a Rule in MSR
- Create or Edit a Rule in MSR
- Add new blank Expressions of the form
fieldA
,fieldB
, etc., one for each question appearing on your Form. - Add the Expression
getURL
and paste in the modified URL from Step 2, surrounded by double quotes. - Add the Expression
getData
and type the following formula:"?entry.1234567=" + fieldA + "&entry.7654321=" + fieldB
(continuing as needed for all X fields)
STEP 4: Send your data
- Add to Set Reaction as many
[Set Variable]
actions as needed to store future values infieldA
,fieldB
, etc. (and/or pre-populate some Expressions with numbers or text now). - Add an
[HTTP Request]
action in Set Reaction. - Populate its "Request URL" field with
${{ getURL + getData }}
SAVE
andEXIT
- When triggered, the Rule will invisibly post the current field values to a new row of the assigned spreadsheet, using the Google Form.
STEP 5: Review your data
- To view the stored values, simply open the receiving Spreadsheet and examine its
FormResponses
tab. - Columns will be named
Timestamp
,Field A
,Field B
, etc. - The timestamps in column A reflect when MSR posted data.
- You can easily use this data to generate graphs, add columns to calculate complex formulae, export as a .CSV file, etc.
Enjoy! I may one day (in the distant future) post a follow-up tutorial on extracting values from Google Sheets for use in MSR. For now, I hope this serves as a fun, makeshift and FREE database for storing your important data directly from MSR.
-
T toggledbits locked this topic on