Google Script更新表单多选选项异常：连续重复选择超订问题排查

我们用Google Form作为预约入口，响应表收集用户数据并触发Google Apps Script；另有独立工作簿存储线下（In Person）、线上（Remote）两类预约的日期、时段及剩余座位数，分别放在不同工作表中。
流程逻辑：用户提交表单后，脚本扣减对应时段的座位数，当座位数为0时，该时段从表单选项中移除。

多数场景运行正常，但连续两次选择同一类型同一时段预约时出现异常：

• 第一次提交后，工作表中座位数已显示为0，但该时段仍保留在表单选项中；
• 第二次提交后，座位数变为-1，该时段才会从表单选项中被移除。
  非连续选择同一时段时无超订问题。

已尝试的优化：

• 在响应表变更、打开、表单提交时触发脚本；
• 在存储日期的工作簿中添加同步脚本，编辑工作簿或打开表单编辑器时拉取更新，但仅能隔离连续重复场景，未解决根本问题。

function autodates(e) {
  
// Get a reference to the spreadsheet "Placement Test: User Editable Appointment Form (Responses)"
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

//Get a reference to the Form 
  const form =FormApp.openById("1XfvnBAnd1-fqgd5kCycv8fUToCTnLPeysb9Niw_j7bk")
  
//For the purpose of writing this program, the form's question ID's are necessary. Use the following script to get the unique item ID for the questions of the form by logging them to this and selecting run while you reproduce this program for other forms. When you run this, you will be required to give permissions! Collect the Id's for the form questions that you will update with the dates listed on a separate form so that you can identify the question to edit for your form.
  var questions = form.getItems();
  questions.forEach(question => {
    Logger.log(question.getTitle())
    Logger.log(question.getType())
    Logger.log(question.getId().toString())
  })
  
//First date list question retrieved by the logger.logs above:
  const dateQuestion = form.getItemById("1598842866");
 
//Second date list question retrieved by the logger.logs above:
  const dateQuestion2 = form.getItemById("713650670");
  
//UPDATING DATES
    //Get a reference to the workbook you want to access that has the list of dates and how many seats available per date
    
const dateListWB = SpreadsheetApp.openById("1EnNmU4Ii5BVruFOnlMF8pa7a7EoASZ33CweeR8_vdmg");
    
//First Date Question
//Get a reference to the correct tab of the workbook associated with the corresponding dates for the question. In this case, its the first tab:
    
const iPsheet = dateListWB.getSheetByName("In Person Appt Dates");
    
//and specify the range you want to collect. Since the dates are in column A, and number of seats in B:
    const iPsheetArray = iPsheet.getRange("A3:B").getValues();
    
//but you don't want to get the rows with empty spaces:
    const filterediPsheetArray = iPsheetArray.filter(iProw=>iProw[0]);
    
//Limit the choices so that if the value of column B, the number of seats, falls below 1, it can no longer be used
    const limitchoice = filterediPsheetArray.filter(function (iProw) {
      return iProw[1] >= 1;
    });
    Logger.log('limitchoice');
    Logger.log(limitchoice);
    
//The second number in the splice below indicates THE NUMBER OF DATE CHOICES that will show on the form for this question.
    if (limitchoice.length > 0) {
      var datecut = limitchoice.splice(0,5).map(function(row) {
        return row [0]
      });
      Logger.log('datecut');
      Logger.log(datecut)
    } 
    if (datecut.length > 0) {
      dateQuestion.asMultipleChoiceItem().setChoiceValues(datecut);
    } else {
      dateQuestion.asMultipleChoiceItem().setChoiceValues([]);
    };

//Second Date Question
    
//Get a reference to the correct tab of the workbook associated with the corresponding dates for the question. In this case, its the second tab:
    const remSheet =dateListWB.getSheetByName("Remote Appointment Dates");
    
//Get a reference to the Range you want to access from that sheet
    const remSheetArray = remSheet.getRange("A1:B").getValues();
    
//filter out any empty rows from the sheet
    const filteredremSheetArray = remSheetArray.filter(remrow => remrow[0]);
    
//Limit the choices so that if the value of columb B (the number of seats) falls below 1, it can no longer be used as a choice
    const limitchoice2 = filteredremSheetArray.filter(function(rem3row){
      return rem3row[1] >= 1;
    });
    Logger.log(limitchoice2);
    
//The second number in the splice below indicates the number of date choices that will be on for this question.
    if (limitchoice2.length > 0) {
      var datecut2 = limitchoice2.splice(0, 5).map(function(rem2row) {
        return rem2row[0];
      });
    }
    if (datecut2.length > 0) {
      dateQuestion2.asMultipleChoiceItem().setChoiceValues(datecut2);
      Logger.log(datecut2);
    } else {
      dateQuestion2.asMultipleChoiceItem().setChoiceValues([]);
    }

//UPDATING THE COUNT (or the number of seates for the date/time)
  
//First Date
//Get the date the student selected
  const rDateChoice = e.values[5]; //e.values are the numbered cells 
  counting the first column as [0]. Column B is [1] etc.
  const dateChoice = rDateChoice;
  
//Get the range of the data to search through
  const range = iPsheet.getRange("A:B");
  const data = range.getValues();
  
//Set the starting row index.
  let rowIndex = 1;
  
//Loop through the date choices until a blank row is encountered.
  while (rowIndex <= data.length && data[rowIndex][0] != "") {
    
//decrease value in column B by 1
    if (data[rowIndex][0] === dateChoice) {
      iPsheet.getRange(rowIndex +1, 2).setValue(data[rowIndex][1] - 1);
      break;
    }
    //move to the next row.
    rowIndex++;
  }
  
//Second Date
  const rDateChoice2 = e.values[6];
  const dateChoice2 = rDateChoice2;
  
//Get the range of the data to search through
  const range2 = remSheet.getRange("A:B");
  const data2 = range2.getValues();
  
//Set the starting row index.
  let rowIndex2 = 1;
  
//Loop through the date choices until a blank row is encountered.
  while (rowIndex2 <= data2.length && data2[rowIndex2][0] != "") {
    
//If the search string is found, decrease the value in column B by 1.
    if (data2[rowIndex2][0] === dateChoice2) {
      remSheet.getRange(rowIndex2 + 1, 2).setValue(data2[rowIndex2][1] - 1);
      break;
    }
    
//Move to the next row
    rowIndex2++;
  }
}

