Google Apps Script拉取Gmail到表格无法整行写入数据求助

你的代码存在两处核心逻辑错误，直接导致了数据拆分多行、字段赋值错误的问题：

• appendRow方法被放在了遍历emailData属性的循环内部，每向数组插入一个属性值就新增一行，单条邮件的8个属性就会生成8行数据，每行逐列新增内容
• 匹配「转账金额」的正则处理逻辑错误，把匹配到的金额值赋值给了recipient字段，覆盖了已经提取的收件人信息，同时没有给amount、total字段赋值

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Gmail');
  menu.addItem('导入Gmail邮件', 'getGmail')
  menu.addToUi();
}

function getGmail(){
  var label = GmailApp.getUserLabelByName("Billing");
  var threads = label.getThreads();
  for (var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++){
      var message = messages[j];
      extractDetails(message);
      GmailApp.markMessageRead(message);
    }
    threads[i].removeLabel(label);
  }
}

function extractDetails(message){
  var emailData = {
    date: "Null",
    sender: "Null",
    subject: "Null",
    body: "Null",
    recipient: "Null",
    amount: "Null",
    fee: "Null",
    total: "Null"
  }
  // 正则特殊字符*需要转义
  var emailKeywords = {
    recipient: "Recipient:\\*",
    amount: "Amount Sent",
    fee: "Fee",
    total: "Total Amount Charged:\\*"
  }
  emailData.date = message.getDate();
  emailData.sender = message.getFrom();
  emailData.subject = message.getSubject();
  emailData.body = message.getPlainBody();
  var regExp, matchRes;
  // 匹配收件人
  regExp = new RegExp("(?<=" + emailKeywords.recipient + ").*");
  matchRes = emailData.body.match(regExp);
  if(matchRes) emailData.recipient = matchRes.toString().trim();
  // 匹配转账金额
  regExp = new RegExp("(?<=" + emailKeywords.amount +").*[0-9]*\\.[0-9]{2}");
  matchRes = emailData.body.match(regExp);
  if(matchRes) emailData.amount = matchRes.toString().trim();
  // 匹配手续费
  regExp = new RegExp("(?<=" + emailKeywords.fee + ").*[0-9]*\\.[0-9]{2}");
  matchRes = emailData.body.match(regExp);
  if(matchRes) emailData.fee = matchRes.toString().trim();
  // 匹配总费用（原代码遗漏该逻辑）
  regExp = new RegExp("(?<=" + emailKeywords.total + ").*[0-9]*\\.[0-9]{2}");
  matchRes = emailData.body.match(regExp);
  if(matchRes) emailData.total = matchRes.toString().trim();
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var emailDataArr = [];
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
  }
  // appendRow移到循环外部，数组组装完成后再整行插入
  activeSheet.appendRow(emailDataArr);
}

内容的提问来源于stack exchange，提问作者Jonathan D