Extract data from Drive PDFs into a Sheet

// Registers new PDFs from a Drive folder in a Sheet and tries to read fields out of their text.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, switch on the Drive API
// advanced service (Services > Add a service > Drive API, v3), then run installTriggers() once.

const CONFIG = {
  WATCH_FOLDER_ID: 'PASTE_THE_DRIVE_FOLDER_ID_HERE',
  SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'PDF register',
  WINDOW_DAYS: 14,                 // only files changed in this many days are looked at
  MAX_FILES_PER_RUN: 8,            // reading a PDF takes seconds, so keep this small
  MAX_READ_ATTEMPTS: 3,            // a file that fails to convert is retried this many runs before it is marked Could not read
  MAX_READ_BYTES: 2000000,         // Google's help page says the file should be 2 MB or smaller
  // 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,
  // Each field is a regular expression with ONE bracketed group around the value to keep.
  FIELDS: [
    { name: 'Document number', pattern: '(?:invoice|order)\\s*(?:no\\.?|number|#)\\s*[:\\-]?\\s*([A-Z0-9\\-]*[0-9][A-Z0-9\\-]*)' },
    { name: 'Total', pattern: '\\btotal\\s*(?:due|amount)?\\s*[:\\-]?\\s*\\$?\\s*([0-9][0-9,]*\\.[0-9]{2})' },
  ],
  ALERT_TO: 'ops@example.com',
  ALERT_EVERY_MS: 6 * 60 * 60 * 1000,
};

const FIXED_HEADERS = ['Added', 'File', 'Link', 'Status', 'Notes'];
const NEEDS_A_PERSON = ['Check by hand', 'No text found', 'Too large to read', 'Could not read'];

function registerPdfs() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;
  const started = Date.now();
  const problems = [];
  const props = PropertiesService.getScriptProperties();
  let attempts = {};
  try { attempts = JSON.parse(props.getProperty('readAttempts') || '{}'); } catch (e) { attempts = {}; }
  try {
    const sheet = getSheet_();
    const idCol = FIXED_HEADERS.length + CONFIG.FIELDS.length + 1; // 1-based position of 'File ID'
    const lastRow = sheet.getLastRow();
    // Read the register once and index it in memory.
    const seen = {};
    if (lastRow > 1) {
      sheet.getRange(2, idCol, lastRow - 1, 1).getValues().forEach(function (r) { seen[r[0]] = true; });
    }
    const since = Utilities.formatDate(new Date(Date.now() - CONFIG.WINDOW_DAYS * 24 * 60 * 60 * 1000), 'UTC', 'yyyy-MM-dd');
    const folder = DriveApp.getFolderById(CONFIG.WATCH_FOLDER_ID);
    // The Drive service takes v2 style search terms, hence modifiedDate.
    const files = folder.searchFiles('mimeType = "application/pdf" and modifiedDate > "' + since + '"');
    const rows = [];
    while (files.hasNext() && rows.length < CONFIG.MAX_FILES_PER_RUN) {
      if (Date.now() - started > CONFIG.TIME_BUDGET_MS) break; // the next run picks up the rest
      const file = files.next();
      const id = 'f:' + file.getId(); // the prefix keeps Sheets from reading it as a number
      if (seen[id]) continue;
      const row = describe_(file, id, attempts, problems);
      if (row) { rows.push(row); seen[id] = true; }
    }
    // One write for the whole run. A file is registered even when it could not be read,
    // so it is never dropped: its Status says what a person has to do.
    if (rows.length) {
      sheet.getRange(lastRow + 1, 1, rows.length, idCol).setValues(rows);
    }
  } catch (err) {
    problems.push('Run failed: ' + err.message);
  } finally {
    try { props.setProperty('readAttempts', JSON.stringify(attempts)); } catch (e) { /* only a retry counter */ }
    lock.releaseLock();
  }
  alertOnce_(problems);
}