1. 核心逻辑顺序错误：脚本先执行「更新表单选项」，再执行「扣减座位数」。第一次提交时，表单选项更新用的是扣减前的座位数（比如原本是1，更新时仍显示该时段），之后才把座位数改成0。连续第二次提交时，脚本再次读取座位数（此时是0，但表单没更新用户还能选），扣减成-1后才更新表单，此时该时段才被移除。
2. 变量未定义风险：datecut和datecut2仅在if分支内定义，若分支不执行，后续判断会报错。
3. 索引与范围不匹配：线下时段读取A3:B，但搜索用整个A:B范围，可能导致索引错位；循环起始索引设置为1，若数据起始行不符会引发搜索错误。

function autodates(e) {
  // 获取表单实例
  const form = FormApp.openById("1XfvnBAnd1-fqgd5kCycv8fUToCTnLPeysb9Niw_j7bk");
  // 获取日期选项存储工作簿
  const dateListWB = SpreadsheetApp.openById("1EnNmU4Ii5BVruFOnlMF8pa7a7EoASZ33CweeR8_vdmg");
  // 获取线下/线上对应的工作表
  const iPsheet = dateListWB.getSheetByName("In Person Appt Dates");
  const remSheet = dateListWB.getSheetByName("Remote Appointment Dates");
  // 获取表单中的两个日期选择问题
  const dateQuestion = form.getItemById("1598842866").asMultipleChoiceItem();
  const dateQuestion2 = form.getItemById("713650670").asMultipleChoiceItem();

  // ====== 第一步：先扣减座位数 ======
  // 处理线下预约座位扣减
  const dateChoice = e.values[5];
  if (dateChoice) {
    const ipData = iPsheet.getRange("A3:B").getValues();
    ipData.forEach((row, index) => {
      if (row[0] === dateChoice && row[1] > 0) {
        iPsheet.getRange(index + 3, 2).setValue(row[1] - 1);
      }
    });
  }

  // 处理线上预约座位扣减
  const dateChoice2 = e.values[6];
  if (dateChoice2) {
    const remData = remSheet.getRange("A1:B").getValues();
    remData.forEach((row, index) => {
      if (row[0] === dateChoice2 && row[1] > 0) {
        remSheet.getRange(index + 1, 2).setValue(row[1] - 1);
      }
    });
  }

  // ====== 第二步：更新表单选项 ======
  // 更新线下预约选项
  const iPsheetArray = iPsheet.getRange("A3:B").getValues();
  const filterediPsheetArray = iPsheetArray.filter(row => row[0] && row[1] >= 1);
  const datecut = filterediPsheetArray.slice(0, 5).map(row => row[0]);
  dateQuestion.setChoiceValues(datecut);

  // 更新线上预约选项
  const remSheetArray = remSheet.getRange("A1:B").getValues();
  const filteredremSheetArray = remSheetArray.filter(row => row[0] && row[1] >= 1);
  const datecut2 = filteredremSheetArray.slice(0, 5).map(row => row[0]);
  dateQuestion2.setChoiceValues(datecut2);
}

1. 调整逻辑顺序：先执行座位数扣减，再读取最新数据更新表单选项，确保表单选项始终反映当前剩余座位状态；
2. 简化数据处理：用forEach替代循环查找，代码更简洁且避免索引错误；
3. 变量安全处理：直接初始化datecut和datecut2，避免未定义报错；
4. 范围匹配：线下数据统一使用A3:B范围，确保读取和修改的索引一致；
5. 增加判断：仅在用户选择了对应类型的时段时才执行扣减逻辑，避免无效操作。

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