Google Apps Script: Auto-Forward Emails to Xero

Auto-Forward-Invoice-to-Xero

/*
 * Google Apps Script: Auto-Forward Emails to Xero
 * Author: Lukas Gibb (Written with help from ChatGPT o1)
 * Email: lg@cloudjourneyman.com
 * Credit for idea: Reddit User 'zfa' (https://www.reddit.com/user/zfa/) mentioned this idea here:
 *    https://www.reddit.com/r/GMail/comments/hk4gc1/comment/fwrcghc/
 *
 * Purpose:
 *  Xero (accounting tool) allows PDF invoices to be forwarded manually to create draft invoices.
 *  Doing this one-by-one can be time consuming and tedious so it would be great to just set up filters
 *  to do it however Xero do not currently allow a method to click the verification link or retrieve
 *  the code that GMail sends to verify you are allowed to forward email to your xerofiles.com address.
 *  This means you cannot automatically forward emails to Xero as they come in. With this script you 
 *  can save some time by adding a label to the emails you want to forward (manually or with a filter)
 *  and then forwarding them all by running this script.
 *
 * Description:
 *  This script automatically forwards Gmail messages that meet the following criteria to Xero:
 *   1. They have a specific label (eg. "ForwardToXero").
 *   2. They contain at least one PDF attachment.
 *   3. The message is currently in the Inbox.
 *
 * The script then:
 *   - Removes the ForwardToXero label.
 *   - Applies an "Accounts Payable" label (configurable).
 *   - Archives the thread.
 *   - Sends a summary report via email listing forwarded/skipped messages.
 *
 * Setup & Usage:
 *   1. Create labels "ForwardToXero" and "Reference/- Accounts Payable" in Gmail.
 *   2. Add the "ForwardToXero" label (manually or via a filter) to messages you want forwarded to Xero.
 *   3. Update the configuration constants (DRY_RUN, XERO_EMAIL, REPORT_RECIPIENT) as needed.
 *   4. Go to https://script.google.com/home, create a new project and copy this script in and save it.
 *   5. Run the script. Check the execution log and your email for the forwarding report.
 *   6. If DRY_RUN = true, it's only a simulation. Set DRY_RUN = false to actually forward.
 *
 *  Notes:
 *   - Uses message.forward(recipient, options) to preserve the conversation thread.
 *   - Embeds a custom HTML block that includes the original From/Date/Subject/To in the forwarded mail.
 *   - If you have aliases set up in your gmail. The script will look for the original "To:" address
 *      in your valid aliases to determine the "from" address when forwarding.
 *   - If no matching alias is found in the "To:" field, the message is skipped.
 *   - If no PDF is found, the message is skipped.
 *   - If the message is not in the Inbox, it is skipped.
 *
 *  Support:
 *   - For further customization or issues, see Google Apps Script docs or contact the script author.
 */

