Google脚本可从电子邮件中提取某些信息并放入表格中 [英] Google Script to pull certain information from email and put in a Sheet

问题描述

希望能够自动完成一天中令人烦恼的部分... 电子邮件总是采用相同的格式...这是我要从中提取数据的电子邮件部分.

Hoping to be able to automate an annoying part of my day... The emails always come formatted the same ... here's the part of the email I'm looking to pull the data from.

项目#:SS10MM
产品描述:10mm插座
供应商:商店
供应商物料代码:10MSS
投诉:输了另一个，真的希望您可以在其中放入GPS芯片！

Item#: SS10MM
Product Description: 10mm SOCKET
Vendor: Store
Vendor Item Code: 10MSS
Complaint: Lost another one, really wish you could put a GPS chip in these!

是否可以有一个脚本来提取该信息并将每个信息放在相应的列中?

Is it possible to have a script pull that information and put each of those in a corresponding column?

我已经尝试使用在搜索将电子邮件解析为工作表"时在网上找到的一些脚本，但是这些脚本似乎试图将整个电子邮件转移过来，这显然不是我想要的.

I have attempted to work with some of the scripts I have found online when searching 'parse email to sheet', but those seem to be trying to bring the entire email over, which is obviously not what I'm looking for.

帮助?

我想当前的代码可能有帮助，是吧?

I guess current code might help, huh?

function getGmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var threads = GmailApp.search('in:inbox is:unread from:
([e-mail]) "TEST EMAIL"');

for (var i = 0; i < threads.length; i++) {
var messages = GmailApp.getMessagesForThread(threads[i]);
for (var j = 0; j < messages.length; j++) {
var msg=messages[j].getPlainBody(); 
var msg=msg.trim() 
}
}
result1(msg)
}
function result1(range) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("Sheet1")
var lr=sheet1.getLastRow()
var output=[]
var line=range.split("\n")
for(j=0;j<line.length;j++){
output.push(line[j].split(" "))
}
var output1=[]
for(k=0;k<output.length;k++){
if(output[k]!=""){
output1.push(output[k])
}}
sheet1.getRange(lr+1, 1, output1.length, 
output1[0].length).setValues(output1)
}

推荐答案

我使用此要点，并添加了一些内容来帮助您入门.您必须设置时间驱动的触发器运行main_emailDataToSpreadsheet()函数，并将电子邮件标记为待处理".

I use this gist and added a little bit to get you started. You have to setup a Time-driven Trigger to run the main_emailDataToSpreadsheet() function and have the emails labeled as 'pending'.

已更新:

 //Modified from https://gist.github.com/richard-to/8797504 < http://pipetree.com/qmacro/blog/2011/10/04/automated-email-to-task-mechanism-with-google-apps-script/

var LABEL_PENDING = "pending";
var LABEL_DONE = "done";

// processPending(sheet)
// Process any pending emails and then move them to done
function processPending_(sheet) {

  // Get out labels by name
  var label_pending = GmailApp.getUserLabelByName(LABEL_PENDING);
  var label_done = GmailApp.getUserLabelByName(LABEL_DONE);

  // The threads currently assigned to the 'pending' label
  var threads = label_pending.getThreads();

  // Process each one in turn, assuming there's only a single
  // message in each thread
  for (var t in threads) {
    var thread = threads[t];

    // Gets the message body
    var message = thread.getMessages()[0].getPlainBody();
    Logger.log(message);
    // Process the messages here
    message = message.substr(message.search("Item#:")); //Get the beginning of the important part + cut off the beginning
    Logger.log(message);
    message = message.split("\n");
    Logger.log(message[0].split(": ")[1]);

    var data = [message[0].split(": ")[1], //Item#
                message[1].split(": ")[1], //Prod. Desc.
                message[2].split(": ")[1], //Vendor
                message[3].split(": ")[1], //Ven. Item Code
                message[4].split(": ")[1] //Complaint
               ];
    // Add message to sheet
    sheet.appendRow(data);

    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }
}

这篇关于Google脚本可从电子邮件中提取某些信息并放入表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！