更新列记录如果一个应用程序脚本运行的行或不在谷歌表

Question

我试图运行与Whatsapp业务API的谷歌应用程序脚本，直接从谷歌表发送消息给我的客户。下面的应用程序运行良好，但每次我运行它时，它都会一次又一次地向所有客户发送消息，而不管之前向同一客户发送了相同的消息。

是否有一种方法，我可以添加一列并自动更新它以记录消息是否已发送给该客户，在这种情况下跳到下一个(就像在邮件合并脚本)。

我有下面的代码和这里的图像的屏幕截图

const WHATSAPP_ACCESS_TOKEN = "**My whatsapp token**";
const WHATSAPP_TEMPLATE_NAME = "**My template name**";
const LANGUAGE_CODE = "en";

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 { error } = JSON.parse(request);
const status = error ? `Error: ${JSON.stringify(error)}` : `Message sent to ${recipient_number}`;
Logger.log(status);
};
const getSheetData_ = () => {
const [header, ...rows] = SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
const data = []; 
rows.forEach((row) => {
const recipient = { };
header.forEach((title, column) => {
recipient[title] = row[column];
});
data.push(recipient);
}); 
return data;
};

const main = () => {
const data = getSheetData_();
data.forEach((recipient) => {
const status = sendMessage_({
recipient_number: recipient["Phone Number"].replace(/[^d]/g, ""),
customer_name: recipient["Customer Name"],
item_name: recipient["Item Name"],
delivery_date: recipient["Delivery Date"],
});
});
};

Answer 1

在您的情况下，如何修改您的脚本如下?请将main修改如下

:

const data = getSheetData_();

:

const temp = getSheetData_();
const { data, ranges } = temp.reduce((o, e, i) => {
if (e["Sent"] != "sent") {
o.data.push(e);
o.ranges.push(`E${i + 2}`);
}
return o;
}, { data: [], ranges: [] });
if (ranges.length > 0) {
SpreadsheetApp.getActiveSheet().getRangeList(ranges).setValue("sent");
}

• 通过此修改，该脚本检查列";E"的"Sent"并且，没有"sent"的行值。in column "E"检索为data。并且，"sent"的值列";E">
参考:

• reduce ()