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

问题描述

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

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 Scripts - 从 gmail 中提取数据到电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！