Google Forms & AppSheet
The intent of this post is to document certain Google Forms behavior as it relates to AppSheet, and create a Best Practices Guide by compiling input here from the community.
Please feel free to suggest edits and add links to other relevant posts.
Before we get into Best Practices, it’s important to understand a few things about Google Forms:
- Forms can be linked to save responses to a NEW SHEET ONLY. This can be in a new or existing workbook. But a form can NOT be linked to an existing sheet (tab). A new sheet will always be created when creating/changing the response destination.
- Adding a question to a form will add a column to the sheet. Renaming the question in your form will rename the header column in the sheet.
- You can re-arrange questions in the form, but the columns will stay in the same order, and the form continues to work.
- You can rename the column headers in the sheet, and the responses will still go to the correct column.
- You can re-order your columns in the sheet, and the form will still work.
- You can have two questions with the same text, and two sheet columns with the same header, and the form will still work.
- Columns get named after the question text, which tends to be a full sentence with punctuation. This works fine for stand-alone forms, but becomes very cumbersome in AppSheet when a single column name is a full sentence.
None of these changes interfere with the
Form <-> Sheet setup,
They DO break the
AppSheet <-> Sheet connection, and require regenerating the table. You can edit the options for a question without breaking the app, but any changes to the number of questions or their names will edit the sheet in a way that will require a table regeneration.
So, with all that in mind,
BEST PRACTICE FOR USING FORMS
- Finalize Google Form first, before starting spreadsheet or connecting to app.
- Select response destination (adds new sheet to workbook).
- Rename tab and columns for easy use in AppSheet.
- Test form again, with new destination and column names.
- Add as a table in AppSheet (New or existing app).
Remember that a future edit to the Google Form could break your app!
USING PRE-FILLED LINKS
Send users a link to your form with values pre-filled from your app data!
- Fill out the form using whatever values you want. Using TEST# makes it easy to find the value in the next step.
- Click Get Link, then copy and paste it into a text editor.
- Each question/column has a unique id. Figure out which
entry.########goes with each AppSheet column you want to pre-fill, and swap out the
"&[ColumnName]&". Or if it’s the last question in the string,
- Now that you have the URL, you can use HYPERLINK() to display the dynamic, prefilled link as any text you like.
ADDING A FORM TO AN EXISTING TABLE
It will always link to a new sheet. The easiest option is add the response destination as a new table to the app, then set up an action to copy them over to the main table. This can also function as an approval/screening process to keep the main table data clean.
But if you want a single table, and want to link an existing form:
- Change the Form’s response destination to a new sheet in the desired workbook.
- Copy the rows from the existing table, and paste them into the new sheet. You can insert them above if you want the existing entries to stay above new submissions.
- Delete the original sheet from the workbook.
- Rename the new sheet to the name of the sheet you just deleted.
- Relink table and regenerate in AppSheet. (May not be required, but should fix errors link is broken)