Behaviour of Google Form script to set choice values adds unwanted "," to string in the souce

user_16287583 0
Viewed 43

The Google script I have gets data from a sheet column and updates the choices in a Forms Dropdown list item. However while the item in the source is 'mystring' the loaded option is mystring with added ",". .... Thus "Dog" in the source becomes "Dog," in the Forms Question drowpdown list of items. What is wrong with my script?

function updateFormFromData() {
 
  var ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/'<mysheetID>'/edit');
  var sheetAL = ss.getSheetByName('FormDataAL');
  var lastAL = sheetAL.getLastRow();
  console.log(lastAL);
  options = sheetAL.getRange('A2:' + lastAL).getValues();
  console.log(options)
  const form = FormApp.getActiveForm(),
    allItems = form.getItems();
  Logger.log(allItems);
  formQ2 = allItems[2].asListItem();
  formQ2.setChoiceValues(options);

var sheetMZ = ss.getSheetByName('FormDataMZ');
  var lastMZ = sheetMZ.getLastRow();
  console.log(lastMZ);**strong text**
  options2 = sheetMZ.getRange('A2:' + lastMZ).getValues();
  console.log(options2)
  formQ3 = allItems[4].asListItem();
  formQ3.setChoiceValues(options2);
}
google-forms google-apps-script
1 Answers

Issue:

You are not setting the A1 notation appropriately when calling getRange(a1Notation). Since you are not specifying the column letter (A) after :, the retrieved range includes all the remaining columns in the sheet.

Because of this, when you want to use it as the item options, each option is a multiple-element array, which gets forcibly converted to a comma-separated string.

For example, as you can see at Google Sheets API Overview:

Sheet1!1:2 refers to all the cells in the first two rows of Sheet1.

Solution:

Add the A after :.

Replace these lines:

// ...
options = sheetAL.getRange('A2:' + lastAL).getValues();
// ...
options2 = sheetMZ.getRange('A2:' + lastMZ).getValues();
// ...

With these ones:

// ...
options = sheetAL.getRange('A2:A' + lastAL).getValues();
// ...
options2 = sheetMZ.getRange('A2:A' + lastMZ).getValues();
// ...

Note:

  • I'd suggest using flat on options and options2 in order to get an array of strings, not an array of single-element arrays.
user_106120110
Related