google-apps-script - 使用 Google App Scrip 过滤消息正文以放入电子表格表

Question

我根据电子邮件正文中的交易数据从银行收到有关每笔交易的电子邮件，请参见下面的示例。

现在，我想在我的电子表格中分离要传输的数据，例如分支代码旁边的值应该在 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(/\n*.+:/g,',')
        .replace(/^,/,'')
        .replace(/\n/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]

标签： google-apps-scriptautomationgmailspreadsheet

Answer 1

有太多的猜测，所以只是猜测......

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('\n').slice(0, replaces.length)
    .map((c,i) => c.replace(replaces[i], '').trim());

}

console.log(parseEmail(message));

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

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

结果：

一个	乙	C	D	乙	F	G	H	我	Ĵ	ķ	大号
123	abc BR 卡拉奇	242	abc123456789	4	美国广播公司			查看	100, 000.00	27-08-2021	27-08-2021