Update Google Forms field based on dynamic data from a Google Sheets spreadsheet

Question

In a Google Form we have a pull down selection list.

The list of items may change from minute to minute. The master copy of the data is in a column in a google docs spreadsheet.

When a user accesses the form, we want them to see the latest copy of the pull down list as is current in the spreadsheet.

I don't see an obvious way to do this with triggers. onOpen, for example, only triggers for administrators of the form, not users. Is there a way I can keep this form up-to-date automatically?

This is my first time in google docs, but I'm very experienced in excel scripting.

Answer 1

The work flow needs to be a bit different than what you've described. Since there is no ability for Google Apps Script to interact with an end user accessing a Form, there's no way to react to that. However, you can react to changes in the spreadsheet, and update the form options in preparation for the next end user.

• Depending on how the options get updated in the spreadsheet, you can use an installable trigger there, to react to an edit or a change.

• If the form is associated with the spreadsheet containing the choices, you can get a handle on the form using Spreadsheet.getFormUrl(), then pass the URL to FormApp.openByUrl(). Otherwise you will need to provide the ID or URL explicitly to FormApp.openById() or .openByUrl(), respectively.

• That spreadsheet trigger function can modify the available selections for the relevant question by using ListItem.setChoices() or ListItem.setChoiceValues().