Apps Script adds duplicate of last entry into Sheets - Email Parser

Question

I have a script in Apps Script that goes through my emails based on specific subjects, parses through each of those emails, grabbing info and adding it to a Google Sheet. I have the script working fine but when I rerun the script, the last entry in the sheet gets duplicated and added, even though I check for duplications before adding new info into the sheet. The sheet simply has two columns: column A is the message subject, and B is the plain text of the message body. See below for a snippet of the script:

function parse_email(){

 // Open spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

 // Look through emails and pull anything matching subject and from 2023
  var filter = "subject:2023";
  var threads = GmailApp.search(filter);

 // For each message, loop through and pull info
  for(var i = 0; i < threads.length; i ++){
    var messages = threads[i].getMessages();
      var sub = messages[0].getSubject();
      var pmsg = messages[0].getPlainBody();

  // Check if entry exists based on unique value in column 4 (subject/sub), add only new entries

      // Get the current data in the spreadsheet
      var data = sheet.getDataRange().getValues();

      // Create an empty array to store the unique values in the specified column
      var uniqueValues = [];

      // Loop through the data and add each value in the specified column to the array
      // if it doesn't already exist in the array
      for (var i = 0; i < data.length; i++) {
        var value = data[i][1];
        if (uniqueValues.indexOf(value) == -1) {
          uniqueValues.push(value);
        }
      }

      // Check if the subject is in the array of unique values
      if (uniqueValues.indexOf(sub) == -1) {
        // The subject is not in the array, so it is a new entry
        // Add the new entry to the Google Sheets spreadsheet
        sheet.appendRow([sub, pmsg]);
      } else {
        // The subject is already in the array, so it is not a new entry
        // Do not add it to the Google Sheets spreadsheet    
      }
   }
}

I'm new to the world of Javascript and Apps Script, but I have tried creating separate functions for the email parse part and for adding the info to the spreadsheet, but it got a little messy. I'm also aware this code only looks at the first email in the thread, not any replies, this is done on purpose. I already have the code set up to automatically retrigger every night at midnight.

What I would like to happen is the last entry not to be duplicated and added to the spreadsheet, only new entries are added.

Any help is greatly appreciated! Thank you!

Answer 1

It appears that the issue is in this line of code:

var value = data[i][1];

Can you try changing that to:

var value = data[i][0];

Where the 0 replaces the 1. Note that in Javascript (as with most programming languages), array indexes begin at 0, not 1--so when you are trying to pull in the values from column A to match the sub field, you were using a 1 which was actually getting you the data in column B.

This may be a helpful resource if you would like to learn more about JS arrays!