Created
January 17, 2020 19:21
-
-
Save corneliusroemer/5680fd761bb6d27de540e3c9b56ac60e to your computer and use it in GitHub Desktop.
Google Apps Script that searches for "undeliverable" emails and extracts email addresses that bounced. Pastes emails and context into Google Sheet for analysis.
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 processInboxToSheet() { | |
var threads = GmailApp.search("Undeliverable", 0, 500) | |
//Replace ID with one of your private spreadsheets ID | |
var sheet = SpreadsheetApp.openById("16RsL_4fh0WXSICw5VJDHVAKtija21JBxxxxxxxxx").getActiveSheet(); | |
for (var i = 0; i < threads.length; i++) { | |
var messages = threads[i].getMessages(); | |
for (var j = 0; j < messages.length; j++){ | |
var subject = messages[j].getSubject(); | |
var content = messages[j].getPlainBody(); | |
var date = messages[j].getDate(); | |
if (RegExp(/Original Message Headers/i).test(content)){ | |
var extract = content.match(RegExp(/[\s\S]*?(?=Original Message Headers)/i)).toString(); | |
var email = extract.match(/[^\s\:\(\)\,\<\>\@]+@[^\s\:\(\)\,\<\>\@]+/g); | |
var type = extract.match(/Policy violation or system error|Invalid recipient|Quota|Hop count exceeded|Connection refused|mailbox full|SPF validation error|554 delivery error|mailbox is disabled|doesn't have|spam|mailbox unavailable|Message expired|user|wasn't found|does not exist|RecipNotFound|DNS|overquota|expired|Unknown to address|Spam or virus detected/gi); | |
type ? type=type.toString() : type="none"; | |
if(!email) Logger.log(extract); | |
else{ | |
for (var k = 0; k < email.length; k++){ | |
if(email.indexOf(email[k])>=k){ | |
sheet.appendRow([date,subject, email[k],type]); | |
} | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is a similar snippet developed by the prolific add-on developer Amit Agarwal which works for bounce messages sent directly by Gmail (I send emails through Outlook via alias so my code is slightly different). His is definitely cleaner - but basically the same.
https://www.labnol.org/code/20297-gmail-parse-bounced-emails