Search code examples
google-apps-scriptgoogle-forms

Only 1 of 2 Google Forms dropdowns works as expected

I created a Google Form dropdown with an Apps Script that I found online that pulls the dropdown choices from another Sheet that I named "Choices". I needed the same list as options for 2 questions so I simply "duplicated" the 1st question to create the 2nd. I don't know why but changes to the Choices tab only show up for the 1st dropdown, not the 2nd.

I tried several things to fix this but none worked (see below). What do I need to do to fix this problem?

function updateDropdown() {

  // Get the form and the spreadsheet
  var form = FormApp.openById('(Link ID removed here)';  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Choices');

  // Get the data from the spreadsheet  
  var data = sheet.getRange('A:A').getValues();  
  var items = data.flat().filter(String);
 
  // Get the dropdown question in the form  
  var formItems = form.getItems(FormApp.ItemType.LIST);

  var listItem = formItems[0].asListItem();
 
  // Update the dropdown options  
  listItem.setChoiceValues(items);
}

I tried recreating the 2nd question from scratch and also move Question 2 to the top just to test and make sure the question syntax, etc wasn't the problem. It wasn't. When I moved #2 to the top, it picked up the changes to the list, but now #1 (now below it) didn't.

Solution

  • Try something like this:

    function updateDropdown() {

var form = FormApp.openById('xxxxx');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Choices');

// Get the data from the spreadsheet
var data = sheet.getRange('A:A').getValues();
var items = data.flat().filter(String);

// Get the dropdown question in the form
var formItems = form.getItems(FormApp.ItemType.LIST);

var listItem1 = formItems[0].asListItem();
var listItem2 = formItems[1].asListItem();

// Update the dropdown options
listItem1.setChoiceValues(items);
listItem2.setChoiceValues(items);

}

    You need to designate which form fields to update. The snippet below extracted from the script above chooses the first [0] and the second [1] fields:

    var listItem1 = formItems[0].asListItem();
var listItem2 = formItems[1].asListItem();

    Then each field is updated with the following code:

    listItem1.setChoiceValues(items);
listItem2.setChoiceValues(items);

    Sample