Capture supplier invoices from Gmail into Google Sheets

// Saves supplier invoice PDFs from Gmail to Drive and logs each one in a Sheet.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTriggers() once.
// It captures and registers invoices. Reading line items out of the PDF is a separate step.

const CONFIG = {
  SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'Invoice register',
  ROOT_FOLDER_ID: 'PASTE_THE_DRIVE_FOLDER_ID_HERE',
  DONE_LABEL: 'Invoice-logged',                  // created automatically
  WINDOW_DAYS: 14,                               // how far back to look
  MAX_THREADS: 25,                               // per run
  // Stop well before the 6 minute limit. On a gmail.com account the whole day's
  // trigger time is 90 minutes, so lower this (for example 60 * 1000) there.
  TIME_BUDGET_MS: 4 * 60 * 1000,
  ALERT_TO: 'accounts@example.com',
  ALERT_EVERY_MS: 6 * 60 * 60 * 1000,            // at most one failure email per 6 hours
};

const HEADERS = ['Received', 'Supplier', 'Subject', 'File', 'Drive link', 'Status', 'Attachment ID', 'Duplicate key'];
const STATUS_COL = 6;   // 1-based
const ATTACH_COL = 7;

function captureInvoices() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;
  const started = Date.now();
  const problems = [];
  try {
    const sheet = getSheet_();
    const lastRow = sheet.getLastRow();
    // Read the register once and index it in memory.
    const seenAttachments = {};
    const seenDuplicates = {};
    if (lastRow > 1) {
      sheet.getRange(2, ATTACH_COL, lastRow - 1, 2).getValues().forEach(function (r) {
        seenAttachments[r[0]] = true;
        seenDuplicates[r[1]] = true;
      });
    }
    const me = Session.getEffectiveUser().getEmail().toLowerCase();
    const oldest = Date.now() - CONFIG.WINDOW_DAYS * 24 * 60 * 60 * 1000;
    const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
    const label = GmailApp.getUserLabelByName(CONFIG.DONE_LABEL) || GmailApp.createLabel(CONFIG.DONE_LABEL);
    const query = 'has:attachment filename:pdf newer_than:' + CONFIG.WINDOW_DAYS + 'd -label:' + CONFIG.DONE_LABEL;
    const threads = GmailApp.search(query, 0, CONFIG.MAX_THREADS);
    const monthFolders = {};
    const rows = [];
    const finished = [];

    for (const thread of threads) {
      if (Date.now() - started > CONFIG.TIME_BUDGET_MS) break; // the next run picks up the rest
      try {
        for (const message of thread.getMessages()) {
          const from = message.getFrom();
          const when = message.getDate();
          // Skip old messages in a long thread and anything you sent yourself.
          if (when.getTime() < oldest || (me && from.toLowerCase().indexOf(me) !== -1)) continue;
          const attachments = message.getAttachments({ includeInlineImages: false });
          attachments.forEach(function (att, i) {
            if (!isPdf_(att)) return;
            // Prefixes keep Sheets from reading these as numbers or formulas.
            const attachmentId = 'm:' + message.getId() + ':' + i;
            if (seenAttachments[attachmentId]) return;
            const file = saveFile_(root, monthFolders, att, when, message.getId(), i);
            const dupKey = 'k|' + from.toLowerCase() + '|' + att.getName() + '|' + att.getSize();
            const status = seenDuplicates[dupKey] ? 'Possible duplicate' : 'To review';
            rows.push([when, safe_(from), safe_(message.getSubject()), safe_(file.getName()), file.getUrl(), status, attachmentId, dupKey]);
            seenAttachments[attachmentId] = true;
            seenDuplicates[dupKey] = true;
          });
        }
        finished.push(thread); // labelled only when the whole thread went through
      } catch (err) {
        problems.push(subjectOf_(thread) + ' -> ' + err.message);
      }
    }
    // Good rows are written even if another thread failed. A failed thread is not
    // labelled, so the next run retries it and skips anything already logged.
    if (rows.length) {
      sheet.getRange(lastRow + 1, 1, rows.length, HEADERS.length).setValues(rows);
    }
    finished.forEach(function (t) { t.addLabel(label); });
  } catch (err) {
    problems.push('Run failed: ' + err.message);
  } finally {
    lock.releaseLock();
  }
  alertOnce_(problems);
}

function isPdf_(att) {
  return att.getContentType() === 'application/pdf' || /\.pdf$/i.test(att.getName() || '');
}

function saveFile_(root, cache, att, when, messageId, index) {
  const zone = Session.getScriptTimeZone();
  const month = Utilities.formatDate(when, zone, 'yyyy-MM');
  if (!cache[month]) {
    const found = root.getFoldersByName(month);
    cache[month] = found.hasNext() ? found.next() : root.createFolder(month);
  }
  const day = Utilities.formatDate(when, zone, 'yyyy-MM-dd');
  const name = day + '_' + messageId.slice(-6) + '-' + (index + 1) + '_' + (att.getName() || 'invoice.pdf');
  const existing = cache[month].getFilesByName(name);
  // Reuse the file if an earlier run saved it but stopped before logging the row.
  return existing.hasNext() ? existing.next() : cache[month].createFile(att.copyBlob().setName(name));
}

function getSheet_() {
  const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  const sheet = ss.getSheetByName(CONFIG.SHEET_NAME) || ss.insertSheet(CONFIG.SHEET_NAME);
  if (sheet.getLastRow() === 0) sheet.appendRow(HEADERS);
  return sheet;
}

// Stops sender-controlled text like "=IMPORTXML(...)" running as a formula.
function safe_(value) {
  return /^[=+@\t\r-]/.test(value) ? "'" + value : value;
}

// A broken thread must not stop the report on it, so never let this throw.
function subjectOf_(thread) {
  try {
    return thread.getFirstMessageSubject();
  } catch (err) {
    return '(unreadable thread)';
  }
}

function alertOnce_(problems) {
  if (!problems.length) return;
  const props = PropertiesService.getScriptProperties();
  const last = Number(props.getProperty('lastAlertAt') || 0);
  if (Date.now() - last < CONFIG.ALERT_EVERY_MS) return;
  MailApp.sendEmail(
    CONFIG.ALERT_TO,
    'Invoice capture needs attention (' + problems.length + ')',
    problems.join('\n')
  );
  props.setProperty('lastAlertAt', String(Date.now()));
}

// Daily email: how many invoices still need a person. Silent when there are none.
function dailySummary() {
  const sheet = getSheet_();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  let review = 0;
  let duplicates = 0;
  sheet.getRange(2, STATUS_COL, lastRow - 1, 1).getValues().forEach(function (r) {
    if (r[0] === 'To review') review++;
    if (r[0] === 'Possible duplicate') duplicates++;
  });
  if (!review && !duplicates) return;
  MailApp.sendEmail(
    CONFIG.ALERT_TO,
    'Invoices waiting: ' + review + ' to review, ' + duplicates + ' possible duplicates',
    'Open the register: ' + SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID).getUrl() +
      '\nChange Status to Done once an invoice is entered.'
  );
}

// Run once by hand. Safe to run again: it replaces the old triggers.
function installTriggers() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return ['captureInvoices', 'dailySummary'].indexOf(t.getHandlerFunction()) !== -1; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('captureInvoices').timeBased().everyMinutes(10).create();
  ScriptApp.newTrigger('dailySummary').timeBased().everyDays(1).atHour(8).create();
}