Google Apps Script for building a CSV report from Gmail threads

gmail-report.js

// To use this, create a new Apps Script project and paste this script in.
// https://developers.google.com/apps-script

function FetchReport() {

    // Define a Gmail search query.
    var searchQuery = "cluedo after:2022-01-01"

    // Define a predicate that determines when to stop looping.
    function shouldWeKeepLooping(thread) {
        // Return true to keep looping until we process all results. You could do something more sophisticated.
        return true
    }

    // Define a function that returns a report row.
    function reportRow(thread) {
        var subject = thread.getFirstMessageSubject();
        var date = thread.getLastMessageDate();
        var firstMessage = thread.getMessages()[0];
        var fromAddress = firstMessage.getFrom()

        return [date.toISOString(), subject, fromAddress]
    }

    // Add a header row - this needs to be in sync with the `reportRow` function's implementation.
    sheet = _createDoc()
    sheet.appendRow(["Date", "Subject", "From"])

    _generateReport(searchQuery, sheet, reportRow, shouldWeKeepLooping)
}

function _createDoc() {
    // Create a results doc.
    var now = new Date();
    var filename = "App script results - " + now.toISOString()
    var resultsDoc = SpreadsheetApp.create(filename)
    return resultsDoc.getActiveSheet()
}

function _generateReport(searchQuery, sheet, generateRowFn, breakLoopFn) {
    // Main loop.
    var start = 0;
    var batchSize = 200;
    var threads, thread, numProcessed = 0;
    var keepLooping = true;

    var filename = sheet.getParent().getName()
    Logger.log("Writing results to %s", filename)
    while (keepLooping) {
        // Run search
        Logger.log("Fetching results from %s to %s", start, start + batchSize)
        threads = GmailApp.search(searchQuery, start, batchSize)
        Logger.log("Got %s results", threads.length)

        if (threads.length > 0) {
            for (var i = 0; i < threads.length; i++) {
                thread = threads[i]

                // Add a row to the sheet.
                sheet.appendRow(generateRowFn(thread))

                numProcessed += 1
                keepLooping = breakLoopFn(thread)
                if (!keepLooping) break
            } 
            // If the number of results is less than the batch size then we're
            // on the last page.
            keepLooping = threads.length == batchSize
        } else {
            keepLooping = false;
        }
        start += batchSize;
    }

    Logger.log("Processing finished — processed %s results", numProcessed)
}