Google Apps Script问题：在setChoices中嵌入条件判断实现动态表单选项

Got it, the issue here is that you can't drop an if statement directly inside an array literal when calling setChoices() — JavaScript doesn't allow statements in that expression context. Instead, you'll want to build your choices array dynamically before passing it to the method. Here's how to fix this:

Step-by-Step Solution #

1. First, grab the actual values from your spreadsheet cells (your original code stores Range objects, not cell content, so comparisons would fail).
2. Initialize an empty array to hold your dynamic choices.
3. Use standalone if checks to add choices only when the corresponding cell value is "Fine".
4. Pass the completed array to setChoices().

Modified Working Code #

var ss = SpreadsheetApp.openById("1QARjdbtFpERRkP7Mw7Ud56plOygMzQawjQbXsbf9Hgw");
// Get cell values instead of Range objects
var mh1Status = ss.getRange("Helicopter Status!C4").getValue();
var mh2Status = ss.getRange("Helicopter Status!C5").getValue();
var hellcat1Status = ss.getRange("Helicopter Status!C6").getValue();
var hellcat2Status = ss.getRange("Helicopter Status!C7").getValue();

var form = FormApp.getActiveForm();
var items = form.getItems();
var deleteold = items[2];
form.deleteItem(deleteold);
Utilities.sleep(200);

var item = form.addListItem();
// Initialize empty array for dynamic choices
var choices = [];

// Add choices conditionally based on spreadsheet status
if (mh1Status === 'Fine') {
  choices.push(item.createChoice('MH-9 #1'));
}
if (mh2Status === 'Fine') {
  choices.push(item.createChoice('MH-9 #2'));
}
if (hellcat1Status === 'Fine') {
  choices.push(item.createChoice('Hellcat #1'));
}
if (hellcat2Status === 'Fine') {
  choices.push(item.createChoice('Hellcat #2'));
}

// Apply the dynamic choices to the list item
item.setTitle('Helicopters')
 .setRequired(true)
 .setChoices(choices);

var updatedItems = form.getItems();
var movenew = updatedItems[5];
form.moveItem(movenew, 2);

Key Notes #

• I changed your Range variables to store cell values directly with getValue() — this fixes the core issue where you were comparing a Range object to a string ("Fine"), which would never match.
• Building the choices array with push() lets you conditionally add items without breaking JavaScript's syntax rules.
• If none of the helicopters are marked "Fine", the dropdown will be empty. You might want to add a fallback option (like choices.push(item.createChoice('No available helicopters'))) or set setRequired(false) if that scenario is possible.

内容的提问来源于stack exchange，提问作者Jord Duineveld