Skip to content

Instantly share code, notes, and snippets.

@corneliusroemer
Created January 17, 2020 19:21
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save corneliusroemer/5680fd761bb6d27de540e3c9b56ac60e to your computer and use it in GitHub Desktop.
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.
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]);
}
}
}
}
}
}
}
@corneliusroemer
Copy link
Author

corneliusroemer commented Jan 17, 2020

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

function getBouncedEmails() {

    /* Written by Amit Agarwal */
    /* Email: amit@labnol.org  */

    // Write the bounced email report to a Google SpreadsheetApp
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();

    // Find all emails returned via Gmail Mailer Maemon
    var query = "from:(mailer-daemon@google.com OR mailer-daemon@googlemail.com)";

    // Get the most recent 500 bounced email messages in Gmail
    GmailApp.search(query, 0, 500).forEach(function(thread) {
        thread.getMessages().forEach(function(message) {
            if (message.getFrom().indexOf("mailer-daemon") !== -1) {
                var body = message.getPlainBody();
                // Get the bounced email address from the body
                var matches = body.match(/Delivery to[\s\S]+?(\S+\@\S+)\s([\s\S]+?)----- Original Message/);
                if (matches) {
                    // Get the exact reason for the email bounce
                    var reason = matches[2].match(/The error.+:\s+(.+)/) || matches[2].match(/Technical details.+:\s+(.+)/);
                    if (reason) {
                        // Save the data in a Google Spreadsheet
                        sheet.appendRow([
                            thread.getLastMessageDate(),
                            matches[1],
                            reason[1].replace(/ (Please|Learn|See).*$/, ""),
                            thread.getPermalink(),
                            thread.getFirstMessageSubject()
                        ]);
                    }
                }
            }
        });
    });
}

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