Google App Script：如何按固定模式补全表格缺失数据行

核心问题分析 #

遍历行时从前往后插入会导致后续行索引偏移——插入新行后原行位置后移，循环逻辑会跳过或重复处理行。解决关键是从最后一行往前遍历，插入操作不会影响未处理的行索引。

完整实现代码 #

function fillMissingCategories() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  // 固定类别循环序列
  const categorySequence = ["Age", "Sex", "Drug Allergies", "History"];
  
  // 从后往前遍历，规避插入行导致的索引混乱
  for (let i = values.length - 1; i >= 0; i--) {
    const currentCategory = values[i][0]; // 假设类别在A列
    
    const currentIndex = categorySequence.indexOf(currentCategory);
    if (currentIndex === -1) continue; // 非目标类别，直接跳过
    
    // 计算前一个应出现的类别
    let prevIndex;
    if (currentIndex === 0) {
      if (i === 0) continue; // 第一行是Age，无需处理
      prevIndex = categorySequence.length - 1; // 新案例Age的前一个应是上一个案例的History
    } else {
      prevIndex = currentIndex - 1;
    }
    
    const expectedPrevCategory = categorySequence[prevIndex];
    const actualPrevCategory = i > 0 ? values[i-1][0] : null;
    
    // 前一行不符合预期，说明存在缺失类别
    if (actualPrevCategory !== expectedPrevCategory) {
      const missingCategories = [];
      
      if (currentIndex === 0) {
        // 新案例Age，补全上一个案例缺失的后续类别
        const lastCaseIndex = categorySequence.indexOf(actualPrevCategory);
        for (let j = lastCaseIndex + 1; j < categorySequence.length; j++) {
          missingCategories.push(categorySequence[j]);
        }
      } else {
        // 序列中间类别，补全当前类别之前缺失的项
        for (let j = prevIndex + 1; j < currentIndex; j++) {
          missingCategories.push(categorySequence[j]);
        }
      }
      
      // 插入缺失行，同步更新数组
      missingCategories.forEach((category, idx) => {
        const insertRow = i + idx;
        sheet.insertRowAfter(insertRow);
        sheet.getRange(insertRow + 1, 1).setValue(category); // 类别列
        sheet.getRange(insertRow + 1, 2).setValue("Nil"); // Data列（假设是B列）
        values.splice(insertRow, 0, [category, "Nil"]);
      });
    }
  }
}

关键细节说明 #

• 反向遍历逻辑：从后往前处理，插入新行不会干扰未处理的行索引，避免循环漏判或重复处理。
• 序列匹配判断：通过indexOf定位当前类别在固定序列中的位置，对比前一行类别是否符合循环顺序。
• 缺失类别补全：分两种场景处理缺失——新案例Age需补全上一个案例的剩余类别，序列中间类别需补全当前位置之前的缺失项。
• 数组同步：插入行后更新values数组，保证后续循环判断基于最新数据。

使用注意事项 #

• 请确认类别列是A列、Data列是B列，若列位置不同，修改代码中对应的列索引（如values[i][0]对应A列，sheet.getRange(...,2)对应B列）。
• 运行前建议备份数据，避免意外修改。
• 若案例间有空白行，需先删除空白行，或在代码中加入判断跳过空白行。

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