Google Forms Integrations: Best Practices

Google Forms & AppSheet

Best Practices

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.
:smiley:

Before we get into Best Practices, it’s important to understand a few things about Google Forms:

  1. 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.
  2. 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.
  3. You can re-arrange questions in the form, but the columns will stay in the same order, and the form continues to work.
  4. You can rename the column headers in the sheet, and the responses will still go to the correct column.
  5. You can re-order your columns in the sheet, and the form will still work.
  6. You can have two questions with the same text, and two sheet columns with the same header, and the form will still work.
  7. 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,

HOWEVER!

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

  1. Finalize Google Form first, before starting spreadsheet or connecting to app.
  2. Select response destination (adds new sheet to workbook).
  3. Rename tab and columns for easy use in AppSheet.
  4. Test form again, with new destination and column names.
  5. 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!
Screen Shot 2020-05-29 at 3.52.45 PM

  1. Fill out the form using whatever values you want. Using TEST# makes it easy to find the value in the next step.
  2. Click Get Link, then copy and paste it into a text editor.
    https://docs.google.com/forms/d/e/1FAIpQLScZg*****CtCaw/viewform?usp=pp_url&entry.930286630=TEST1&entry.1876527609=TEST2
  3. 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 TEST# with "&[ColumnName]&". Or if it’s the last question in the string, "&[ColumnName].
  4. 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

YOU CAN’T. :unamused:

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:

  1. Change the Form’s response destination to a new sheet in the desired workbook.
  2. 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.
  3. Delete the original sheet from the workbook.
  4. Rename the new sheet to the name of the sheet you just deleted.
  5. Relink table and regenerate in AppSheet. (May not be required, but should fix errors link is broken)

5 Likes

A client asked me to type up some Best Practices for Google Forms. I figured I’d share it here and see what else we can come up with as a community.

So, how have you used Google Forms in your apps?

1 Like

One practice I have shared within my team is to always remember to re-launch Appsheet add-ons for Forms and again click “PREPARE” and “LAUNCH” whenever any update to a google form previously connected to AppSheet utilizing AppSheet add-ons for Forms.

By doing that, AppSheet will re-connect to the form with the new form’s updated information. Sometime people just looking for reasons (for example) why an update to a form’s Multiple Choice didn’t get updated to the list of Enum in AppSheet and start messing around with manual change the enum which will go back again to previous original Enum from the Forms and to overcome that, I’ve shared this practice.

3 Likes

Great tip, @Heru! I didn’t realize the add-on would auto-update Enum values in the app!

I’ve seen the add-on before and didn’t pay it any attention because I thought it was only a shortcut/launcher for Chrome like a lot of other add-ons. Thanks for pointing this out!

[EDIT]
Just found some more useful info on the Forms Add-On:

2 Likes

It’s great topic you got here :+1: :+1: and this discussion is as complement to the reference document, Since, I believed, only few topics were asking about Google Forms in this community and not all is available in the reference document.

And in addition also, if someone already gone ahead updating AppSheet in the editor and want to do something else other than re-launching the Add-Ons, than a manual changes need to be made to the connected sheet header note, like below:
image

Changing the EnumValues manually will change also in AppSheet.
I give this recommendation once to a person in this community since he was already gone ahead with many changes in the AppSheet editor. This is not a best practice and avoid taking this steps unless it is really necessary to do so.

3 Likes

Thank you so much for sharing this @GreenFlux and @Heru
I would like to use Google Forms. For example:

Right now, our customers send E-Mails to us, if they have any claim, like a damaged part.
We don’t want to give our Clients access to our AppSheet App. It would be to much to tell them: “You need a Google Account, or Dropbox, or… and you have to sign into this App…”
But sending them a Link to a Google Form is way better.
It’s also faster than AppSheet. If an App needs 5 Seconds to load up, the client would begin to think what’s wrong. And he will end up saying: “Sending an E-Mail is easier for me.”

A hybrid would be: They send us an E-Mail, we copy paste the information in our AppSheet App and send the Google Form pre-filled link to the customer. In this Link will be all Information that he already send us via E-Mail. In the same time we could ask him to update missing information.
Would that be possible? Could he also add images via the Google Form?

2 Likes

Surely he can also add images through Google Form.
A “File Responses” folder will be created where the google forms is on the Google Drive. I just had a discussion in below thread for displaying images from Google form.

2 Likes

I know Google Forms has an option to allow responders to edit their response, but I haven’t tried that with a form connected to an app. You could collect a new response though, and match it up by name or ID, then update the existing record.

This is a neat reference @GreenFlux for anyone integrating Google forms with AppSheet. Thank you very much and also thanks to @Heru for adding his insights.

1 Like