Google Sheets：按条件复制数据并随职位数变更调整行

问题原因 #

你使用的自定义函数返回数组时，当No of positions值减小，Sheet2中原有多余行的旧数据不会自动清除，导致后续列（如Owner）出现匹配错位。要实现行数自动增删，需改用触发式脚本直接操作Sheet2的内容，而非依赖自定义函数返回数组。

实现代码 #

替换原自定义函数为以下脚本：

function updateSheet2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');
  
  // 清空Sheet2的现有数据（假设第一行为表头，从第二行开始清除）
  const lastRow = sheet2.getLastRow();
  if (lastRow > 1) {
    sheet2.getRange(2, 1, lastRow - 1, sheet2.getLastColumn()).clearContent();
  }
  
  // 获取Sheet1的全部数据
  const values = sheet1.getDataRange().getValues();
  // 跳过表头行
  const dataRows = values.slice(1);
  
  // 生成重复后的数据数组
  const result = dataRows.flatMap(row => {
    const [, owner, role, , positionCount, qualification] = row;
    if (positionCount > 0 && qualification === 'Qualified') {
      return Array(positionCount).fill([owner, role, qualification]);
    }
    return [];
  });
  
  // 将结果写入Sheet2
  if (result.length > 0) {
    sheet2.getRange(2, 1, result.length, result[0].length).setValues(result);
  }
}

// 创建编辑触发：当Sheet1数据变更时自动更新Sheet2
function setupAutoTrigger() {
  ScriptApp.newTrigger('updateSheet2')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

使用步骤 #

1. 打开表格的脚本编辑器（顶部菜单栏：工具 > 脚本编辑器），删除原RepeatMatching函数，粘贴上述代码。
2. 点击脚本编辑器的运行按钮，执行setupAutoTrigger函数，按提示完成权限授权（第一次运行需授权，属于正常操作）。
3. 删除Sheet2中原来的=RepeatMatching(...)公式，确保Sheet2第一行为表头（如果有）。

额外说明 #

• 自动触发：此后修改Sheet1中No of positions或Qualification的值时，Sheet2会自动清空旧数据并重新写入对应行数的内容，彻底解决列错位问题。
• 手动更新：若不需要自动触发，可删除setupAutoTrigger函数，在Sheet2添加一个按钮（插入 > 绘图），将按钮绑定updateSheet2函数，点击即可手动更新。

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