CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-apigoogle-forms
user3288051
user3288051

Reputation: 614

How to change an existing Google Form element using Google Apps Script when the form initially loads

I have an existing Google Form which is manually created, i.e. by adding form elements using the GUI. There is a dropdown field called "Team Member ID Number" as shown below.

enter image description here

What I need is to pull those ids of the team members from a spreadsheet when the form loads.

I know how to pull data from a spreadsheet using Google Apps Script. I just don't know how I can make any change to an existing Google Form field using Apps Script. I want to display those ids, coming from outside (spreadsheet), in that dropdown. Any idea how I can accomplish that?

Upvotes: 0

Views: 6627

Answers (3)

Mani Garlapati
Mani Garlapati

Reputation: 1

There is nothing like On form submit in (view mode). Currently there is no way to make changes to the view only form. triggers can be called only when the form is in edit mode.

Upvotes: 0

user3288051
user3288051

Reputation: 614

Thanks for your answer @ZektorH. It worked. But I didn't use the onOpen, because it would allow only the owner of the form to have that list. Instead, I used the On form submit (view mode).

Thanks again for all your help.

enter image description here

enter image description here

Upvotes: 0

ZektorH
ZektorH

Reputation: 2760

You can do this with Apps Script and a trigger.

Here is the function that performs the question creation:

function populateQuestionWithSheetsData() {
  var form = FormApp.getActiveForm();
  var questions = form.getItems();
  for (var i=0; i<questions.length; i++) {
    if (questions[i].getTitle()=="Dynamic Question Title") { //You can replace this if you know the ID of the question you want to change
      var list = questions[i].asListItem();
      var sheetsData = SpreadsheetApp.openByUrl("YOUR SHEETS URL").getSheets()[0].getDataRange().getValues();
      var choices = [];
      for (var j=1; j<sheetsData.length; j++) { //Starts on 1 to ignore first row (with column name ID)
        choices.push(list.createChoice(sheetsData[j][0]));
      }      
      list.setChoices(choices); //Overrides the question choices with the array of choices we just populated.
    }
  }
}

Then just create a new trigger that happens onOpen().

Hope this helps!

Upvotes: 1

Related Questions