Google Apps Script：接收请求添加行后无法自动发送出站Webhook

你的代码无法自动发送Webhook的核心原因是sendWebhook函数中数组索引越界：newRowData仅包含8个元素（索引0-7），但你在构造payload时使用了索引9-13，导致这些utm参数值为undefined，多数Webhook服务器会拒绝这类无效请求。此外，代码缺乏错误处理机制，请求失败时无法捕获异常并排查问题。

以下是修改后的完整代码：

const SPREADSHEET_ID = "ID";
const SHEET_NAME = "Sheet1";
const LOGS_SHEET_NAME = "Logs";
const WEBHOOK_URL = "URL";

function doGet(e) {
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
    logToSheet("Incoming request: " + JSON.stringify(e.parameter));

    const newRowData = [
      e.parameter.date,
      e.parameter.user_id,
      e.parameter._ga,
      e.parameter.utm_source,
      e.parameter.utm_medium,
      e.parameter.utm_campaign,
      e.parameter.utm_term,
      e.parameter.utm_content,
    ];

    sheet.appendRow(newRowData);
    logToSheet("New row added: " + JSON.stringify(newRowData));

    // 调用Webhook发送函数，包裹在try-catch中避免影响主流程
    const webhookResponse = sendWebhook(WEBHOOK_URL, newRowData);
    logToSheet("Webhook sent successfully: " + webhookResponse.getContentText());

    return ContentService.createTextOutput("Data successfully added and webhook sent.");
  } catch (error) {
    logToSheet("Error occurred: " + error.message);
    return ContentService.createTextOutput("Failed to process request: " + error.message).setStatusCode(500);
  }
}

function sendWebhook(url, newRowData) {
  // 修正数组索引，对应newRowData的正确位置（索引0-7）
  const payload = {
    date: newRowData[0],
    user_id: newRowData[1],
    _ga: newRowData[2],
    utm_source: newRowData[3],
    utm_medium: newRowData[4],
    utm_campaign: newRowData[5],
    utm_term: newRowData[6],
    utm_content: newRowData[7],
  };

  const options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true, // 允许捕获HTTP错误而非直接抛出异常
  };

  logToSheet("Sending webhook to URL: " + url);
  logToSheet("Webhook payload: " + JSON.stringify(payload));

  const response = UrlFetchApp.fetch(url, options);
  logToSheet("Webhook server response status: " + response.getResponseCode());
  logToSheet("Webhook server response body: " + response.getContentText());

  return response;
}

function testSendWebhook() {
  const newRowData = [
    "2023-04-04",
    "GA1.2.123456789.987654321",
    "test_ga_id",
    "test_source",
    "test_medium",
    "test_campaign",
    "test_term",
    "test_content",
  ];

  sendWebhook(WEBHOOK_URL, newRowData);
}

function logToSheet(logMessage) {
  const logSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(LOGS_SHEET_NAME);
  const timestamp = new Date();
  logSheet.appendRow([timestamp, logMessage]);
}

关键修改说明 #

• 修正数组索引：将payload中utm参数的索引从9-13改为3-7，匹配newRowData的实际元素位置，确保参数值正确传递
• 全局错误捕获：在doGet函数外层添加try-catch块，捕获整个流程中的异常并返回500状态码，避免请求无响应
• Webhook请求优化：添加muteHttpExceptions: true，允许捕获HTTP错误（如4xx/5xx状态码）并记录详细响应，方便排查服务器端问题
• 日志增强：新增Webhook响应状态码日志，更直观地判断请求是否成功
• 测试数据修复：补充testSendWebhook中的_ga字段，避免测试时出现索引错位问题

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