Using Google Forms to Store Data from MSR

[LibraSun]

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.

[LibraSun]

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 of Network pane
• With Headers highlighted, scroll down to FORM 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 in fieldA, 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 and EXIT
• 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.