-
-
Save takvol/0a0dd9b089314bc5463eaf6af83fa060 to your computer and use it in GitHub Desktop.
Parse Gmail Inbox to sheet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function extractData(data, startStr, endStr) { | |
// This function extracts text between two strings | |
// i.e. extractData("Good_news,_everyone!", "Good_", ",_Everyone!") will return "News" | |
var startIndex, endIndex, text = 'N/A'; | |
startIndex = data.indexOf(startStr); | |
if(startIndex != -1) { | |
startIndex += startStr.length; | |
text = data.substring(startIndex); | |
if(endStr) { | |
endIndex = text.indexOf(endStr); | |
if(endIndex != -1) { | |
text = text.substring(0, endIndex); | |
} else { | |
text = 'N/A'; | |
} | |
} | |
} | |
return text; | |
} | |
function parseEmailMessages() { | |
// var threads = GmailApp.search(); | |
// Have to get data separate to avoid google app script limit! | |
var start = 0; | |
var sheetId, sheet, threads, labelName, label; | |
// SheetId is your google spreadsheet id | |
labelName = 'Parsed'; | |
sheetId = 'abc1234567'; | |
label = GmailApp.getUserLabelByName(labelName)||GmailApp.createLabel(labelName); | |
sheet = SpreadsheetApp.openById(sheetId).getSheets()[0]; | |
threads = GmailApp.search('in:inbox -label:'+labelName, start, 100); | |
// Search query for messages without specific label | |
for (var i = 0; i < threads.length; i++) { | |
// Get the first email message of a threads | |
var message = threads[i].getMessages()[0]; | |
var content = message.getPlainBody(); | |
// Get the plain text body of the email message | |
// Note that for some types of messages getPlainBody() method may return null | |
// in such cases consider using getRawContent() or getBody() methods and parse HTML | |
if (content) { | |
var date = message.getDate(), | |
subject = message.getSubject(), | |
sender = message.getFrom(), | |
someData = extractData(content,"the", "."), | |
name = extractData(content,"Name: ", "\n"), | |
comment = content.match(/Comment:\s([^]+?)\n/); | |
// You may prefer regex for parsing, then extra validation needed | |
comment = (comment && comment[1]) ? comment[1]: 'N/A'; | |
// Add extra item to parse | |
sheet.appendRow([date, subject, sender, someData, name, comment]); | |
threads[i].addLabel(label); | |
//add your label for parsed messages | |
Utilities.sleep(500); | |
}// End if | |
}// End for loop | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you! This code is incredibly helpful. I'm using getPlainBody to get the message contents but want to remove the signature portion. I've looked online but cannot find that info. Is that something you can help with? I will also speak with colleagues. Thank you for your time!