Javascript Google Apps Scripts - 从 gmail 中提取数据到电子表格中

Google Apps Scripts - Extract data from gmail into a spreadsheet

提问by Sebastian Hollmann

this is the first script i try to write from scratch. It's been no good up to now so i'm going to ask for some help.

这是我尝试从头开始编写的第一个脚本。到目前为止一直不好，所以我要寻求一些帮助。

Case: I recieve e-commerce confirmation emails from e-commerce sites no reply email address. In the email's body they send email address from buyers. I want to send an automated mail to the body's email address.

案例：我收到来自电子商务网站的电子商务确认电子邮件，但没有回复电子邮件地址。在电子邮件的正文中，他们发送买家的电子邮件地址。我想向正文的电子邮件地址发送自动邮件。

How i plan to do this (any suggetions to eliminate steps will be thanked).

我打算如何做到这一点（将感谢任何消除步骤的建议）。

1. Use a rule to tag incoming emails with a unique tag.

2. Use that tag to identify emails in gmail with a script, go one by one and extract the info i need. Use regex with the emails body content to extract the email address i need to send the automated emails. Plan is to get: subject, date, email from body.

3. Write all that info to a spreadsheet.

4. Get rid of unique tag info to prevent duplicate runs.

5. Then use form mule addon to send emails from the spreadsheet.

1. 使用规则用唯一标签标记传入的电子邮件。

2. 使用该标签通过脚本识别 gmail 中的电子邮件，逐一提取我需要的信息。使用带有电子邮件正文内容的正则表达式来提取我需要发送自动电子邮件的电子邮件地址。计划是从正文中获取：主题、日期、电子邮件。

3. 将所有信息写入电子表格。

4. 摆脱独特的标签信息以防止重复运行。

5. 然后使用 form mule 插件从电子表格发送电子邮件。

So far, i've dealt with steps 1 (easy), and been stuggling with steps 2 and 3 (im not a coder, i can read, undestrand and hack. writing from scratch is a completely different thing). Ive dealt with 4 before i think this is the best way to deal with it.

到目前为止，我已经处理了第 1 步（简单），并一直在纠结第 2 步和第 3 步（我不是编码员，我可以阅读、理解和破解。从头开始写作是完全不同的事情）。在我认为这是处理它的最佳方法之前，我已经处理了 4 个。

With the script i extract info to the spreadsheet, with the addon i use the info from the spreadsheet to send emails.

使用脚本我将信息提取到电子表格中，使用插件我使用电子表格中的信息发送电子邮件。

This is the code ive written so far. I've left the regex part for later cause i cant even write anything into the spreadsheet yet. once i get that working, ill start working in the regex and "remove the label" aspects of the script.

这是我到目前为止编写的代码。我已经将正则表达式部分留给以后使用，因为我什至无法在电子表格中写入任何内容。一旦我开始工作，我就开始在正则表达式中工作并“删除标签”脚本的方面。

function myFunction() {
  function getemails() {
    var label = GmailApp.getUserLabelByName("Main tag/subtag");
    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 message=messages[j];
    var subject=message.getSubject();
    tosp(message);
      }
     }
  }

  function tosp(message){
    var body=message.getBody()
    var date=message.getDate();
    var subject=message.getSubject(); 
    var id= "my spreasheet id";
    var ss = SpreadsheetApp.openById(id);
    var sheet = ss.getActiveSheet();
    sheet.appendRow(subject,date,body);    

}
} 

Any help would be appreciated.

任何帮助，将不胜感激。

Thanks Sebastian

谢谢塞巴斯蒂安

回答by pointNclick

Following is the code I wrote and tested that performs the steps 2, 3 and 4 mentioned by you perfectly well.

以下是我编写和测试的代码，它们完美地执行了您提到的步骤 2、3 和 4。

function myFunction() {

  var ss = SpreadsheetApp.getActiveSheet();

  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++)
    {
      var msg = messages[j].getBody();
      var sub = messages[j].getSubject();
      var dat = messages[j].getDate();

      ss.appendRow([msg, sub, dat])
    }
      threads[i].removeLabel(label);
  }
}

One of the faults in your code was that the appendRowfunction accepts an array of elements specified within [ ]brackets.

代码中的一个错误是该appendRow函数接受[ ]括号内指定的元素数组。

Depending on where you're attaching this script, your line of code:

根据您附加此脚本的位置，您的代码行：

var ss = SpreadsheetApp.openById(id);

is not necessary if the script is being written in the script editor of the Spreadsheet where you want these emails to be logged. However, if there are multiple sheets in that spreadsheet, you can replace my line

如果脚本是在您希望记录这些电子邮件的电子表格的脚本编辑器中编写的，则不需要。但是，如果该电子表格中有多个工作表，您可以替换我的行

var ss = SpreadsheetApp.getActiveSheet();

by

经过

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

Another suggestion is that the current code will give you messages in HTML format. Hence, if you want to get the message in plain text as you see it, use:

另一个建议是当前的代码将以 HTML 格式为您提供消息。因此，如果您想以纯文本形式获取消息，请使用：

var msg = messages[i].getPlainBody();

Now you can write another function for regex and pass the message msgto that. Hope this helps!

现在您可以为正则表达式编写另一个函数并将消息传递msg给它。希望这可以帮助！

回答by Moayad Hani Abu Rmilah

Watch the following YouTube videos that exactly explain what you're looking for. Part 1 shows how to get email details:

观看以下 YouTube 视频，这些视频准确地解释了您要查找的内容。第 1 部分展示了如何获取电子邮件详细信息：

https://www.youtube.com/watch?v=gdgCVqtcIw4

https://www.youtube.com/watch?v=gdgCVqtcIw4

You would need regular expressions as you said to extract lead email texts. Part 2 of the video series explains how to achieve this. You may watch it here:

正如您所说，您需要正则表达式来提取主要电子邮件文本。视频系列的第 2 部分解释了如何实现这一点。你可以在这里观看：

https://youtu.be/nI1OH3pAz6s?t=9

https://youtu.be/nI1OH3pAz6s?t=9

You can also get the full code from GitHub from the following link:

您还可以通过以下链接从 GitHub 获取完整代码：

• GitHub: Get Gmail Emails into Google Sheet

• GitHub: Extract Body Contents from Gmail Emails

• GitHub：将 Gmail 电子邮件导入 Google 表格

• GitHub：从 Gmail 电子邮件中提取正文内容