Populate Options on Google Forms From Google Sheets using App Script

Question

I am using the below code to populate options on Google Forms from Google Sheets. And It is working. The issue I am facing is that options are populated/updated when I refresh/reopen the edit mode of Form after changing options in Google Sheet. I want that Form automatically populate/update the options from Google Sheets when the view/submission mode of the form is refreshed or reopened.

{
populateQuestions();
}

function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
var itemsArray = form.getItems();
itemsArray.forEach(function(item){
 googleSheetsQuestions[0].forEach(function(header_value, header_index) {
   if(header_value == item.getTitle())
   {
     var choiceArray = [];
     for(j = 1; j < googleSheetsQuestions.length; j++)
     {
       (googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;
     }
     //item.asMultipleChoiceItem().setChoiceValues(choiceArray);
     // If using Dropdown Questions use line below instead of line above.
     item.asListItem().setChoiceValues(choiceArray);
   }
 });     
});
}

function getQuestionValues() {
var ss= SpreadsheetApp.openById('1VUxlMl100R33lfbiiZ8JiNIQjiqW-SyvCQc197Vkc4k');
var questionSheet = ss.getSheetByName('Services');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}

What do you mean by `the view/submission mode`? You want to update the items after each form submission? — johndee31415, Jul 20 '22 at 08:53
the view/submission mode means the view through which responses are submitted. Yes, I want when the form is refreshed/reopen/submitted, update the items. — Umair Mukhtar, Jul 20 '22 at 11:34
Did you try using an on form submit trigger? You can read about it in the [docs](https://developers.google.com/apps-script/guides/triggers/installable) — johndee31415, Jul 20 '22 at 11:46

Rubén · Answer 1 · 2022-07-20T14:54:41.160

From the question:

I want that Form automatically populate/update the options from Google Sheets when the view/submission mode of the form is refreshed or reopened.

That is not possible with "basic" Google Apps Script because Google Apps Script because Google Apps Script doesn't include any method / trigger that works on the view/submission mode.

One option might be that you create a web-browser extension that detect that the view/submission mode of the form was opened, then use the Google Apps Script Form Service / Google Apps Script API or the Forms API to update the form options and refresh reload the view/submission mode for the form.

If you are open to slightly change your approach, instead of updating the options when the view/submission mode is done,

Run the function manually. To make this easier you might add a button on custom menu on the source spreadsheet to call the function.
Use the Google Sheets edit or change triggers in such way that every time that the options are updated on the sheet, the changes are populated in the form.

To implement the any of the above you could add your script as a library to the a project contained by the spreadsheet or adapt the code to run in from the project contained by the spreadsheet, i.e. instead of FormApp.getActiveForm() use FormApp.openById(formId)

score 0 · Answer 2 · answered Apr 25 '23 at 17:50

0

Use a time-based trigger. This can keep your form updated to within a minute of any changes in the source spreadsheet.

answered Apr 25 '23 at 17:50

Codedabbler

143
10

Populate Options on Google Forms From Google Sheets using App Script

2 Answers2