0

I am using a script to update dropdowns in a Google form from a spreadsheet. There is a Questions#1 worksheet on which there are all questions that should be updated automatically in the form when any changes are made and/or a new question is added.

var ssID = "1Z7uUBnzS5zKnxGswKKNAgn2XbRunqIzQZMATDl7skoE";
var formID = "1LxORhL2WgU6teJ11ax7_HsFxhbpXbjtFXiuPH5qfEfA";

var wsData = SpreadsheetApp.openById(ssID).getSheetByName("Questions#1");
var form = FormApp.openById(formID);

function dropdown() {
//var labels = wsData.getRange(1, 1, 1, wsData.getLastColumn()).getValues()[0];
 var labels = wsData.getRange(1, 1, 1, wsData.getLastColumn()).getValues().map(function(row){
  return row[0];
});

  labels.forEach(function(label,i){
    var options = wsData
                  .getRange(2, i+1,wsData.getLastRow()-1,1)
                  .getValues()
                  .map(function(o){ return o[0] })
                  .filter(function(o){ return o !== "" });

    updateDropdownUsingTitle(label,options);
  });

}  


function updateDropdownUsingTitle(title,values) {
   var items = form.getItems();
   var titles = items.map(function(item){
   return item.getTitle();
});
  //after correction
  var pos = titles.indexOf(title);
  if( pos !==-1){
  var item = items[pos];
  var itemID = item.getId();
  updateDropdown(itemID,values); 
}

///the old
   var pos = titles.indexOf(title);
   var item = items[pos];
   var itemID = item.getId();

  updateDropdown(itemID,values); 
}


function updateDropdown(id,values) {
  var item = form.getItemById(id);
  item.asListItem().setChoiceValues(values);
}  

But this script is updating only the first question in the list of Questions#1. by contrast it should not. what is the wrong with the script?

  • If you need to clarify your own question or to share your findings during your continued search/research efforts, please edit the question to add to it all the relevant details. – Rubén Mar 22 at 20:59
  • 1
    Thank you! I have found what was going wrong! I have added if( pos !==-1){ to updatedropdownusingtitle function so that it can ignore an extra staff that is irrelevant. Now it is working – Aroon Advisory Group Mar 22 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.