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.
GitHub - dbochicchio/reactor-mqtt-contrib: Contrib MQTT templates for Reactor
