使用Google App Scrip过滤邮件正文放入电子表格

问题描述

根据电子邮件正文中的交易数据，我在每笔交易中都会收到来自银行的电子邮件，请参阅下面的示例。

现在，我想要分隔要在电子表格中传输的数据，比如分支机构代码旁边的值应该在A列的最后一行，分支机构代码在B列，编号在C列，依此类推。

下面是我尝试的代码：

//function to seprate and filter data
function parseEmail(message){
    let parsed = message.replace(/Branch Code /g,'')
        .replace(/Branch Name /g,'')
        .replace(/Slip No /g,'')
        .replace(/
*.+:/g,',')
        .replace(/^,/,'')
        .replace(/
/g,'')
        .split(',');

    let result = [0,1,2,3,4,6].map(index => parsed[index]);
    Logger.log(result[0]);
    return result;
}

// main function to search email
function testforemails0003(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Emails");
  var Gmail = GmailApp;
  var lasttime = sheet.getRange("Z1").getValue();
  Logger.log(lasttime);
  var cdate = new Date();
  var ctime = cdate.getTime();
  var qDate = sheet.getRange("Z3").getValue();
  Logger.log("QDATE IS " + qDate);

  // SEARCH EMAIL
  var query = 'subject: subject, after:' + Math.floor((qDate.getTime()) /1000);
  var threadsNew = Gmail.search(query);
  Logger.log(threadsNew.length);
  
  //loop all emails
  for(var n in threadsNew){
    var thdNew  = threadsNew[n]; 
    var msgsNew = thdNew.getMessages();　
    var msgNew = msgsNew[msgsNew.length-1];
  // GET ATTACHMENT
    var bodyNew = msgNew.getBody();
    var plainbody  = msgNew.getPlainBody();
    var subject = msgNew.getSubject();
    var Etime = msgNew.getDate();
    var attachments = msgNew.getAttachments();
    var attachment = attachments[0];
    
    Logger.log(Etime);
    Logger.log(subject);
    parseEmail(plainbody);
  }
    
    Logger.log(threadsNew.length);
    var lastscantime = threadsNew[0].getLastMessageDate();
    var lastsubject = threadsNew[0].getFirstMessageSubject();
    Logger.log(lastscantime);
    Logger.log(lastsubject);
    sheet.getRange("z3").setValue(lastscantime);
    
}

问题是我希望所有值都在单独的数组中，所以将它们放在单独的列中。

我的输出是一个数组中的所有值，我希望在电子表格中设置所有分隔的字段。

我的输出是：

[0123
abc BR KARACHI
Slip No 0000242
BL Number abc123456789
Type Of Payment 04
Customer Name abc corporation
Payer Bank
Payer Bank Branch
Transaction Type Cheque
Amount 100, 000.00
Posting Date 27-08-2021
Value Date 27-08-2021
Lorem ipsum dolor sit amet,  consectetuer adipiscing elit. Aenean commodo
ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis
parturient montes,  nascetur ridiculus mus. Donec quam felis,  ultricies nec,  pretium quis]

推荐答案

要猜的太多了，所以只是个猜测...

var message =

`Branch code 0123
Branch name abc BR KARACHI
Slip No 0000242
BL Number abc123456789
Type Of Payment 04
Customer Name abc corporation
Payer Bank
Payer Bank Branch
Transaction Type Cheque
Amount 100, 000.00
Posting Date 27-08-2021
Value Date 27-08-2021
Lorem ipsum dolor sit amet,  consectetuer adipiscing elit. Aenean commodo
ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis
parturient montes,  nascetur ridiculus mus. Donec quam felis,  ultricies nec,  pretium quis`


function parseEmail(message) {

  var replaces = [
    'Branch code',
    'Branch name',
    'Slip No',
    'BL Number',
    'Type Of Payment',
    'Customer Name',
    'Payer Bank',
    'Payer Bank Branch',
    'Transaction Type',
    'Amount',
    'Posting Date',
    'Value Date'
  ];

  return message.split('
').slice(0, replaces.length)
    .map((c,i) => c.replace(replaces[i], '').trim());

}

console.log(parseEmail(message));

您可以通过以下方式将解析后的数组作为新行追加到工作表：

SpreadsheetApp.getActiveSheet().appendRow(parseEmail(message));

结果：