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);
}

For the first question, if you control the sheet, it might be easiest to take an empty column, and place in its top cell =unique(Q1:Q). Then have your script get from there. — Jeremy Kahan
Jeremy, thank for the response, I have already tried this, but than there will be some problem: for example, I have 100 items in the first initial column, and 10 of them are unique values. If I add 101st value to the initial column, and this value will be unique, than I will get 11 unique values. But than I do not know how many unique values I will have, so I cannot tell exact range to get from the spreadsheet.. and If I choose the hole column with unique values, I will get a lot of empty items in the list... — Алексей Мельников

PeterT PeterT · Accepted Answer · 2019-04-15T21:23:32

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);
}

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

1 Answers