Parsing Gmails and extracting Subject line into Google Spreadsheet

Question

I am trying to create a code that parses through a gmail and extracts the subject line of specifically labeled emails and automatically exports the data into a google spreadsheet.

var sheet = SpreadsheetApp.getActiveSheet();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

function getEmails() {
  var label = GmailApp.getUserLabelByName("Reservation confirmed");
  var threads = label.getThreads();
  var row = 2;
  for (var i = 0; i < threads.length; i++) {
    var messages=threads[i].getMessages();
    for (var m=0; m < messages.length; m++) {   
      sheet.getRange(row,1).setValue(messages[m].getPlainBody());
      row++;
    }
  }
}

function onOpen() {
  var menuEntries = [ {name: "Load Emails", functionName: "getEmails"} ];
  spreadsheet.addMenu("Email", menuEntries);
}

This current code I have takes the body of labeled emails but for some reason I cannot alter it so that it only takes the subject line.

Possible duplicate of [How to get subject from gmail and put into a google doc or spreadsheet](https://stackoverflow.com/questions/29084507/how-to-get-subject-from-gmail-and-put-into-a-google-doc-or-spreadsheet) — Ujjwal Gulecha, Feb 20 '18 at 07:43
I looked into this but I've after trying it out, this only takes one subject line. I tested it by marking 3 emails with the same label but it only extracts one of the subject lines. — Michael Watanabe, Feb 20 '18 at 10:45

score 0 · Answer 1 · answered Feb 20 '18 at 11:09

0

Use the getSubject() function on the messages instead:

for (var m=0; m < messages.length; m++) {   
  sheet.getRange(row,1).setValue(messages[m].getSubject());
  row++;
}

answered Feb 20 '18 at 11:09

Tholle

108,070
19
198
189

Thank you! Would it then be possible to take specific information from a subject line? Such as a date for example – Michael Watanabe Feb 20 '18 at 11:37
@MichaelWatanabe No problem! I'm not quite sure I understand what you mean. If you want the date the message was sent, you can use [getDate()](https://developers.google.com/apps-script/reference/gmail/gmail-message#getdate) instead. – Tholle Feb 20 '18 at 11:54
I actually meant more like if the subject line was something like "Reservation confirmed for February 25th" if I could just extract February 25th. – Michael Watanabe Feb 20 '18 at 11:59
@MichaelWatanabe Alright. That is a whole other question, but [this answer](https://stackoverflow.com/questions/25060186/how-to-extract-date-from-string-using-javascript#answer-25060462) might give some inspiration. – Tholle Feb 20 '18 at 12:02

score 0 · Answer 2 · answered Feb 24 '18 at 07:15

0

You could dump the full subject into the sheet into another column and use a formula to extract the date like below:

=Right('cell reference',(Len('cell reference')-FIND("confirmed for ",'put your cell reference here'))-13)

answered Feb 24 '18 at 07:15

Jon Jewett

68
1
7

Parsing Gmails and extracting Subject line into Google Spreadsheet

2 Answers2