How to Extract PDF Receipts to Google Sheets or QuickBooks

Receipt extraction automation is a pipeline that ingests PDFs or image receipts, converts them to text, parses structured fields, validates against mapping rules, and writes clean rows into Google Sheets or accounting imports like QuickBooks or Xero.

We built and shipped this for accounting teams and operators who were re-keying receipts, bank confirmations, and supplier invoices. In production it pulls files from email or Drive, extracts totals, dates, taxes, and vendors, dedupes against a ledger, and lands clean rows ready for reconciliation or CSV import.

The problem it solves

Manual receipt entry means saving attachments, opening each PDF, squinting for the total and tax, typing vendors that never match accounting names, and hoping the right GL is chosen. Errors creep in when two receipts look similar or the decimal mark is locale-formatted. A single month produces hundreds of near-identical clicks.

We saw this play out first-hand when a third-party PDF service expired and an accounting firm's receipt extraction collapsed overnight. We refactored the fleet to native PDF handling and removed the subscription risk. The clicks disappeared and failures stopped being a surprise.

How the automation works

The system is a staged conveyor: intake from email or Drive, a conversion and parsing engine, normalization with a vendor map, and output to a Sheet and accounting-ready CSVs.

• Intake watchers: A Gmail label or forwarding rule routes invoices and receipts to a single Drive folder. Files dropped in subfolders inherit the same flow, which keeps client gates simple.
• Conversion and parsing engine: Google Drive converts PDFs to Google Docs so we can read text. Deterministic patterns extract totals, tax, currency, and dates. A fallback handles image-heavy scans.
• Normalization and validation: A mapping table fixes vendor naming, sets GL and tax codes, and enforces currency and locale parsing. A dedupe ledger prevents double-posts.
• Destinations: Clean rows land in a Google Sheet for review. The same rows serialize to CSV matching the import templates used by QuickBooks or Xero so posting does not depend on a live API.

Step-by-step: how to build it

1) Route receipts to a watched Drive folder

Set a Gmail filter that labels vendor emails and auto-forwards attachments to a Google Apps Script web app or saves them to Drive. A simple Apps Script can also pull attachments from a label and save them on a schedule.

// Apps Script: save attachments from a label to Drive
function saveLabeledAttachments() {
  const LABEL = 'receipts-auto';
  const DEST_FOLDER_ID = PropertiesService.getScriptProperties().getProperty('RECEIPTS_FOLDER');
  const folder = DriveApp.getFolderById(DEST_FOLDER_ID);
  const threads = GmailApp.search(`label:${LABEL} has:attachment newer_than:7d`);
  threads.forEach(t => t.getMessages().forEach(m => m.getAttachments()
    .filter(a => /\.(pdf|jpg|jpeg|png)$/i.test(a.getName()))
    .forEach(a => folder.createFile(a))));
}

Key gotcha: Gmail quotas and Apps Script time limits apply. Batch by date window and keep the filter narrow.

2) Convert PDFs to Google Docs for OCR

Drive's native conversion handles most PDFs and image receipts. Convert each PDF to a Google Doc, then read its text back.

// Apps Script Advanced Drive service required (enable in Services)
function pdfToText(fileId) {
  const file = Drive.Files.copy({
    title: 'OCR-' + new Date().toISOString(),
    mimeType: 'application/vnd.google-apps.document'
  }, fileId);
  const doc = DocumentApp.openById(file.id);
  const text = doc.getBody().getText();
  Drive.Files.trash(file.id); // cleanup temp Doc
  return text;
}

Key gotcha: enable the Advanced Drive service in Apps Script or the copy call fails. For very poor scans, consider keeping the original file reference and flagging for human review.

3) Parse totals, taxes, currency, and dates

Use conservative regex with locale-aware fallbacks. Keep patterns explicit and layered to avoid false positives.

function parseFields(txt) {
  const clean = txt.replace(/[\u00A0\t]/g, ' ').replace(/\s+/g, ' ').trim();
  const currency = (clean.match(/\b(USD|CAD|EUR|GBP)\b/i) || [,'USD'])[1].toUpperCase();
  const total = pickNumber(clean, /(grand\s*total|total\s*due|amount\s*due)[:\s]*([$€£]?)([0-9.,]+)/i);
  const tax = pickNumber(clean, /(tax|vat|hst|gst)[:\s]*([$€£]?)([0-9.,]+)/i);
  const date = pickDate(clean);
  return { currency, total, tax, date };
}
 
function pickNumber(s, re){
  const m = s.match(re); if(!m) return null;
  const raw = m[3];
  // Normalize 1.234,56 vs 1,234.56
  const norm = /,\d{2}$/.test(raw) && !/\.\d{2}$/.test(raw)
    ? raw.replace(/\./g,'').replace(',', '.')
    : raw.replace(/,/g, '');
  return Number(norm);
}
 
function pickDate(s){
  const m = s.match(/(\d{4}[-\/.]\d{1,2}[-\/.]\d{1,2}|\d{1,2}[-\/.]\d{1,2}[-\/.]\d{2,4}|\b\w{3,9}\s+\d{1,2},\s*\d{4})/);
  return m ? new Date(m[1]) : null;
}

Key gotcha: European formats will trip naive parsers. Normalize by detecting the decimal mark from the tail of the number before stripping separators.

4) Normalize vendors and map GL and tax codes

Keep a Sheet tab called Vendors with columns: pattern, vendor_name, gl_account, tax_code, currency. Resolve each extracted vendor line to a canonical record.

function vendorMap(vendorText){
  const sheet = SpreadsheetApp.getActive().getSheetByName('Vendors');
  const rows = sheet.getDataRange().getValues().slice(1);
  const hit = rows.find(r => new RegExp(r[0], 'i').test(vendorText));
  return hit ? { vendor: rstr(hit[1]), gl: rstr(hit[2]), tax: rstr(hit[3]), cur: rstr(hit[4]) } : null;
}
function rstr(x){ return (x||'').toString().trim(); }

Key gotcha: do not rely on exact matches. Real PDFs vary vendor casing and punctuation. Regex patterns in the map handle this without code changes.

5) Dedupe and validate

Prevent double posting by hashing a stable key like vendor:date:amount. Store that in a ledger tab before appending new rows.

function seenBefore(key){
  const sh = SpreadsheetApp.getActive().getSheetByName('Ledger');
  const set = new Set(sh.getDataRange().getValues().slice(1).map(r => r[0]));
  return set.has(key);
}
function appendLedger(key){
  const sh = SpreadsheetApp.getActive().getSheetByName('Ledger');
  sh.appendRow([key, new Date()]);
}

Key gotcha: hash collisions are rare but possible if you only use amount. Include vendor and date and optionally the file checksum from Drive to make it robust.

6) Write to Sheets and generate QuickBooks or Xero CSV

Write normalized rows to a Review tab for a human glance, then export a CSV that matches the target platform's import template. This keeps posting decoupled from API availability.

function writeRow(rec){
  const sh = SpreadsheetApp.getActive().getSheetByName('Review');
  const row = [rec.date, rec.vendor, rec.total, rec.tax, rec.currency, rec.gl, rec.memo, rec.sourceFileId];
  sh.appendRow(row);
}
 
function exportCsv(){
  const sh = SpreadsheetApp.getActive().getSheetByName('Review');
  const rows = sh.getDataRange().getValues();
  const csv = rows.map(r => r.map(v => '"' + String(v).replace(/"/g,'""') + '"').join(',')).join('\n');
  const folder = DriveApp.getFolderById(PropertiesService.getScriptProperties().getProperty('EXPORT_FOLDER'));
  folder.createFile('receipts-export-' + new Date().toISOString().slice(0,10) + '.csv', csv, MimeType.CSV);
}

Key gotcha: QuickBooks and Xero import templates expect column order and formatting to match their sample files. Load a sample into your Review tab header and keep that header fixed.

Where it gets complicated

Third-party OCR outages. In one production fleet a vendor PDF service expired and every receipt scenario failed. We refactored to native PDF conversion inside Google Drive to remove the subscription as a single point of failure.

Image-heavy scans and low DPI. Drive conversion handles many cases, but very low-quality scans merge characters and drop separators. Flag low-confidence parses, attach the original file ID, and route to a review queue.

Locale and currency traps. Parsing 1.234,56 correctly requires detecting the decimal character from context. Do not hardcode separators. Always store currency explicitly.

Ambiguous vendor names. Many receipts use brand banners or parent company names. Use a mapping table with regex patterns and review any new unknown vendor before allowing autopost.

Password-protected statements. Some bank confirmations are locked. Detect the error and request an unlocked copy. Do not try to break protection in automation.

Make.com module drift. If you run parts of this in Make.com, replacing modules without rewiring templated references can silently break flows. Audit downstream references after a blueprint change and pace PATCHes to avoid 429s.

What this actually changes

For a St. Martin accounting firm we stabilized receipt and bank-statement extraction by moving from a brittle external PDF dependency to native conversion and deterministic parsing. The team stopped firefighting expired-token outages and returned to exception-only reviews instead of line-by-line entry.

One external benchmark helps frame the value: the GBTA Foundation found the average expense report costs 58 dollars to process and takes 20 minutes, with corrections adding 18 minutes and 52 dollars more on average (GBTA, The Total Cost of Processing Expense Reports, 2015: https://www.gbta.org). Your receipts are a subset of that flow. Shifting the repetitive entry to an automated pipeline while keeping humans on exceptions is why this pays back quickly.

Frequently asked questions

Can this work without paying for a separate OCR tool?

Yes. For many PDFs and image receipts, converting to a Google Doc and reading the text works well. We only add a paid OCR when the source quality or language mix makes native conversion unreliable at volume.

Will it handle scanned photos from a phone?

Often, yes. Low light, angle, or very low DPI can reduce accuracy. We route low-confidence extractions to a human queue and retain the original file so a reviewer can correct the record in seconds.

Can it post directly into QuickBooks or Xero?

We usually generate a CSV matching each platform's import template and let a staffer import in one action. Direct API posting is possible when appropriate, but CSV keeps you resilient to credentials and endpoint changes and is easy to audit.

How do you prevent duplicates?

We hash a stable key like vendor:date:amount and store it in a ledger. Before writing a new row we check the ledger. We also keep the original file ID for a second guard and traceability.

What does this cost monthly to run?

Infrastructure is minimal. Google Apps Script and Drive are usually within free or existing workspace limits. If you add a paid OCR, it is typically a usage fee. The primary cost is the initial build and occasional updates to the vendor map.

How long does the setup take?

A straightforward pipeline with Drive intake, parsing, normalization, and CSV export typically takes 3 to 7 business days, plus a short shadow period to tune patterns against your real receipts.

If you want this running against your inbox and Drive with a clean handoff to QuickBooks or Xero, we have shipped this exact system. See our related piece on automating QuickBooks bank imports and our document automation services. When you are ready, book a 15 minute call and we will map your intake and posting rules.