How do I get script to get emails after ones already imported? (ignore emails already called by script)

0
votes

Problem: Every time I run the script, the same previous emails are loaded into the spreadsheet. Given that I need to run this script weekly, I would like to ignore emails already imported from prior runs.

I believe an edit needs to be made in the for loop or perhaps add an if statement before the append

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

  var label = GmailApp.getUserLabelByName("Test Script");
  var threads = label.getThreads();

  for (var i=0; i<threads.length; i++)
    {
    var messages = threads[i].getMessages();

    for (var j=0; j<messages.length; j++)
    {
  var msg = messages[j].getPlainBody();
  var sub = messages[j].getSubject();
  var dat = messages[j].getDate();

  ss.appendRow([msg, sub, dat])
    }

    }

Output is currently: Email 1 Email 2 Email 3 Email 1 Email 2 Email 3 Email 4 Email 1 Email 2 Email 3 Email 4 Email 5

But I would like it to just add new emails coming in: Email 1 Email 2 Email 3 +Email 4 +Email 5

1
google-apps-scriptgoogle-sheets
Either have your script check if the emails already exist in your spreadsheet, or use some way to filter the emails you are processing (e.g. adding/removing a tag) – sinaraheneba
You might also find some value in using GmailApp.search() – Cooper

1 Answers

0
votes

An easy solution would be to retrieve the message IDs and check if a message with given ID is already imported into the sheet.

This is how you can do it:

function myFunction(){
 var messageArray=[];
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Sheet1");
 var values=sheet.getDataRange().getValues();
 for (var k=0; k<values.length; k++){
   messageArray.push(values[k][0])  
  }
 var label = GmailApp.getUserLabelByName("testlabel");
 var threads = label.getThreads();
 for (var i=0; i<threads.length; i++){
   var messages = threads[i].getMessages();
   for (var j=0; j<messages.length; j++) {
    var msg = messages[j].getPlainBody();
    var sub = messages[j].getSubject();
    var dat = messages[j].getDate();
    var id= messages[j].getId()
    if(messageArray.indexOf(id)==(-1)){
     ss.appendRow([id, msg, sub, dat])
     }
   }
  }
}