Forked from takvol/gist:0a0dd9b089314bc5463eaf6af83fa060
Created
July 17, 2020 14:56
-
-
Save MikelMosso/cc46d3a9bb98a62dbec3b08f3fc49fb1 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