Avoid importing all messages in thread

Question

I've written a script that imports Gmail messages to a spreadsheet. An inbox rule moves it to a "awaiting import" Gmail label, and the script processes the message then removes this label and adds it to the "imported" label.

The problem is that, when an email is received into this "awaiting import" label, the label seems to be applied to the whole thread. As the message has the same subject as previously imported messages, it therefore imports all other messages in the thread (these have already been imported).

Here's my script:

  var importedCount = 0;
  var label = GmailApp.getUserLabelByName('Monitor/AwaitingImport');
  var threads = label.getThreads(0,50);      // An array of threads (limit set to 50, this could be increased)

  if (threads.length != 0) { // If a thread exists
    for (var i = 0; i < threads.length; i++) { // for each thread in the label
      var threadsubject = threads[i].getFirstMessageSubject();      
      if (threadsubject.match(/(Check In)/)) { // if the subject contains "Check In"
        var messages = threads[i].getMessages(); // get ALL the messages
        for (var j = 0; j < messages.length; j++) { // for each message
          var body = messages[j].getPlainBody();
          // Get key:value pairs from email into an array
          var array = body.trim().split("\n");
          var newArray = [];
          for (k = 0; k < array.length; k++) {
            newArray = newArray.concat(array[k].toString().split("="));
          }

          // Push other data to array that isn't in body of email
          newArray.push("GmailMessageID", messages[j].getId());
          newArray.push("MessageDate", messages[j].getDate());

          // Create object
          var newObj = {};
          for (var l =0 ; l< newArray.length ; l++) {
            if (l%2 == 0){
              newObj[newArray[l]] = newArray[l+1];
            }
          }

          // Now insert object as record into the spreadsheet
          insertRecordWithAnyFieldsIntoAnySheet('Incoming Logs', newObj, true);
        }
        // Finally remove the label from this message, mark it as read, and update the importedCount (for logging)
        threads[i].removeLabel(GmailApp.getUserLabelByName('Monitor/AwaitingImport'));
        threads[i].addLabel(GmailApp.getUserLabelByName('Monitor/Imported'));
        importedCount++;
      }
    }    
  }
  SpreadsheetApp.getActiveSpreadsheet().toast(importedCount + ' messages imported');

How can I prevent ALL messages in the thread from being imported, and just import the one that was just received?

I don't want to check the incoming message against messages already imported using the Gmail Message ID, as this would involve a resource-intensive search of thousands of messages already imported into the sheet.
I don't want to import only the last message in the thread, because this potentially misses out other messages that haven't been imported
Finally I tried to import only unread messages, by adding another condition to the script (i.e. if (messages[j].isRead())), on the basis that my script would mark the message as read (not shown in above snippet) but this still seemed to import all messages in the thread

Vytautas Vytautas · Accepted Answer · 2017-12-11T05:19:11

By default Gmail works in threads and GmailApp only works with threads. There is a possible workaround. First you need to go to Gmail settings and set Conversation View to Off. This will make each email be it's separate object. That way the label gets applied to the message (note that the first time you do this, all messages in the thread will have the label, but afterwards each message can have it's own label).

As far as I know GmailApp will still work with threads. You will need to use Gmail API instead. Read up here to see how to start using it. Then what you want to do is something along the lines of this:

queriedMessages =
      Gmail.Users.Messages.list(userInfo.mail, {
        'q': queryString,
        'pageToken': execProperties.nextPageId
      });

This is an example from my script. UserInfo.mail can be replaced with a simple 'me' which will check for your emails. queryString is the important part. That is the same as an advanced search string in Gmail. For example 'in:all newer_than:2d -in:chats -in:trash -in:draft' will search all mail, no older than 2 days, not in Hangouts chat, not in trash and not in draft. Read here on how to write these queries. pageToken is actually found in queriedMessages.nextPageToken. This is only needed if you want to search again and start from let's say page 2 of the search results instead of page 1.

That is useful if you think you will go over 6 minutes. You store pageToken inside of user or script properties, create a trigger to restart the script and kill the current run. Then when it restarts you pull the property and start from the page you left off at.

Avoid importing all messages in thread

1 Answers