如何在Google Sheets中新增列记录App Script执行状态以避免重复发WhatsApp消息

核心思路是新增发送状态列，记录每条消息的发送结果，后续执行脚本时跳过已标记为“已发送”的行。

步骤1：在表格中新增状态列 #

在你的Google Sheets表格末尾新增一列，表头设为发送状态，用于标记消息是否已发送。

步骤2：修改代码实现状态判断与更新 #

以下是修改后的完整代码，关键改动包括：

• 保留行索引，方便更新对应行的发送状态
• 新增发送状态判断逻辑，跳过已发送的记录
• 发送完成后更新表格中的状态标记

const WHATSAPP_ACCESS_TOKEN = "**My whatsapp token**";
const WHATSAPP_TEMPLATE_NAME = "**My template name**";
const LANGUAGE_CODE = "en";
const STATUS_COLUMN_HEADER = "发送状态"; // 对应新增的表头
const SENT_STATUS = "已发送";
const FAILED_STATUS = "发送失败";

// 修改sendMessage_函数，返回发送是否成功
const sendMessage_ = ({
  recipient_number,
  customer_name,
  item_name,
  delivery_date,
}) => {
  const apiUrl = "**My api url**";    
  const request = UrlFetchApp.fetch(apiUrl, {
    muteHttpExceptions: true,
    method: "POST",
    headers: {
      Authorization: `Bearer ${WHATSAPP_ACCESS_TOKEN}`,
      "Content-Type": "application/json",
    },
    payload: JSON.stringify({
      messaging_product: "whatsapp",
      type: "template",
      to: recipient_number,
      template: {
        name: WHATSAPP_TEMPLATE_NAME,
        language: { code: LANGUAGE_CODE },
        components: [
          {
            type: "body",
            parameters: [
              {
                type: "text",
                text: customer_name,
              },
              {
                type: "text",
                text: item_name,
              },
              {
                type: "text",
                text: delivery_date,
              },
            ],
          },
        ],
      },
    }),
  });

  const response = JSON.parse(request);
  if (response.error) {
    const errorMsg = `Error sending to ${recipient_number}: ${JSON.stringify(response.error)}`;
    Logger.log(errorMsg);
    return false;
  } else {
    Logger.log(`Message sent to ${recipient_number}`);
    return true;
  }
};

// 修改getSheetData_函数，返回包含行号的完整数据
const getSheetData_ = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [header, ...rows] = sheet.getDataRange().getDisplayValues();
  const statusColumnIndex = header.indexOf(STATUS_COLUMN_HEADER);
  const data = []; 

  rows.forEach((row, rowIndex) => {
    const recipient = { rowNumber: rowIndex + 2 }; // 行号从2开始（跳过表头）
    header.forEach((title, column) => {
      recipient[title] = row[column];
    });
    recipient.statusColumnIndex = statusColumnIndex;
    data.push(recipient);
  }); 
  return data;
};

// 修改main函数，加入状态判断与更新逻辑
const main = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = getSheetData_();
  
  data.forEach((recipient) => {
    // 跳过已发送的记录
    if (recipient[STATUS_COLUMN_HEADER] === SENT_STATUS) {
      Logger.log(`Skipping ${recipient["Phone Number"]} - already sent`);
      return;
    }

    // 发送消息
    const sendSuccess = sendMessage_({
      recipient_number: recipient["Phone Number"].replace(/[^\d]/g, ""),
      customer_name: recipient["Customer Name"],
      item_name: recipient["Item Name"],
      delivery_date: recipient["Delivery Date"],
    });

    // 更新发送状态到表格
    if (recipient.statusColumnIndex !== -1) {
      const status = sendSuccess ? SENT_STATUS : FAILED_STATUS;
      sheet.getRange(recipient.rowNumber, recipient.statusColumnIndex + 1).setValue(status);
    }
  });
};

使用说明 #

1. 确保表格中已添加发送状态列
2. 运行main函数，脚本会自动跳过已标记为“已发送”的行，仅向未发送的客户发送消息
3. 发送完成后，对应行会自动标记“已发送”或“发送失败”，后续执行不会重复发送

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