update column to record if an app script is run for that row or not in google sheets-CodePudding

I am trying to run a google app script with Whatsapp business API to send messages to my customers directly from google sheets. The below app runs fine but every time I run it, it sends the message again and again to all customers irrespective of the same msg being sent to the same customer earlier.

Is there a way, I can add a column and update it automatically to record if the message has been sent to this customer in which case skip to the next (just like in mail merge scripts).

I have the below code and a screenshot of the image here

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"],
      });
  });
};

CodePudding user response：

In your situation, how about modifying your script as follows? Please modify main as follows.

From:

const data = getSheetData_();

To:

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");
}

By this modification, this script checks the column "E" of "Sent". And, the row values without "sent" in column "E" are retrieved as data. And, the value of "sent" is put into column "E" of the retrieved rows.

Reference:

reduce()