How to get the body of individual emails based on

2019-08-01 03:28发布

问题:

Every day I get 0-20 emails that look basically like this:

Name: FirstName LastName

Phone Number: 555-867-5309

Email Address: Fake@email.com

Location: NameOfPreferedBranch

Request: ThingPersonWants

I'm attempting to get the body of these emails sent to different Google Sheets based on the location listed. So if the person filling out the form says that Location1 works for them I'd like the message body sent to Location1's Sheet. If the person says Location2 then the message body should go to location2's sheet, and so on. Every time one of these emails comes in I have Gmail apply a different label based on the location. Ideally I'd like to set up a trigger that runs every 15 minutes and does't duplicate results that have already been run through the process.

I've found some code on other questions similar to this that gets me frustrating close, but I can't seem to get this quite right. Here is what I'm working with currently:

    function myFunction() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('NameOfSheet');

      var label = GmailApp.getUserLabelByName("MyLabel");
      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++)
        {
          if (messages[j].isUnread())
              {
                var msg = messages[j].getBody();      
                sheet.appendRow([msg]);
                messages[j].markRead();
              }
        }
          threads[i].removeLabel(label);
      }
    }

I was hoping the combination of getting the threads by the label and the

    if (messages[j].isUnread())

section would get me just the messages that had the label and hadn't been read, but it's still returning all the messages in the entire thread. Additionally it's marking all the messages in the thread as read which means that if Location1 and Location2 both have a message in the thread when the script runs for Location1's sheet it marks all of the messages as read, and when the script runs for Location2's sheet it doesn't pull anything because all the messages are already marked as read.

As is probably pretty obvious by now I'm really new with Google Apps Scripts and coding in general. If you could use short words and painfully detailed explanations in you answers it would help out a lot.

Thanks.

Edit: I tried to use GmailApp.search() but am still getting the same results. It pulls the body from every email in the thread and marks them all as Unread. This is what the code looks like at present:

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('NameOfSheet');

  var label = GmailApp.getUserLabelByName("Location1Label");
  var threads = GmailApp.search('label:"Location1Label" is:Unread');

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

    for (var j=0; j<messages.length; j++)
    {
      if (messages[j].isUnread())
          {
            var msg = messages[j].getBody();      
            sheet.appendRow([msg]);
            messages[j].markRead();
          }
    }
      threads[i].removeLabel(Location1Label);
  }
}

回答1:

Requirement:

Find all emails inside a label that are unread and add to sheet.


Solution:

Use a query passed to GmailApp.search() to find all of the unread messages inside a label.

Here's the script:

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('NameOfSheet');

  var label = GmailApp.getUserLabelByName("Location1Label");
  var threads = GmailApp.search('label:"Location1Label" is:Unread');

  for (var i=0; i<threads.length; i++) 
  {
    var messages = GmailApp.getMessagesForThread(threads[i]);
    for (var j=0; j<messages.length; j++) 
    {
      if (messages[j].isUnread())
      {
        var msg = messages[j].getBody();      
        sheet.appendRow([msg]);
        messages[j].markRead();
      }
    }
    threads[i].removeLabel(label);
  }
}

Explanation:

Now we're using GmailApp.search() to find all of the emails, then passing looping through the result array and passing each object to GmailApp.getMessagesForThread().

The rest of the code is unchanged except for your removeLabel() as it wasn't looking at the variable you defined earlier in the script.


References:

  1. .search()
  2. .getMessagesForThread()