请求优化Google Apps Script：缩短代码并仅追加非空行

function verify() {
  const mainFunctionName = "verify";
  const alertFunctionName = "alert4";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const verifySheet = ss.getSheetByName("VERIFY");
  const drawings = verifySheet.getDrawings();
  const targetDrawing = drawings.filter(d => d.getOnAction() === mainFunctionName)[0];
  
  if (targetDrawing) {
    targetDrawing.setOnAction(alertFunctionName);
    SpreadsheetApp.flush();
    vf();
    targetDrawing.setOnAction(mainFunctionName);
  }
}

function alert4() {
  SpreadsheetApp.getUi().alert("NOW SAVING");
}

function vf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const verifySheet = ss.getSheetByName("VERIFY");
  const index5Sheet = ss.getSheetByName("INDEX5");
  const index6Sheet = ss.getSheetByName("INDEX6");
  
  // 定义需要处理的行范围和目标列（对应原pfv的列映射）
  const rowStart = 15;
  const rowEnd = 39;
  const targetColumns = [1, 12, 2, 3, 4, 5, 6, 7, 8, 9, 10, 14]; // A(1), L(12), B-J(2-10), N(14)
  
  // 批量读取VERIFY表15-39行的所有数据
  const dataRange = verifySheet.getRange(rowStart, 1, rowEnd - rowStart + 1, 14);
  const allRows = dataRange.getValues();
  
  // 过滤非空行并提取目标列数据
  const rowsToAppend = allRows.filter(row => row[0] !== "") // 以A列是否为空判断是否为有效行
    .map(row => targetColumns.map(colIndex => row[colIndex - 1]));
  
  // 批量追加到INDEX5
  if (rowsToAppend.length > 0) {
    index5Sheet.getRange(index5Sheet.getLastRow() + 1, 1, rowsToAppend.length, rowsToAppend[0].length)
      .setValues(rowsToAppend);
  }
  
  // 批量读取并追加INDEX6需要的数据
  const index6CellList = ["B10","B11","B1","B2","B3","B4","B5","D6","D7","D8","D9","E9","F9","B12"];
  const index6Values = verifySheet.getRangeList(index6CellList).getRanges()
    .map(range => range.getValue());
  index6Sheet.appendRow(index6Values);
  
  // 批量清空指定单元格
  const clearCellList = ["B1","C6","C7","C8","C9", ...["M15","M16","M17","M18","M19","M20","M21","M22","M23","M24","M25","M26","M27","M28","M29","M30","M31","M32","M33","M34","M35","M36","M37","M38","M39"]];
  verifySheet.getRangeList(clearCellList).clearContent();
}

关键优化说明 #

• 批量读写数据：一次性读取目标区域的所有数据，替代原脚本数百次单个单元格读取；批量写入所有非空行到INDEX5，替代24次appendRow调用，大幅减少与Google Sheets服务的交互次数，提升执行速度。
• 过滤空行：通过判断A列是否为空自动跳过无数据行，避免无效处理。
• 移除冗余延迟：删除原脚本中Utilities.sleep(10000)，直接减少10秒执行时间。
• 简化重复逻辑：用循环和数组映射替代硬编码的24组重复行处理代码，代码更简洁易维护。
• 批量清空内容：使用getRangeList一次性处理所有需要清空的单元格，减少服务调用次数。

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