How to parse Gmail messages to extract data to Google Sheets

Question

I'm working on a R&D project where I collect my server side statistics using Linux commands and output the same to my mail. Now my plan is to read and parse the Gmail content which has my data of the server (as below) and extract it to Google Sheets. My Gmail content looks like below which has data in rows and columns.

Date&Time JVM PID CPU MEM FGC
03-09-2017-09-08-PM abc01_xzy01 12345 1.2% 2.75 3
03-09-2017-09-08-PM abc01_xzy01 12345 3.5% 2.71 4
03-09-2017-09-08-PM abc01_xzy01 12345 4.6% 2.79 5

My idea here is to pull exactly the same way into a Google Sheet. I'm using the below code but it's unable to read the content. I'm thinking if it's related to the RegExp used. Could you please tell me how exactly the code has to be written in the if statement.

function parseEmailMessages(start) {

  start = start || 0;

  var threads = GmailApp.getInboxThreads(start, 100);
  var sheet = SpreadsheetApp.getActiveSheet();

  for (var i = 0; i < threads.length; i++) {

    var tmp,
      message = threads[i].getMessages()[0],
     // subject = message.getSubject(),
     subject = "SERVER TEST REPORT",
      content = message.getPlainBody();

    if (content) {

      tmp = content.match(/Date&Time:\s*([A-Za-z0-9\s]+)(\r?\n)/);
      var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No Date & Time';

      tmp = content.match(/JVM:\s*([A-Za-z0-9\s]+)(\r?\n)/);
      var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No JVM';

      sheet.appendRow([username, email, subject, comment]);

    } 

  } 
}

My output just prints No Date & Time and No JVM in the Google Sheets. I want to print the names and data in the columns and rows as given in the mail. Can someone please help me on this and tell me what is the mistake and how to pull the data like expected. Thanks in advance.

Ed Nelson · Accepted Answer · 2017-09-03 22:02:58Z

1

If all that is in your email is what you posted above, this will put it in your spreadsheet. Change the email subject and sheet name to suit your needs.

function getGmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var threads = GmailApp.search('IMPORTANT1', 0, 10);

  for (var i = 0; i < threads.length; i++) {
    var messages = GmailApp.getMessagesForThread(threads[i]);
  for (var j = 0; j < messages.length; j++) {
    var msg=messages[j].getPlainBody();
    var msg=msg.trim()
  }
}
 result1(msg)
  }
function result1(range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1")
  var lr=sheet1.getLastRow()
  var output=[]
  var line=range.split("\n")
  for(j=0;j<line.length;j++){
     output.push(line[j].split(" "))
  }
  sheet1.getRange(lr+1, 1, output.length, output[0].length).setValues(output)
 }

answered Sep 3, 2017 at 22:02

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sdgd Over a year ago

thanks for your suggestion Ed. I have a couple of queries. could you pls clarify. Where can i mention the subject name so that it can directly look for the respective subject and extract the mail content. Also, After executing the code, i observed that all the data is being pasted in one single row rather than getting into the new row after the first row ends. could you please help on that

Ed Nelson Over a year ago

I am attaching a shared spreadsheet you can copy and try. It has a notes sheet with instructions. When you copy it, you will be the owner and it will get your Gmail. As for the single row. The script looks for /n new-line at the end of the lines to know where to split them. Your email must not contain the new-line character. The easiest thing would be to add them to your Linux feed. To test, you can send yourself an email copying your lines one at a time hitting enter after each line. docs.google.com/spreadsheets/d/…

sdgd Over a year ago

Thanks Ed. this is helpful.

sdgd Over a year ago

Ed, i'm facing a problem here, could you please help me on this. i'm getting the error Cannot convert [object Object] to (class). (line 6, file "Code") when i try to run the code from the trigger. could you please let me know on how to rectify this.

Collectives™ on Stack Overflow

How to parse Gmail messages to extract data to Google Sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related