如何根据从gmail到Google表格的标签获取单个电子邮件的正文 [英] How to get the body of individual emails based on a label from gmail to google sheets

问题描述

每天我都会收到0-20封电子邮件，基本上是这样的:

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

名称:名字姓氏

Name: FirstName LastName

电话号码:555-867-5309

Phone Number: 555-867-5309

电子邮件地址:[e-mail]

Email Address: [e-mail]

位置:NameOfPreferedBranch

Location: NameOfPreferedBranch

请求:ThingPersonWants

Request: ThingPersonWants

我正在尝试根据列出的位置将这些电子邮件的正文发送到不同的Google表格.因此，如果填写该表格的人说Location1适用于他们，那么我希望将邮件正文发送到Location1的表格中. 如果此人说的是Location2，则邮件正文应转到location2的工作表，依此类推.每次收到一封电子邮件时，我都会让Gmail根据位置应用不同的标签.理想情况下，我想设置一个触发器，该触发器每15分钟运行一次，并且不会重复该过程中已经运行过的结果.

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

我希望结合使用标签和

    if (messages[j].isUnread())

部分可以让我得到带有标签并且未被读取的消息，但是它仍然返回整个线程中的所有消息.此外，它会将线程中的所有消息标记为已读，这意味着，如果脚本在Location1的工作表中运行，则当Location1和Location2在线程中都有消息时，会将所有消息都标记为已读，而脚本在Location2的工作表中运行时，它将被标记为已读.不会拉任何东西，因为所有消息都已标记为已读.

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.

到目前为止，很明显我是Google Apps脚本和编码方面的新手.如果您可以在回答中使用简短的单词和详尽的解释，那将大有帮助.

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.

谢谢.

我尝试使用GmailApp.search()，但仍得到相同的结果.它从线程中的每封电子邮件中提取正文并将其全部标记为未读".这是当前代码的样子:

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

推荐答案

要求:

在标签内查找所有未读的电子邮件并将其添加到工作表中.

Requirement:

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

使用传递给GmailApp.search()的查询来查找标签内的所有未读邮件.

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

这是脚本:

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

---

说明:

现在，我们使用GmailApp.search()查找所有电子邮件，然后循环遍历结果数组并将每个对象传递给GmailApp.getMessagesForThread().

---

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().

除了removeLabel()之外，其余代码均未更改，因为它没有查看脚本中先前定义的变量.

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.

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

这篇关于如何根据从gmail到Google表格的标签获取单个电子邮件的正文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！