如何不删除原有问题更新Google Form的描述与选项，避免响应表新增列？

你现有代码的核心问题是每次执行都会清空所有原有问题再重新创建，这才导致响应表新增列。要实现不删除原有问题直接更新，按以下步骤修改即可：

步骤1：删除清空原有问题的代码段 #

把你代码里的以下这段完全删掉：

var items = form.getItems();
while(items.length > 0){
  form.deleteItem(items.pop());
}

步骤2：调整核心逻辑为「先更新现有问题，不足再新增」 #

核心逻辑是：先获取表单现有所有问题，按电子表格的行顺序和表单的问题顺序一一对应，对应位置有符合类型的问题就直接更新内容，没有对应问题或者类型不匹配再新增。

完整修改后的代码 #

function FormCreator() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = ss.getDataRange(); 
  var data = range.getValues();
  var numberRows = range.getNumRows();
  var firstRow = 1;
  var form = FormApp.openById('12z4pe1XM1qhUOdhL198w8ut_hW0Mwh_rZnZGjVBIbTQ');
  // 获取现有表单的所有问题
  var existingItems = form.getItems();
  // 记录当前处理到表单的第几个问题
  var currentItemIndex = 0;

  for(var i=0;i<numberRows;i++){
    var questionType = data[i][0]; 
    if (questionType==''){
       continue;
    }
    var targetItem = null;
    // 先检查当前索引有没有对应问题，类型是否匹配
    if(currentItemIndex < existingItems.length){
      var item = existingItems[currentItemIndex];
      var itemType = item.getType();
      // 类型匹配判断
      var typeMatch = false;
      switch(questionType){
        case 'TEXT': typeMatch = itemType === FormApp.ItemType.TEXT; break;
        case 'PARAGRAPH': typeMatch = itemType === FormApp.ItemType.PARAGRAPH_TEXT; break;
        case 'CHOICE': typeMatch = itemType === FormApp.ItemType.MULTIPLE_CHOICE; break;
        case 'CHECKBOX': typeMatch = itemType === FormApp.ItemType.CHECKBOX; break;
        case 'LIST': typeMatch = itemType === FormApp.ItemType.LIST; break;
        case 'GRID': typeMatch = itemType === FormApp.ItemType.GRID; break;
        case 'IMAGE': typeMatch = itemType === FormApp.ItemType.IMAGE; break;
        case 'PAGE': typeMatch = itemType === FormApp.ItemType.PAGE_BREAK; break;
        case 'SECTION': typeMatch = itemType === FormApp.ItemType.SECTION_HEADER; break;
        case 'TIME': typeMatch = itemType === FormApp.ItemType.TIME; break;
      }
      if(typeMatch) targetItem = item;
    }

    // 分类型处理更新/新增
    if(questionType=='TEXT'){
      if(targetItem){
        targetItem.asTextItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRequired(true);   
      }else{
        form.addTextItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRequired(true);   
      }
      currentItemIndex++;
    } 
    else if(questionType=='PARAGRAPH'){
      if(targetItem){
        targetItem.asParagraphTextItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRequired(true);
      }else{
        form.addParagraphTextItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRequired(true);
      }
      currentItemIndex++;
    }
    else if(questionType=='CHOICE'){
      var rowLength = data[i].length;
      var currentRow = firstRow+i;
      var numberOfColumnsSheet = ss.getLastColumn();
      var lastColumnInRange = String.fromCharCode(64 + (numberOfColumnsSheet));
      var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
      var optionsArray = ss.getRange(range_string).getValues();
      var choicesForQuestion =[];
      for (var j=0;j<optionsArray[0].length;j++){
        if(optionsArray[0][j]) choicesForQuestion.push(optionsArray[0][j]);
      }
      if(targetItem){
        targetItem.asMultipleChoiceItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }else{
        form.addMultipleChoiceItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }
      currentItemIndex++;
    }
    else if(questionType=='CHECKBOX'){
      var rowLength = data[i].length;
      var currentRow = firstRow+i;
      var numberOfColumnsSheet = ss.getLastColumn();
      var lastColumnInRange = String.fromCharCode(64 + (numberOfColumnsSheet));
      var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
      var optionsArray = ss.getRange(range_string).getValues();
      var choicesForQuestion =[];
      for (var j=0;j<optionsArray[0].length;j++){
        if(optionsArray[0][j]) choicesForQuestion.push(optionsArray[0][j]);
      }
      if(targetItem){
        targetItem.asCheckboxItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }else{
        form.addCheckboxItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }
      currentItemIndex++;
    }
    else if(questionType=='LIST'){
      var rowLength = data[i].length;
      var currentRow = firstRow+i;
      var numberOfColumnsSheet = ss.getLastColumn();
      var lastColumnInRange = String.fromCharCode(64 + (numberOfColumnsSheet));
      var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
      var optionsArray = ss.getRange(range_string).getValues();
      var choicesForQuestion =[];
      for (var j=0;j<optionsArray[0].length;j++){
        if(optionsArray[0][j]) choicesForQuestion.push(optionsArray[0][j]);
      }
      if(targetItem){
        targetItem.asListItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }else{
        form.addListItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setChoiceValues(choicesForQuestion)
          .showOtherOption(true); 
      }
      currentItemIndex++;
    }
    else if(questionType=='GRID'){
      var rowLength = data[i].length;
      var currentRow = firstRow+i;
      var numberOfColumnsSheet = ss.getLastColumn();
      var lastColumnInRange = String.fromCharCode(64 + (numberOfColumnsSheet));
      var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
      var optionsArray = ss.getRange(range_string).getValues();
      var rowTitles =[];
      for (var j=0;j<optionsArray[0].length;j++){
        if(optionsArray[0][j]) rowTitles.push(optionsArray[0][j]);
      }
      currentRow = firstRow+i+1;
      range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
      optionsArray = ss.getRange(range_string).getValues();
      var columnTitles =[];
      for (var j=0;j<optionsArray[0].length;j++){
        if(optionsArray[0][j]) columnTitles.push(optionsArray[0][j]);
      }  
      if(targetItem){
        targetItem.asGridItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRows(rowTitles)
          .setColumns(columnTitles)
          .showOtherOption(true); 
      }else{
        form.addGridItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setRows(rowTitles)
          .setColumns(columnTitles)
          .showOtherOption(true); 
      }
      currentItemIndex++;
      i++; // GRID占两行，跳过下一行
    } 
    else if(questionType=='IMAGE'){
      var img = UrlFetchApp.fetch(data[i][3]); 
      if(targetItem){
        targetItem.asImageItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setImage(img);
      }else{
        form.addImageItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2])
          .setImage(img);
      }
      currentItemIndex++;
    }
    else if(questionType=='PAGE'){
      if(targetItem){
        targetItem.asPageBreakItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }else{
        form.addPageBreakItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }
      currentItemIndex++;
    } 
    else if(questionType=='SECTION'){
      if(targetItem){
        targetItem.asSectionHeaderItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }else{
        form.addSectionHeaderItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }
      currentItemIndex++;
    }
    else if(questionType=='TIME'){
      if(targetItem){
        targetItem.asTimeItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }else{
        form.addTimeItem()
          .setTitle(data[i][1]) 
          .setHelpText(data[i][2]);   
      }
      currentItemIndex++;
    }
    else{
      continue;
    }
  } 
  // 如果表单还有多余的旧问题，可以选择删除，不需要就注释掉下面这段
  while(currentItemIndex < existingItems.length){
    form.deleteItem(existingItems.pop());
  }
}

注意事项 #

• 第一次运行修改后的代码前，请确保表单里的现有问题顺序、类型和电子表格里的行顺序、类型完全一致，避免修改错位
• 后续修改问题内容、选项直接改电子表格对应行的内容，重新运行代码即可同步到表单，不会改动关联响应表的列，原有响应数据会保留
• 新增问题直接在电子表格最后添加对应类型的行即可，代码会自动在表单末尾新增对应问题

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