如何优化Google Apps Script的moveRows脚本以提升运行速度？

问题描述 #

我用一段Google Apps Script实现将符合条件的行移动到目标工作表并删除原行，脚本能正常运行但有时耗时很长，请问怎么优化提升速度？我尝试修改脚本后反而失效，怀疑是setValues和删除行的逻辑结合有问题，或者循环语句存在错误。

原脚本（可运行但速度慢） #

function moveRows(){
  var ss=SpreadsheetApp.getActive();
  var sh0=ss.getSheetByName('Source sheet');
  var sheet = SpreadsheetApp.getActive().getSheetByName('Target');
  var lastRow = sheet.getLastRow() + 1;
  var rg0=sh0.getDataRange();
  var sh1=ss.getSheetByName('Target');
  var vals=rg0.getValues();
  sheet.getRange(lastRow, 1).setValue(new Date())
  for(var i=vals.length-1;i>0;i--)
  {
    if(vals[i][0]=='OK')
    {
      sh1.appendRow(vals[i]);
      sh0.deleteRow(i+1)
    }
  }
} 

修改后失效的脚本 #

function moveRows(){
  var ss=SpreadsheetApp.getActive();
  var sh0=ss.getSheetByName('Source sheet');
  var sheet = SpreadsheetApp.getActive().getSheetByName('Target');
  var lastRow = sheet.getLastRow() + 1;
  var rg0=sh0.getDataRange();
  var sh1=ss.getSheetByName('Target');
  var vals=rg0.getValues();
  var v = []
  sheet.getRange(lastRow, 1).setValue(new Date())
  for(var i=vals.length-1;i>0;i--)
  {
    if(vals[i][0]=='OK'){
      v.push(vals[i])

    }
    {
      sh1.getRange(sh1.getLastRow()+1, 1, v.length; v[0].length).setValues(v)
      sh0.deleteRow(i+1)
    }
  }
} 

失效原因分析 #

1. 语法错误：sh1.getRange()的参数之间误用分号;，正确应为逗号,分隔。
2. 逻辑错误：每次循环都会执行写入和删除操作，不管当前行是否符合OK条件；且每次循环都写入数组v，会导致重复写入数据。
3. 低效操作：循环内依然频繁调用工作表API，没有解决原脚本的性能问题。

优化解决方案 #

核心思路是减少与Google Sheets的交互次数（每一次API调用都有固定开销），所有数据处理在内存中完成后，一次性写入/修改工作表。

优化后脚本 #

function moveRows() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName('Source sheet');
  const targetSheet = ss.getSheetByName('Target');
  
  // 一次性读取源表所有数据
  const sourceRange = sourceSheet.getDataRange();
  const sourceValues = sourceRange.getValues();
  
  // 分离需要移动的行和需要保留的行
  const rowsToMove = [];
  const rowsToKeep = [];
  
  // 跳过表头（对应原脚本的i>0逻辑）
  for (let i = 0; i < sourceValues.length; i++) {
    const row = sourceValues[i];
    if (i === 0 || row[0] !== 'OK') {
      rowsToKeep.push(row);
    } else {
      rowsToMove.push(row);
    }
  }
  
  // 一次性写入目标表
  if (rowsToMove.length > 0) {
    const targetLastRow = targetSheet.getLastRow();
    // 写入移动的行
    targetSheet.getRange(targetLastRow + 1, 1, rowsToMove.length, rowsToMove[0].length).setValues(rowsToMove);
    // 在目标表写入日期（对应原脚本逻辑）
    targetSheet.getRange(targetLastRow + 1, 1).setValue(new Date());
  }
  
  // 一次性更新源表：清空后写入保留的行
  sourceSheet.clearContents();
  if (rowsToKeep.length > 0) {
    sourceSheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
  }
}

优化关键点 #

• 批量数据处理：一次性读取所有数据，在内存中筛选分离行，避免循环内的API调用。
• 批量写入/更新：用一次setValues写入目标表，用清空+写入保留行的方式更新源表，替代循环中的appendRow和deleteRow。
• 避免重复操作：原脚本中重复获取Target表引用，优化后只获取一次。
• 逻辑清晰：分离移动和保留行的逻辑，减少出错概率。

备选方案（批量删除行，保留源表格式） #

如果源表有特殊格式需要保留，不想清空重写，可以用批量删除行的方式：

function moveRowsAlternative() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName('Source sheet');
  const targetSheet = ss.getSheetByName('Target');
  
  const sourceRange = sourceSheet.getDataRange();
  const sourceValues = sourceRange.getValues();
  const rowsToMove = [];
  const rowsToDelete = [];
  
  // 从下往上遍历，收集要删除的行号（工作表行号从1开始）
  for (let i = sourceValues.length - 1; i > 0; i--) {
    if (sourceValues[i][0] === 'OK') {
      rowsToMove.unshift(sourceValues[i]); // unshift保持行顺序与原表一致
      rowsToDelete.push(i + 1); // 转换为工作表实际行号
    }
  }
  
  // 写入目标表
  if (rowsToMove.length > 0) {
    const targetLastRow = targetSheet.getLastRow();
    targetSheet.getRange(targetLastRow + 1, 1, rowsToMove.length, rowsToMove[0].length).setValues(rowsToMove);
    targetSheet.getRange(targetLastRow + 1, 1).setValue(new Date());
  }
  
  // 批量删除行
  rowsToDelete.forEach(rowNum => {
    sourceSheet.deleteRow(rowNum);
  });
}

注：批量删除行的效率略低于清空重写，但能保留源表的格式设置。

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