function forwardInvoicesToXero() {
    // If DRY_RUN is true, we simulate actions without actually forwarding.
    var DRY_RUN = true;

    // Configuration
    var FORWARD_LABEL = "ForwardToXero";
    var ACCOUNTS_PAYABLE_LABEL = "Reference/- Accounts Payable";
    var XERO_EMAIL = "your.xero.address@xerofiles.com";
    var REPORT_RECIPIENT = "youraccount@gmail.com";

    Logger.log("Starting script execution...");

    // Retrieve all valid alias addresses (including primary)
    var validAliases = GmailApp.getAliases();
    validAliases.push(Session.getActiveUser().getEmail());
    Logger.log("Valid 'From' aliases: " + JSON.stringify(validAliases, null, 2));

    var gmail = GmailApp;
    var label = gmail.getUserLabelByName(FORWARD_LABEL);
    var accountsPayableLabel = gmail.getUserLabelByName(ACCOUNTS_PAYABLE_LABEL);

    // Fail early if labels are missing
    if (!label || !accountsPayableLabel) {
        var missingLabels = [];
        if (!label) missingLabels.push(`"${FORWARD_LABEL}"`);
        if (!accountsPayableLabel) missingLabels.push(`"${ACCOUNTS_PAYABLE_LABEL}"`);

        var errorMessage = `Error: Missing labels: ${missingLabels.join(", ")}. Please create them.`;
        Logger.log(errorMessage);
        gmail.sendEmail(REPORT_RECIPIENT, "Xero Forwarding Script Error", errorMessage);
        return;
    }

    Logger.log(`Labels found: "${FORWARD_LABEL}", "${ACCOUNTS_PAYABLE_LABEL}"`);

    // Retrieve threads with the ForwardToXero label
    var threads = label.getThreads();
    Logger.log(`Total threads with label "${FORWARD_LABEL}": ${threads.length}`);

    // Exit early if no threads found - don't send report
    if (threads.length === 0) {
        Logger.log("No threads found with the specified label. Exiting without sending report.");
        return;
    }

    var forwardedEmails = [];
    var skippedEmails = [];
    var actionTaken = DRY_RUN ? "SIMULATED" : "FORWARDED";

    for (var i = 0; i < threads.length; i++) {
        var thread = threads[i];
        var messages = thread.getMessages();
        Logger.log(`Processing thread ${i + 1} with ${messages.length} message(s)...`);

        for (var j = 0; j < messages.length; j++) {
            var message = messages[j];

            // Basic info for reporting
            var emailLink = `https://mail.google.com/mail/u/0/#inbox/${message.getId()}`;
            var formattedDate = Utilities.formatDate(message.getDate(), Session.getScriptTimeZone(), "yyyy-MM-dd");
            var subject = message.getSubject();
            var fromAddress = message.getFrom();

            // For the forwarded info block, let's do a more verbose date/time
            var dateString = Utilities.formatDate(message.getDate(), Session.getScriptTimeZone(), "EEE, dd MMM yyyy 'at' HH:mm");

            // We'll sanitize the fromAddress for HTML (in case of angle brackets)
            var sanitizedFrom = fromAddress
              .replace(/</g, "&lt;")
              .replace(/>/g, "&gt;");

            // Extract a display name for the 'From' address in the final report
            var senderNameMatch = fromAddress.match(/^(.*?)(?: <[^>]+>)?$/);
            var senderName = senderNameMatch && senderNameMatch[1] ? senderNameMatch[1] : "No Name";

            // The message was originally sent to (possibly multiple addresses)
            // We'll see if ANY of them are in validAliases.
            var toField = message.getTo(); // e.g. "Jane Doe <jane@mydomain.com>, info@mydomain.com"

            // We'll also sanitize the toField for display
            var sanitizedTo = toField
              .replace(/</g, "&lt;")
              .replace(/>/g, "&gt;");

            // Attempt to find a match in validAliases.
            var fromAlias = null;

            // If there are multiple recipients, we split them.
            var possibleRecipients = toField.split(/,/).map(function(r){
              return r.trim();
            });

            // For each recipient, see if we can parse out the address and match it to an alias.
            for (var r = 0; r < possibleRecipients.length; r++) {
                var rec = possibleRecipients[r];
                // Attempt to parse the actual email out of something like "Name <email@domain>".
                var match = rec.match(/<([^>]+)>/);
                var recAddress = match ? match[1] : rec;

                // If this address is one of the valid aliases, we pick it.
                if (validAliases.includes(recAddress)) {
                    fromAlias = recAddress;
                    break;
                }
            }

            Logger.log(`Checking message ${j + 1}: Subject: "${subject}", From: "${fromAddress}"; Found alias in To: "${fromAlias}"`);

            if (message.isInInbox()) {
                var attachments = message.getAttachments();
                Logger.log(`Message has ${attachments.length} attachment(s).`);

                // Check for PDF attachments
                var hasPDF = attachments.some(function(att) {
                    var contentType = att.getContentType();
                    var fileName = att.getName().toLowerCase();
                    var isPDF = contentType.includes("pdf") || fileName.endsWith(".pdf");
                    Logger.log(`Checking attachment: "${att.getName()}" - MIME Type: ${contentType}, Is PDF: ${isPDF}`);
                    return isPDF;
                });

                Logger.log(`Final PDF detection result: ${hasPDF}`);

                if (hasPDF) {
                    // If we found no matching alias in the To: field, skip.
                    if (!fromAlias) {
                        var reasonAlias = "No matching Gmail alias found in To: " + toField;
                        Logger.log(reasonAlias);
                        skippedEmails.push({
                            date: formattedDate,
                            senderName: senderName,
                            fromAddress: fromAddress,
                            subject: subject,
                            reason: reasonAlias,
                            link: emailLink
                        });
                        continue;
                    }

                    try {
                        if (!DRY_RUN) {
                            Logger.log(`Forwarding email using from=${fromAlias} to ${XERO_EMAIL}...`);

                            // We'll build a custom HTML block with the original headers
                            // Then append the original message HTML.
                            var forwardedInfoBlock = [
                                "Forwarded from 'Auto-Forward-Invoice-to-Xero' Google Apps Script<br><br>",
                                "---------- Forwarded message ---------<br>",
                                `From: ${sanitizedFrom}<br>`,
                                `Date: ${dateString}<br>`,
                                `Subject: ${subject.replace(/</g, "&lt;").replace(/>/g, "&gt;")}<br>`,
                                `To: ${sanitizedTo}<br><br>`
                            ].join("");

                            var customHtmlBody = forwardedInfoBlock + message.getBody();

                            // Use message.forward(recipient, options) - preserves the thread
                            message.forward(XERO_EMAIL, {
                                from: fromAlias,
                                subject: "Fwd: " + subject,
                                body: "(Your client does not support HTML)", // fallback text
                                htmlBody: customHtmlBody,
                                attachments: attachments
                            });

                            // Remove the ForwardToXero label, add ACCOUNTS_PAYABLE_LABEL, move thread to archive
                            thread.removeLabel(label);
                            thread.addLabel(accountsPayableLabel);
                            thread.moveToArchive();

                        } else {
                            Logger.log(`SIMULATED: Would have forwarded email from ${fromAlias} to ${XERO_EMAIL}.`);
                        }

                        // Record successful forward
                        forwardedEmails.push({
                            date: formattedDate,
                            senderName: senderName,
                            fromAddress: fromAddress,
                            subject: subject,
                            link: emailLink
                        });
                    } catch (error) {
                        Logger.log(`Error forwarding email: ${error.message}`);
                        skippedEmails.push({
                            date: formattedDate,
                            senderName: senderName,
                            fromAddress: fromAddress,
                            subject: subject,
                            reason: `Failed to forward email - ${error.message}`,
                            link: emailLink
                        });
                    }
                } else {
                    // No PDF found
                    skippedEmails.push({
                        date: formattedDate,
                        senderName: senderName,
                        fromAddress: fromAddress,
                        subject: subject,
                        reason: "No PDF attachment found",
                        link: emailLink
                    });
                    Logger.log("Skipping this message: No PDF attachment found.");
                }
            } else {
                // Not in Inbox
                skippedEmails.push({
                    date: formattedDate,
                    senderName: senderName,
                    fromAddress: fromAddress,
                    subject: subject,
                    reason: "Not in Inbox",
                    link: emailLink
                });
                Logger.log("Skipping this message: Not in inbox.");
            }
        }
    }

    // Build the final report
    var reportSubject = `Xero Forwarding Report (${actionTaken} Mode)`;

    var reportBody = `<p>The following emails were ${actionTaken}:</p><ul>` +
        forwardedEmails.map(function(email) {
            return `<li><a href="${email.link}" target="_blank">${email.date}</a> | <a href="mailto:${email.fromAddress}">${email.senderName}</a> | ${email.subject}</li>`;
        }).join("") + `</ul>`;

    if (skippedEmails.length > 0) {
        reportBody += `<p>The following emails were skipped:</p><ul>` +
            skippedEmails.map(function(email) {
                return `<li><a href="${email.link}" target="_blank">${email.date}</a> | <a href="mailto:${email.fromAddress}">${email.senderName}</a> | ${email.subject} | Reason: ${email.reason}</li>`;
            }).join("") + `</ul>`;
    } else {
        reportBody += `<p>No emails were skipped.</p>`;
    }

    // Send the report
    GmailApp.sendEmail(
        REPORT_RECIPIENT,
        reportSubject,
        "",
        {
            htmlBody: reportBody,
            noReply: true
        }
    );

    Logger.log(reportBody);
    Logger.log("Script execution complete.");
}

Thanks very much for this, it's very helpful! I saw it on the Xero forum.

It didn't work for me at first because line 63 only checks for aliases, but not the primary email address. I added the following to make it work: validAliases.push(Session.getActiveUser().getEmail());

Hey Tom, I'm glad it was helpful for you.

I have added your suggestion to the gist. Thanks for taking the time to give me that feedback.

excellent script.
Suggest adding after line 87:

    // Exit early if no threads found - don't send report
    if (threads.length === 0) {
        Logger.log("No threads found with the specified label. Exiting without sending report.");
        return;
    }

(before I made this change I was getting an empty report email every hour!)

excellent script. Suggest adding after line 87:

    // Exit early if no threads found - don't send report
    if (threads.length === 0) {
        Logger.log("No threads found with the specified label. Exiting without sending report.");
        return;
    }

(before I made this change I was getting an empty report email every hour!)

Thanks @ajlowndes. I've added that improvement now. I only run it manually as needed at this stage so this issue hadn't come up. Sounds like you set up a trigger and got it running hourly. Hope it's working well for you. :)