Google Apps 脚本 - 将 gmail 中的数据提取到电子表格中 [英] Google Apps Scripts - Extract data from gmail into a spreadsheet

问题描述

这是我尝试从头开始编写的第一个脚本.到现在为止都不好，所以我要寻求一些帮助.

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. 使用规则为收到的电子邮件添加唯一标签.

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

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

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.

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

Write all that info to a spreadsheet.

去掉唯一的标签信息以防止重复运行.

Get rid of unique tag info to prevent duplicate runs.

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

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

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

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.

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

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

}
} 

任何帮助将不胜感激.

谢谢塞巴斯蒂安

推荐答案

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

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

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

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

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

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

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

通过

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

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

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:

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

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

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

这篇关于Google Apps 脚本 - 将 gmail 中的数据提取到电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！