Skip to content

Instantly share code, notes, and snippets.

@takvol
Forked from siygle/gist:9678772
Last active January 18, 2023 16:31
Show Gist options
  • Save takvol/0a0dd9b089314bc5463eaf6af83fa060 to your computer and use it in GitHub Desktop.
Save takvol/0a0dd9b089314bc5463eaf6af83fa060 to your computer and use it in GitHub Desktop.
Parse Gmail Inbox to sheet
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
}
@susanlynnholland
Copy link

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment