HubSpot API推送数据至表格时Google Apps Script触发失效求助

我通过HubSpot CRM每日向谷歌表格推送数据，规则是：A列填GCLID，C列填UNIX时间，B、D、E列留空。我写了Google Apps Script来处理这些数据：

• A列保持不变
• B列填充固定文本 Definitivo - GADS - Conversões Offline - Match all
• C列用EPOCHTODATE函数把UNIX时间转成常规日期（转为静态值）
• D列填充数值2100
• E列填充文本BRL

脚本在手动编辑表格（比如添加新行）时运行正常，但HubSpot通过API推送数据时完全没反应。我试过onChange触发器和定时触发器：

• onChange没生效
• 定时触发器会修改所有行，但我只需要处理HubSpot推送的新数据行

当前使用的脚本：

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var startRow = range.getRow();
  var endRow = startRow + range.getNumRows() - 1;

  for (var row = startRow; row <= endRow; row++) {
    // Coluna B
    sheet.getRange(row, 2).setValue('Definitivo - GADS - Conversões Offline - Match all');

    // Coluna C (Converter valor Unix para data usando a fórmula EPOCHTODATE)
    var cell = sheet.getRange(row, 3);
    var cellValue = cell.getValue();
    if (!isNaN(cellValue) && cell.getFormula() === '') {
      // Adicionar a fórmula temporariamente
      cell.setFormula('=EPOCHTODATE(' + cellValue + ', 2)');
     
      // Aguardar um momento para a fórmula calcular o valor
      Utilities.sleep(500);
     
      // Converter o valor calculado para um valor estático
      var calculatedValue = cell.getValue();
      cell.setValue(calculatedValue);
    }

    // Coluna D
    sheet.getRange(row, 4).setValue(2100);

    // Coluna E
    sheet.getRange(row, 5).setValue('BRL');
  }
}

解决方案1：使用可安装的onChange触发器（推荐） #

默认的简单触发器（onEdit、onChange）无法捕获API带来的修改，必须创建可安装的onChange触发器，步骤如下：

1. 打开谷歌表格的脚本编辑器
2. 点击左侧「触发器」图标（时钟形状）
3. 点击「添加触发器」
4. 配置参数：
   • 选择要运行的函数：processNewRows
   • 选择部署类型：「Head」
   • 选择事件源：「从电子表格」
   • 选择事件类型：「更改」
5. 保存并授权所需权限

修改后的脚本逻辑（只处理新添加的行）：

function processNewRows(e) {
  // 只处理插入行或批量更新的变更事件
  if (e.changeType !== 'INSERT_ROW' && e.changeType !== 'OTHER') return;

  var sheet = e.source.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var startRow = 1;

  // 找到最后一行已处理的行（通过检查B列是否有值）
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i][1]) { // B列索引为1（数组从0开始）
      startRow = i + 2; // 下一行是新行的起始
      break;
    }
  }

  // 没有新行则直接退出
  if (startRow > values.length) return;

  // 处理从startRow到最后一行的新数据
  for (var row = startRow; row <= values.length; row++) {
    // 填充B列
    sheet.getRange(row, 2).setValue('Definitivo - GADS - Conversões Offline - Match all');

    // 转换C列UNIX时间为日期（直接计算，避免公式延迟）
    var cell = sheet.getRange(row, 3);
    var cellValue = cell.getValue();
    if (!isNaN(cellValue) && cell.getFormula() === '') {
      var date = new Date(cellValue * 1000); // UNIX时间戳是秒，转成毫秒计算日期
      cell.setValue(date);
    }

    // 填充D列
    sheet.getRange(row, 4).setValue(2100);

    // 填充E列
    sheet.getRange(row, 5).setValue('BRL');
  }
}

• 替换了原脚本中依赖EPOCHTODATE公式的逻辑，直接用JavaScript计算日期，避免Utilities.sleep()的不稳定延迟
• 通过检查B列是否有值识别未处理的新行，确保只处理HubSpot推送的最新数据

解决方案2：优化定时触发器（备选） #

如果可安装触发器仍有问题，可优化定时触发器的逻辑，只处理未处理的行：

function processUnprocessedRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  for (var row = 0; row < values.length; row++) {
    // 只处理A列有GCLID但B列空的未处理行
    if (!values[row][1] && values[row][0]) {
      var actualRow = row + 1; // 表格行号从1开始

      sheet.getRange(actualRow, 2).setValue('Definitivo - GADS - Conversões Offline - Match all');
      
      var cell = sheet.getRange(actualRow, 3);
      var cellValue = cell.getValue();
      if (!isNaN(cellValue)) {
        var date = new Date(cellValue * 1000);
        cell.setValue(date);
      }

      sheet.getRange(actualRow, 4).setValue(2100);
      sheet.getRange(actualRow, 5).setValue('BRL');
    }
  }
}

创建定时触发器时，设置合适的间隔（比如每15分钟一次），既不会频繁运行，也能及时处理新数据。

内容的提问来源于stack exchange，提问作者Rafael Guimares de Assis Motta