Pulling specific data from spreadsheet cell and populating corresponding cells

Question

Goal: Pull Gmail into spreadsheet, pull specific data from cell and populate other cells

Pulling the gmail into the sheet works. Extracting the data works but it doesn't put the data in a single row it put it in several rows and each row will add a column of data. Below is a link to the google sheet so you can see exactly what I'm trying to accomplish. In row 1 I've accomplished my goal using google sheet formulas but that requires multiple hidden columns.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Gmail');
  menu.addItem('Get 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;
 
  regExp = new RegExp("(?<=" + emailKeywords.recipient + ").*");
  emailData.recipient = emailData.body.match(regExp).toString().trim();
  
  regExp = new RegExp("(?<=" + emailKeywords.amount +").*[0-9]*\.[0-9]+[0-9]+");
  emailData.recipient = emailData.body.match(regExp).toString().trim();
 
  regExp = new RegExp("(?<=" + emailKeywords.fee + ").*[0-9]*\.[0-9]+[0-9]+");
  emailData.fee = emailData.body.match(regExp).toString().trim();
 
 
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  var emailDataArr = [];
 
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
 
 
  activeSheet.appendRow(emailDataArr);
 
  }
}

https://docs.google.com/spreadsheets/d/14FuuKS2ChZ_O687D2ybGvg0ZhfGvHnuzUpAx6oIulg4/edit?usp=sharing

fullfine fullfine · Accepted Answer · 2021-07-13T16:01:25

Answer

It is difficult to provide you some help as I don't understand how Pulling the gmail into the sheet works if the error appears before writing to the sheet activeSheet.appendRow(emailDataArr);.

What I see

Looking at your code and the error you get, I think the problem is in the thread/message handling. For example, the line threads[i].Removelabel(label); should be outside for (var j = 0; j < messages.Length; j++).

Suggestion

I suggest you use logs to see how the algorithm is collecting the different data. Try simply putting console.log(variable) in different parts of your code.

Finally, I advice that when you share a Spreadsheet document, do not disable the export options, because then I cannot create a copy of it to test the code. In addition, it would also be useful to have some mails to be able to reproduce all the steps. You could create a function in which these messages are sent, or at least show how they look like.

Reference

Use the Apps Script execution log