如何用Google Apps Script计算Google Sheets托盘出入库时间差并去重

核心优化思路 #

• 放弃逐行读写表格的操作，全量一次性读取数据到内存处理，完成后一次性写回，减少Sheets API调用次数，性能可以提升数十倍
• 时间计算统一转换为毫秒时间戳处理，避免日期格式转换、字符串对比的额外开销，30秒阈值直接用30*1000的数值对比即可
• 遍历过程中直接生成要保留的结果数组，不要执行逐行删除操作，最后批量覆盖原表数据即可

具体脚本实现（Google Apps Script） #

function processPalletData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const lastCol = data[0].length;
  const result = [];
  // 30秒对应的毫秒数
  const THRESHOLD = 30 * 1000;

  for (let i = 1; i < data.length; i++) { // 跳过表头行，从第二行开始
    const currRow = data[i];
    // 第一行数据直接加入结果
    if (result.length === 0) {
      result.push([...currRow]);
      continue;
    }

    const prevRow = result[result.length - 1];
    // 校验同托盘同Type的场景
    if (currRow[0] === prevRow[0] && currRow[3] === prevRow[3]) {
      // 计算两行的完整时间戳
      const prevTime = new Date(prevRow[1] + prevRow[2]).getTime();
      const currTime = new Date(currRow[1] + currRow[2]).getTime();
      // 时间差小于30秒，跳过当前行（不加入结果，相当于删除）
      if (currTime - prevTime < THRESHOLD) {
        continue;
      }
    }

    // 校验E（入库）和S（出库）相邻的场景
    if (prevRow[3] === "E" && currRow[3] === "S" && currRow[0] === prevRow[0]) {
      // 回填E行的对应列
      prevRow[lastCol - 3] = currRow[4];
      prevRow[lastCol - 2] = currRow[2];
      // 回填S行的对应列
      currRow[lastCol - 3] = prevRow[4];
      currRow[lastCol - 2] = prevRow[2];
      // 计算时长，转换回以天为单位的Sheets时间格式
      const diffTime = (new Date(currRow[1] + currRow[2]).getTime() - new Date(prevRow[1] + prevRow[2]).getTime()) / (1000 * 3600 * 24);
      currRow[lastCol - 1] = diffTime;
    }

    result.push([...currRow]);
  }

  // 清空原表，写入处理后的结果
  sheet.clearContents();
  sheet.getRange(1, 1, result.length, result[0].length).setValues(result);
  // 设置时长列格式为[h]:mm:ss，支持超过24小时的时长显示
  sheet.getRange(1, lastCol, result.length, 1).setNumberFormat("[h]:mm:ss");
}

无代码公式方案（适合不想写脚本的场景） #

1. 标记待删除行：新增辅助列，第二行输入公式=IF(AND(A2=A1,D2=D1,(B2+C2)-(B1+C1)<30/86400),"删除","")，下拉填充后筛选所有标记为「删除」的行批量删除即可
2. 回填E/S对应信息：新增对应列，用XLOOKUP匹配同托盘号的对应E/S行数据，时长计算直接用对应S行时间减E行时间，设置单元格格式为[h]:mm:ss即可显示超过24小时的时长

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