CODEWITHSUNDEEP
parsinggoogle-apps-scriptgoogle-sheetsgmail
sdgd
sdgd

Reputation: 733

How to parse Gmail messages to extract data to Google Sheets

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.

Upvotes: 0

Views: 6281

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

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)
 }

Upvotes: 1

Related Questions