CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Umair Mukhtar
Umair Mukhtar

Reputation: 51

Populate Options on Google Forms From Google Sheets using App Script

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;
}

Upvotes: 0

Views: 1758

Answers (2)

Codedabbler
Codedabbler

Reputation: 1082

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

Upvotes: 0

Wicket
Wicket

Reputation: 38150

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,

  1. Run the function manually. To make this easier you might add a button on custom menu on the source spreadsheet to call the function.
  2. 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)

Related

Upvotes: 1

Related Questions