Google Sheets脚本优化：将逾期60天的行从Pending移至Closed

需求说明 #

当「Pending」工作表F列的投标截止日期距离当前日期超过60天时，将该行数据移至「Closed」工作表。

原代码问题分析 #

你的现有脚本存在以下几个不符合需求或可优化的点：

• 错误读取了A列（第1列）的日期，而需求是检查F列（第6列）的投标截止日期
• 判断逻辑仅检查日期早于今天，未实现「超过60天」的核心需求
• 循环从最后一行到第61行，直接跳过前60行数据，逻辑不合理
• 逐行读取、写入单元格，频繁调用API，效率低且易触发Google配额限制

优化后的脚本 #

function transferExpiredBids() {
  // 获取当前表格实例
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pendingSheet = ss.getSheetByName("Pending");
  const closedSheet = ss.getSheetByName("Closed");
  
  if (!pendingSheet || !closedSheet) {
    SpreadsheetApp.getUi().alert("找不到指定工作表，请检查工作表名称是否正确");
    return;
  }
  
  // 一次性读取所有数据（默认第1行是表头，从第2行开始处理）
  const allData = pendingSheet.getDataRange().getValues();
  const headerRow = allData[0];
  const dataRows = allData.slice(1);
  
  const today = new Date();
  // 计算60天前的时间戳（毫秒），避免日期格式差异问题
  const sixtyDaysAgo = today.getTime() - 60 * 24 * 60 * 60 * 1000;
  
  const rowsToKeep = [];
  const rowsToTransfer = [];
  
  // 遍历数据行，筛选需要保留和转移的内容
  dataRows.forEach(row => {
    const deadline = new Date(row[5]); // F列对应数组索引5（从0开始计数）
    // 验证日期有效性，且截止日期早于60天前
    if (!isNaN(deadline.getTime()) && deadline.getTime() < sixtyDaysAgo) {
      rowsToTransfer.push(row);
    } else {
      rowsToKeep.push(row);
    }
  });
  
  // 批量写入转移的数据到Closed表
  if (rowsToTransfer.length > 0) {
    // 若Closed表为空，先写入表头
    if (closedSheet.getLastRow() === 0) {
      closedSheet.appendRow(headerRow);
    }
    closedSheet.getRange(closedSheet.getLastRow() + 1, 1, rowsToTransfer.length, rowsToTransfer[0].length)
      .setValues(rowsToTransfer);
  }
  
  // 重置Pending表：保留表头，写入需要留存的行
  pendingSheet.clearContents();
  pendingSheet.appendRow(headerRow);
  if (rowsToKeep.length > 0) {
    pendingSheet.getRange(2, 1, rowsToKeep.length, rowsToKeep[0].length)
      .setValues(rowsToKeep);
  }
}

优化点说明 #

• 批量操作：一次性读取所有数据、批量写入/删除，大幅减少API调用次数，提升效率并规避配额风险
• 正确列定位：使用索引5读取F列的投标截止日期，匹配需求
• 精准日期判断：通过时间戳计算60天前的节点，避免不同日期格式导致的判断误差
• 容错处理：增加工作表存在性检查，避免因名称错误导致脚本崩溃
• 表头兼容：默认保留第1行作为表头，若你的表头行数不同，可调整slice(1)的参数（比如表头是2行就用slice(2)）

内容的提问来源于stack exchange，提问作者Logan.Oakley