How to get the body of individual emails based on a label from gmail to google sheets

Question

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

Name: FirstName LastName

Phone Number: 555-867-5309

Email Address: [email protected]

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);
  }
}

Did you try the suggestion in your last question of using GmailApp.search() ? — ross
I tried. I would either get a error message that said 'Missing ) after argument list', and when I added one it would say the same thing, or if I got it to run it didn't appear to do anything. Obviously I'm doing something wrong, but I haven't been able to track down what it is...mostly because when I find answers from searching I can't understand what they're explaining because I don't know enough. — mschawacker
Could you add the example of GmailApp.search() to your question so that we can troubleshoot it? — ross
The version that I thought was going to get me there was var messages = GmailApp.search('label:"Location1Label" && is:Unread'); but when I run that nothing appears to happen. If I leave out the ' ' I get the error message "Missing ) after argument list' — mschawacker
Your issue is because you're using && in the search string. You should be building this like you would in the search bar at the top of your Gmail. var messages = GmailApp.search('label:"Location1Label" is:unread'); — ross

ross ross · Accepted Answer · 2019-06-21T14:51:30

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.

How to get the body of individual emails based on a label from gmail to google sheets

1 Answers

Requirement:

Solution:

Explanation:

References: