0

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?

user3288051
  • 574
  • 1
  • 11
  • 28
  • Each question is a specific item; so it is possible to write a script to target a specific list item and pull data from a spreadsheet - but this is not a real-time update. You want the data to update "when the form loads", but the documentation for Forms onOpen event trigger says: `The Google Forms-specific triggers let scripts respond when a user edits a form or submits a response.` Would you elaborate on your expectations for the update, and the forms responsiveness to live data. – Tedinoz Oct 15 '19 at 06:19
  • Thanks for your response @Tedinoz. You are right, the onOpen only allows the list to be dynamically populated on the edit mode. I figured out that it should be the "on form submit" event trigger instead. – user3288051 Oct 15 '19 at 10:18

3 Answers3

1

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!

ZektorH
  • 2,680
  • 1
  • 7
  • 20
0

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

user3288051
  • 574
  • 1
  • 11
  • 28
  • 1
    You've submitted this as an answer rather than a comment to the answer by @ZectorH. So, please provide the script that you used, and explain how this is different. In particular, what is "On form submit (**view mode**)" (my emphasis)? – Tedinoz Oct 16 '19 at 00:39
0

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.

Mani Garlapati
  • 1