How can I prevent duplicate Emails from being posted into the Spreadsheet

Question

function grabDover() {
  const dover = GmailApp.search('has:attachment label:"1st Choice Auto Dover Scans"');
  const doverSheet = ss.getSheetByName('1st Choice Auto Dover Scans');
  dover.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const messageid = message.getId();
      const attachments = message.getAttachments();
      const date = message.getDate();
      const subject = message.getSubject();
      const url = 'https://mail.google.com/mail/u/0/#label/' + messageid;
      attachments.forEach(attachment => {
        Logger.log(date);
        const array = [attachment.getName(), date, url, messageid];
        doverSheet.appendRow(array);
      });
    });
  });
}

I have this script that pulls email attachments from Gmail, based on label names and soon to be a time trigger, and I can not figure out how to prevent duplicate email attachments from being pulled.

I am trying to have the script, cross check the messageIds that are being pulled from Gmail into the sheet, so no duplicate emails are being posted into the sheet. I have the messageid in a array already. I have tried doing it with a scriptproperties method and it did not work or I was doing it wrong.

I can get it to work via google spread sheet formula but It wont work for my exact use case.

Answer 1

I believe your goal is as follows.

• You want to put the values from const dover = GmailApp.search('has:attachment label:"1st Choice Auto Dover Scans"');.
• When you retrieve the values from dover, you want to check the message IDs and want to put only the values when the message IDs are not found.

Modification points:

• In your script, appendRow is used in a loop. In this case, the process cost becomes high. Ref (Author: me)

• Message IDs are not checked.

When these points are reflected in your script, how about the following modification?

Modified script:

function grabDover() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dover = GmailApp.search('has:attachment label:"1st Choice Auto Dover Scans"');
  const doverSheet = ss.getSheetByName('1st Choice Auto Dover Scans');
  const lastRow = doverSheet.getLastRow();
  const currentMessageIds = [...new Set(doverSheet.getDataRange().getValues().map(([, , , d]) => d))];
  const values = dover.reduce((ar, thread) => {
    thread.getMessages().forEach(message => {
      const messageid = message.getId();
      if (!currentMessageIds.includes(messageid)) {
        const attachments = message.getAttachments();
        const date = message.getDate();
        Logger.log(date);
        const url = 'https://mail.google.com/mail/u/0/#label/' + messageid;
        ar = [...ar, ...attachments.map(attachment => [attachment.getName(), date, url, messageid])];
      }
    });
    return ar;
  }, []);
  if (values.length == 0) return;
  doverSheet.getRange(lastRow + 1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, when the message IDs in dover are not found in the current sheet "1st Choice Auto Dover Scans", the values are put into the sheet.

Note:

• In this modified script, it supposes that from your script, the message IDs are put into column "D". Please be careful about this.

References:

• reduce()
• forEach()