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 SETTINGSand 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 Networkpane
- Fill out Form with sample data and click SUBMIT
- Click formResponsein left column ofNetworkpane
- With Headershighlighted, scroll down toFORM DATA
- Copy (into text editor) each field name of the form entry.1234567in 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 getURLand paste in the modified URL from Step 2, surrounded by double quotes.
- Add the Expression getDataand 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 }}
- SAVEand- 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 FormResponsestab.
- 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 T toggledbits locked this topic on
 














