Extract Gmail content and selected headers to a spreadsheet #gas #gmail #sheet

70e04a67f1f5fd96a708.md

Gmail to Spreadsheet

Outputs to spreadsheet:

• From - email
• Date - received
• Flag - target text found in email body
• Comment - extracted from email body

Gmail Advanced search

Code.gs

var SHEET_ID = YOUR_SPREADSHEET_ID;
var SHEET_NAME = YOUR_SHEET_NAME;

function getEmails_(q) {
    var emails = [];
    var thds = GmailApp.search(q);
    for (var i in thds) {
        var msgs = thds[i].getMessages();
        for (var j in msgs) {
            var b = msgs[j].getBody().replace(/<.*?>/g, '\n').replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n');

            // Extract text by stripping known lines before and after
            var comment = b.replace(/(.|\n)Text to discard just before comment line\n/gm, '');
            comment = comment.replace(/Text to discard just after comment line(.|\n)*/gm, '');

            emails.push([
                msgs[j].getFrom(),
                msgs[j].getDate(),
                // TRUE if specific text appears in email body
                b.indexOf('Text to look for in email body') >= 0,
                comment
            ]);
        }
    }
    emails.sort(
        function (a, b) {
            return a[1] - b[1] // Compare by date
        })
    return emails;
}

function appendData_(sheet, array2d) {
    sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function run() {
    //Gmail Advanced search https://support.google.com/mail/answer/7190
    var array2d = getEmails_("the");
    if (array2d) {
        var ss = SpreadsheetApp.openById(SHEET_ID);
        var sheet = ss.getSheetByName(SHEET_NAME);
        if (!sheet) sheet = ss.insertSheet(SHEET_NAME);
        appendData_(sheet, array2d);
    }
}