如何避免Google Apps Script更新表单时重置国家选项的分段映射

Got it, let's sort out this problem! The issue with your current script is that using setChoiceValues() completely replaces all existing dropdown choices—this wipes out every section navigation mapping you've set up for each country. Instead of overwriting everything, we'll adjust the script to only add new countries from your spreadsheet while leaving existing choices (and their linked sections) untouched.

Modified Script #

var ssID = "sheet ID"; // Global Variable sheet ID

// Update form dropdown without resetting section navigation
function formUpdateCC() {
  var form = FormApp.openById("form ID");
  var countryListItem = form.getItemById("ItemID").asListItem();
  
  // Grab existing choices and their section mappings from the form
  var existingChoices = countryListItem.getChoices();
  var existingCountries = existingChoices.map(choice => choice.getValue());
  
  // Fetch the latest country list from your spreadsheet
  var countrySheet = SpreadsheetApp.openById(ssID).getSheetByName("Country");
  // Use getLastRow() to avoid pulling empty rows at the bottom of your sheet
  var countryRange = countrySheet.getRange(2, 1, countrySheet.getLastRow() - 1);
  var countryList = countryRange.getValues().flat().filter(country => country !== "");
  
  // Find countries in the sheet that aren't already in the form dropdown
  var newCountries = countryList.filter(country => !existingCountries.includes(country));
  
  // Add each new country as a separate choice (no impact on existing ones)
  newCountries.forEach(country => {
    countryListItem.createChoice(country);
  });
  
  // Optional: Remove countries that are no longer in the sheet (uncomment if needed)
  /*
  var countriesToRemove = existingCountries.filter(country => !countryList.includes(country));
  countriesToRemove.forEach(country => {
    var choiceToRemove = existingChoices.find(choice => choice.getValue() === country);
    if (choiceToRemove) {
      countryListItem.deleteChoice(choiceToRemove);
    }
  });
  */
}

Key Changes Breakdown #

• Ditched setChoiceValues(): Instead of replacing all choices, we first fetch existing options and their navigation settings with getChoices().
• Switched to getLastRow(): This ignores empty rows at the bottom of your sheet, so you won't end up with blank dropdown options.
• Compare Existing vs. New Countries: We filter out countries that are already in the form, so we only add the brand-new entries.
• Used createChoice(): This method adds individual new choices without touching existing ones—your pre-configured section links stay exactly where you set them.

Optional: Clean Up Outdated Countries #

If you want to remove countries from the dropdown when they're deleted from your spreadsheet, uncomment the optional block in the script. This will tidy up old options while keeping navigation links for remaining countries intact.

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