How to use unique items from Google SpreadSheet as a choice to Google Forms List Item?

Question

I'm new with Google scripts and now I have to make a form with a list of choices. These choices should be picked up from the Google sheet. So the first question is how to chose only unique values from some range of my spreadsheet? The second is how to pass this list so that they will be the items in the list?

The code I've tried is:

function getMembranesList() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/......");
  var itemList = ss.getSheetByName('Answers').getRange("Q1:Q").getValues();
  var form = FormApp.getActiveForm();
  var item = form.addListItem()
  item.setTitle('test question');
  item.createChoice(itemList);
}

Answer 1

Looking at the methods available to populate the ListItem, you have to choose one and set your data up so it matches the expected input. For my example, I chose the setChoiceValues method, which looks for an array. So I have to manipulate the items into an array.

One thing the getRange.getValues() method does NOT get you is how many non-blank items are returned in the list. I used this quick way to get a count of those items, so I have a maximum bound for my loops. Then, I formed the itemArray and added only the non-blank items to it.

After that, it's just a matter of creating the ListItem and adding the values:

function getMembranesList() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/...");
  var itemList = ss.getSheetByName('Answers').getRange("Q1:Q").getValues();
  var itemCount = itemList.filter(String).length;
  var itemArray = [];
  for (var i = 0; i < itemCount; i++) {
    itemArray[i] = itemList[i];
  }
  var form = FormApp.getActiveForm();
  var item = form.addListItem();
  item.setTitle('test question');
  item.setChoiceValues(itemArray);
}