// Returns the register row, or null when the file should be tried again on the next run.
function describe_(file, id, attempts, problems) {
  const base = [new Date(), safe_(file.getName()), file.getUrl()];
  const blank = CONFIG.FIELDS.map(function () { return ''; });
  if (file.getSize() > CONFIG.MAX_READ_BYTES) {
    return base.concat(['Too large to read', 'Larger than ' + Math.round(CONFIG.MAX_READ_BYTES / 1000000) + ' MB'], blank, [id]);
  }
  try {
    const text = readText_(file);
    if (!text.trim()) return base.concat(['No text found', 'Probably a scan Google could not read'], blank, [id]);
    const found = CONFIG.FIELDS.map(function (f) {
      const m = new RegExp(f.pattern, 'i').exec(text);
      return m ? safe_(m[1]) : '';
    });
    const missing = CONFIG.FIELDS.filter(function (f, i) { return found[i] === ''; }).map(function (f) { return f.name; });
    delete attempts[id];
    return base.concat(missing.length ? ['Check by hand', 'Not found: ' + missing.join(', ')] : ['Extracted', ''], found, [id]);
  } catch (err) {
    // The Drive service not being switched on is a set-up fault, not a problem with this file.
    if (err.name === 'ReferenceError') throw new Error('Switch on the Drive API service (Services, Add a service, Drive API, v3)');
    // Quota and service errors are usually temporary, so leave the file for the next run and
    // only give up on it after MAX_READ_ATTEMPTS tries. Either way a person is told.
    attempts[id] = (attempts[id] || 0) + 1;
    problems.push(file.getName() + ' (try ' + attempts[id] + ' of ' + CONFIG.MAX_READ_ATTEMPTS + '): ' + err.message);
    if (attempts[id] < CONFIG.MAX_READ_ATTEMPTS) return null;
    delete attempts[id];
    return base.concat(['Could not read', safe_(err.message)], blank, [id]);
  }
}

// Google turns the PDF into a temporary Google Doc (it reads the text), we take the text, then bin the Doc.
function readText_(file) {
  const temp = Drive.Files.create(
    { name: 'tmp-pdf-read-' + file.getId(), mimeType: MimeType.GOOGLE_DOCS },
    file.getBlob(),
    { fields: 'id' }
  );
  try {
    return DocumentApp.openById(temp.id).getBody().getText();
  } finally {
    DriveApp.getFileById(temp.id).setTrashed(true);
  }
}

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) {
    const headers = FIXED_HEADERS.concat(CONFIG.FIELDS.map(function (f) { return f.name; }), ['File ID']);
    sheet.appendRow(headers);
  }
  return sheet;
}

// Stops text taken from a PDF or a file name, like "=IMPORTXML(...)", running as a formula.
function safe_(value) {
  const s = String(value);
  return /^[=+@\t\r-]/.test(s) ? "'" + s : s;
}

// Keeps the held-back problems in a script property. Oldest entries are dropped first if the text
// would get long (a property value is limited to about 9 KB), and a failure to store never fails the run.
function saveQueue_(props, queue) {
  try {
    while (queue.length > 1 && JSON.stringify(queue).length > 6000) queue.shift();
    props.setProperty('pendingProblems', JSON.stringify(queue));
  } catch (e) { /* the problems are lost only if the store itself is down */ }
}

// Emails the problems to ALERT_TO, at most once every ALERT_EVERY_MS. Problems found while an email is
// being held back are kept in a script property and go out with the next one, so each is reported.
function alertOnce_(problems, force) {
  const props = PropertiesService.getScriptProperties();
  let queue = [];
  try { queue = JSON.parse(props.getProperty('pendingProblems') || '[]'); } catch (e) { queue = []; }
  (problems || []).forEach(function (p) {
    const text = String(p).slice(0, 160);
    if (queue.indexOf(text) === -1) queue.push(text);
  });
  queue = queue.slice(-30);
  if (!queue.length) return;
  const last = Number(props.getProperty('lastAlertAt') || 0);
  if (!force && Date.now() - last < CONFIG.ALERT_EVERY_MS) {
    saveQueue_(props, queue);
    return;
  }
  try {
    MailApp.sendEmail(CONFIG.ALERT_TO, 'PDF register needs attention (' + queue.length + ')', queue.join('\n'));
    props.setProperty('lastAlertAt', String(Date.now()));
    props.deleteProperty('pendingProblems');
  } catch (err) {
    saveQueue_(props, queue); // keep them for the next try
  }
}

// Daily email: how many PDFs still need a person. Silent when there are none.
function dailySummary() {
  try {
    const sheet = getSheet_();
    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;
    let waiting = 0;
    sheet.getRange(2, 4, lastRow - 1, 1).getValues().forEach(function (r) {
      if (NEEDS_A_PERSON.indexOf(r[0]) !== -1) waiting++;
    });
    if (!waiting) return;
    MailApp.sendEmail(
      CONFIG.ALERT_TO,
      'PDFs waiting for a person: ' + waiting,
      'Open the register: ' + sheet.getParent().getUrl() +
        '\nFix the values by hand, then change Status to Done.'
    );
  } catch (err) {
    alertOnce_(['Daily summary failed: ' + err.message]);
  }
}

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