Parsing Gmail into Google Sheets using Google Apps Script

Question

I am trying to parse Gmail with a specific subject and have the data output to a google sheet in specific columns. I had the script working using just the getPlainBody() option, but then I tried to add getDate() and now I am getting an error.

I am sure there is something wrong in my syntax and am now trying various things in the getGmail and gather functions. My current code is below and when ran, the date of the email lands in column A, the entire body of the email lands in column B and I get the error "TypeError: message.replace is not a function".

    function onOpen() {
    const spreadsheet = SpreadsheetApp.getActive();
    let menuItems = [
        {name: 'Gather emails', functionName: 'gather'},
    ];
    spreadsheet.addMenu('New Employees', menuItems);
}
function gather() {
  let messages = getGmail();
  if (messages.length > 0) {
    let curSheet = SpreadsheetApp.getActiveSheet();
    curSheet.getRange(curSheet.getLastRow() + 1, 1, messages.length, messages[0].length).setValues(messages);
     messages.forEach(message => {curSheet.appendRow(parseEmail(message))});
  }
}

function getGmail() {
  const query = "from:(ApplicationServices@help.net) subject:New Account Newer_than:18d NOT label:done";
  let threads = GmailApp.search(query,0,10);

  let label = GmailApp.getUserLabelByName("done");
  if (!label) {label = GmailApp.createLabel("done")}

  let messages = [];
  threads.forEach(thread => {
    const m = thread.getMessages()[0];
    messages.push([m.getDate(), m.getPlainBody()]);
    label.addToThread(thread);
  });
  return messages;
}

function parseEmail(message){
    let parsed = message.replace(/,/g,'')
        .replace(/
\s*.+: /g,',')
        .replace(/^,/,'')
        .replace(/
/g,'')
        .replace(/^[\s]+|[\s]+$/g,'')
        .replace(/
/g,'')
        .split(',');
    let result = [0,1,2,3,4,5,6,7].map(index => parsed[index]);

    return result;
}

Here is what the emails look like:

An account has been created:

Employee ID: 987333
First: Bill
Last: Walsh
Username: bwalsh
Pwd: 67&&8^.!

Employees must change their email password as soon as possible. Passwords expire every 90 days and must be 8 characters or more (with at least one uppercase, one lowercase and a number or symbol). Note: This password is also the password to access Google Email.

If you require assistance please contact your district technical support team or the HELP IT Help Desk at 998-9999 or via e-mail at helpdesk@help.net.

doubleunary · Answer

The error is because your getGmail() function returns a 2D array where the first value in each subarray is a Date and the second value is a text string. To access the text string, you need to refer to it through index 1, like this:

   messages.forEach(message => { curSheet.appendRow(parseEmail(message[1])) });

But it would probably make more sense to remove the above line altogether and run the parse function within getGmail(), like this:

    messages.push([m.getDate(), parseEmail(m.getPlainBody())]);

Parsing Gmail into Google Sheets using Google Apps Script

1 Answers