Update Google Form Choices Info-CodePudding

We want to make a Google Form where there are dropdown options pulled from column F of the sheet (Sheet1), beginning in row 3 on down. However, column F has formulas in it, specifically: =IF(D$="","", CONCATENATE(C$," - ",D$)), so that some of the cells appear blank while others have visible text.

The code we attempted to use below does not work. Any help on how to make this work by pulling choices from column F, but of course ignoring blank cells?

var form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

const current = sheet.getRange(3,6,sheet.getLastRow()-1,6).getValues()
var range = sheet.getDataRange();
 
 var rangeList = current.map(function (row, i) {
 
 for (var i=rangeList; i<range.length; i  ) { 
  if (row[5] == "") return;
  var matched = row[5];

  const Question = form.getItemById ("620176576")
  Question.asListItem().setChoiceValues(matched)
 }
 })
}

CodePudding user response：

You've to use filter to only get the values which are not null.

Try below sample script:-

const form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

const current = sheet.getRange(3,6,sheet.getLastRow()-3).getValues().flat().filter(r=> r)  //filtering out blank values

const Question = form.getItemById("620176576")

Question.asListItem().setChoiceValues(current)

Reference:

filter()