Google Apps脚本 - 将来自gmail的数据提取到电子表格中 [英] Google Apps Scripts - Extract data from gmail into a spreadsheet
问题描述
这是我尝试从头开始编写的第一个脚本。现在一直没有好的，所以我要去寻求帮助。
案例：我从电子商务网站收到电子商务确认电子邮件没有回复电子邮件地址。在电子邮件的正文中，他们从买家处发送电子邮件地址我想发送一封自动邮件到身体的电子邮件地址。
我打算如何做到这一点（任何建议消除步骤将被感谢）。 b
$ b
-
使用规则为传入的电子邮件加上唯一标记标记。
使用该标签通过脚本识别gmail中的电子邮件，逐一浏览并提取我需要的信息。使用正则表达式和电子邮件正文内容来提取我需要发送自动电子邮件的电子邮件地址。计划将获得：主题，日期和来自正文的电子邮件。 -
将所有信息写入电子表格。
-
摆脱唯一的标签信息以防止重复运行。
然后使用form mule addon从电子表格发送电子邮件。
到目前为止，我已经处理了步骤1（简单），并且一直在步骤2和步骤3（im not一个编码器，我可以读，undestrand和黑客，从头开始写就是完全不同的东西）。我认为这是处理它的最好方法之前，我处理了4。
使用脚本将信息提取到电子表格，使用插件我使用电子表格中的信息发送电子邮件。
这是迄今为止编写的代码。我以后离开了正则表达式部分，因为我甚至不能在电子表格中写入任何东西。一旦我得到那个工作，生病开始在正则表达式中工作，并删除标签方面的脚本。
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
var subject = message.getSubject（）;
tosp（message）;
$ b函数tosp（消息）{
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）;
$ b code $
$ b任何帮助将不胜感激。
感谢
Sebastian
解决方案我写和测试完成了你提到的步骤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）;
$ b你的代码中的一个缺点是 appendRow 函数接受在 [] 括号内指定的元素数组。
根据您要附加脚本的位置，您的代码行：
var ss = SpreadsheetApp.openById（id ）;
如果脚本正在电子表格的脚本编辑器中写入，您需要这些电子邮件被记录。但是，如果该电子表格中有多张工作表，则可以替换我的行。
var ss = SpreadsheetApp.getActiveSheet（）;
by
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getSheetByName（Sheet1）;
另一个建议是当前的代码会给你HTML格式的消息。因此，如果您希望以纯文本的形式获取消息，请使用以下命令：
var msg = messages [i] .getPlainBody（）;
现在您可以为正则表达式编写另一个函数，并将消息 msg 。希望这有助于！
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).
Use a rule to tag incoming emails with a unique tag.
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.
Then use form mule addon to send emails from the spreadsheet.
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);
}
}
Any help would be appreciated.
Thanks
Sebastian
解决方案 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);
}
}
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();
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:
var msg = messages[i].getPlainBody();
Now you can write another function for regex and pass the message msg to that. Hope this helps!
这篇关于Google Apps脚本 - 将来自gmail的数据提取到电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
