Dynamic Editing of Form Dropdown Creates Extra Column

Question

I'm relatively new to Google Scripts for Sheets/Forms, and have edited this answer to create a script that updates the options on a dropdown question whenever it is triggered. It works wonderfully, but with one small glitch. My code as below:

var idColumn = 1; 

function getSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ProjectList");
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();

  var docId = sheet.getRange(2,idColumn,lr,1).getValues(); //gets the data from the last row in selected column

  fillFormData(docId);
}

//fills form with document ID
function fillFormData(docId) {
  var formId = 'form ID goes here'
  var form = FormApp.openById(formId);
  var items = form.getItems();
  var item = items[1];
  //loop variables
  var thisValue = "";
  var arrayOfItems = [];
  var newItem = "";

  for (var i=0;i<docId.length;i++) {
    thisValue =docId[i][0];
    Logger.log('thisValue: ' + thisValue);
    newItem = item.createChoice(thisValue);
    arrayOfItems.push(newItem);
  };

  item.setChoices(arrayOfItems)

}

My hope was that, by updating the question using setChoices(), responses would be placed in the same column in the response sheet. Instead, each trigger of this script creates another column in the response sheet named "Project Number", overwriting one of the columns that were there before. Is this an expected behaviour, or does anyone know of a way to ensure that my responses go into the same column? Is there a way to direct responses to this question to a specific column in the response sheet?

Edit: As per the suggestions below, I've tried adjusting the macro to use .asListItems() with no success. The interesting behaviour here is that the new, duplicate column seems to overwrite one of my other columns rather than create a new one.

Here's my ProjectList, just a single column:

Project Number
Project A
Project B
Project C
Project D
Project E

The form consists of 35 questions, of a variety of types, split among 3 sections.

Google Sheets macros are a special script type. Please only use the tag google-sheets-macros for questions about this special script type. — Rubén, Apr 26 '19 at 01:35
The referred answer use asListitem but your code not. AFAIK we should use some `asSomethingItem` to be sure that Apps Script gets the right methods for the form item that we will change. — Rubén, Apr 26 '19 at 01:41
Can you share a version of ProjectList and what your form looks like? — Cooper, Apr 26 '19 at 19:35
Not reproducible. `new, duplicate column seems to overwrite one of my other columns` You're probably referencing the wrong item then. What is the number of dropdown item from the top? — TheMaster, Apr 29 '19 at 20:57

ross · Accepted Answer · 2019-04-30T09:06:14.367

I think you need to explicitly set the question you're trying to add the choices to, please see below example of working script to add new items to form.

function getSheet() {
   var sheet = SpreadsheetApp.openById('sheet id here');
}

var formId = "form id here";
var question = [{formFieldTitle:"question title here", worksheetName:"linked form sheet name here"}]

function newChoices(item, sheetName) {
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(sheetName)
              .getDataRange()
              .getValues());
  var choices = [];
  for (var i = 1; i < data.length; i += 1){
    choices.push(item.createChoice(data[i][0])); 
  }
  item.setChoices(choices); 
}

function addNewChoices() {
  var form = FormApp.openById(formId);
  var items = form.getItems();
  for (var i = 0; i < items.length; i += 1) {
    for (var j = 0; j < question.length; j += 1) {
      var item = items[i];
      if (item.getTitle() === question[j].formFieldTitle) {
        addNewChoices(item.asCheckboxItem(), question[j].worksheetName);
        break; 
      } 
    } 
  } 
}

Variable "question" should have the question header and the sheet name from which your form items are being added.

var question = [{formFieldTitle:"question title here", worksheetName:"sheet name here"}]

Also, make sure to change "asCheckboxItem" if your question is not a checkbox in the line below, see Interface Item documentation to determine what to use.

addNewChoices(item.asCheckboxItem(), question[j].worksheetName);

score 1 · Answer 2 · answered May 01 '19 at 15:21

Instead of using method createChoice() and setChoices(), you should use setChoiceValues() so it doesn't create a new question. By using setChoiceValues(), you are effectively updating the choices.

See the code below:

function optWriteItem_CHECKBOX_(paramItem, paramItemData) {
  try {
    var thisItem = paramItem.asCheckboxItem();
    var listChoices;
    var n, i;

    n = paramItemData.length;
    if(paramItemData.indexOf('__OTHER__') > 5) {
      listChoices = paramItemData.slice(5, n-1);
      thisItem.showOtherOption(true);
    } else {
      listChoices = paramItemData.slice(5, n);
      thisItem.showOtherOption(false);
    }


    thisItem.setTitle(paramItemData[0]);

    if(paramItemData[3].toLowerCase() == 'yes') thisItem.setRequired(true);
    else thisItem.setRequired(false);

    thisItem.setHelpText(paramItemData[4]);

    if(listChoices.length == 0) listChoices = [ 'Option 1' ];
    thisItem.setChoiceValues(listChoices);
  } catch(err) {
    Logger.log('optWriteAsType/opt=CHECKBOX : ' + err.message);
    console.error("optWriteItem_CHECKBOX_()", err);
    return {s:false, m:err.message};
  }

  return {s:true};
}

Source-code

Dynamic Editing of Form Dropdown Creates Extra Column

2 Answers2