Google Sheets使用Apps Script循环遍历日期删除客户重复到访记录

核心问题排查 #

现有代码的错误主要集中在3处：

• 日期取值逻辑完全错误：你把importSheet.getRange(r,36).getTime()整段当成字符串传给Date()，且没有加new关键字调用Date构造函数，此时Date()只会返回当前系统时间的字符串，自然拿不到表格里的记录时间
• 逐单元格读写的方式处理12000行数据会触发Google Sheets的调用限额，运行速度极慢甚至直接报错
• 重复日期判断逻辑有漏洞：仅用else if判断最多只能删除1个重复项，若同一条记录存在3个及以上相同到访日期，会有漏删的情况；最后getRange(beBackRange.setValue("No"))的写法也不符合语法规范

优化后可直接运行的代码 #

function cleanUpBebacks() {
  const myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = myGoogleSheet.getSheetByName('Import from Hubspot');
  const lastRow = importSheet.getLastRow();
  // 批量读取第36到42列的所有数据，一次性操作减少接口调用
  const allData = importSheet.getRange(2, 36, lastRow - 1, 7).getValues();

  // 遍历所有行
  allData.forEach(row => {
    // 取出5个到访日期，只保留日期部分（忽略时分秒），为空的话记为唯一标识避免误判
    const dateList = row.slice(0,5).map(date => {
      if (!date) return Symbol('empty');
      const d = new Date(date);
      // 只对比年月日，避免同一天不同时间被判定为不同
      return new Date(d.getFullYear(), d.getMonth(), d.getDate()).getTime();
    });
    // 去重：从后往前遍历，重复的日期设为空
    for (let i = dateList.length - 1; i > 0; i--) {
      if (dateList[i] === dateList[i-1]) {
        row[i] = '';
      }
    }
    // 处理beBack字段：第二个到访日期为空时设为No
    if (!row[1]) {
      row[6] = 'No';
    }
  });

  // 批量写回所有数据
  importSheet.getRange(2, 36, lastRow - 1, 7).setValues(allData);
}

优化说明 #

• 批量读写：一次性把所有需要处理的数据读入内存操作，处理完后统一写回，12000条数据仅需2次接口调用，运行效率提升几十倍
• 日期判断修复：先把表格内的日期转成标准Date对象，取年月日的时间戳进行对比，避免同一天不同时分秒被判定为不同日期的问题
• 去重逻辑优化：从后往前遍历日期列表，所有相邻重复的日期都会被清空，不会出现漏删
• 语法错误修复：修正了beBack字段的赋值逻辑，符合Apps Script的语法规范

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