How to Set Up an AI‑Driven Invoice Approval Workflow Using Google Sheets and OpenAI

Why automate invoice approval with AI

Manual invoice handling is error‑prone and slow. By combining Google Sheets as a lightweight database with OpenAI’s language model, you can automatically pull key fields (vendor, amount, date, PO number) from PDFs or images, compare them against rules, and route the record for human sign‑off—all without writing a full‑blown ERP module.

Prerequisites

• A Google account (to create Sheets and Apps Script).
• An OpenAI API key with access to the gpt-4o-mini or gpt-3.5-turbo model (cheaper for high volume).
• A way to get invoice files into Sheets: either upload PDFs to Google Drive and reference the file ID, or use a no‑code connector (Zapier/Make) that watches a folder and adds a row with the file link.
• Basic familiarity with Apps Script (JavaScript‑like) or willingness to copy‑paste the provided code.

Step‑by‑step implementation

1. Design the Sheet

Create a new Google Sheet with these columns (you can add more later):

A – Invoice ID	B – File Link	C – Raw Text	D – Vendor	E – Amount	F – Invoice Date	G – PO Number	H – Status	I – Approver	J – Notes

• Invoice ID: a unique identifier (e.g., timestamp or UUID).
• File Link: URL to the PDF/image stored in Drive.
• Raw Text: placeholder for OCR output (we’ll skip OCR for now and assume you already have plain text; see free OCR alternative below).
• Vendor, Amount, Invoice Date, PO Number: fields the AI will extract.
• Status: values like Pending, Needs Review, Approved, Rejected.
• Approver: email of the person who should act.
• Notes: any flags or comments.

2. Get invoice text into the sheet

If you already have machine‑readable invoices (e.g., exported from an accounting system), you can paste the text directly into column C.

If you need OCR from scanned PDFs or images:

• Free option: Use Google Drive’s built‑in OCR. Right‑click a PDF → Open with → Google Docs. The Docs version contains the text; copy it into column C.
• Paid option (if you prefer automation): Use a service like ocrapispace.com (free tier) or Google Cloud Vision API (pay‑as‑you‑go). Connect via Zapier/Make to write the OCR result into column C whenever a new file lands in a Drive folder.

3. Call OpenAI to extract fields

Add an Apps Script bound to the sheet (Extensions → Apps Script). Paste the following code, replace YOUR_OPENAI_KEY, and save.

const OPENAI_KEY = 'YOUR_OPENAI_KEY';
const OPENAI_ENDPOINT = 'https://api.openai.com/v1/chat/completions';

function extractInvoiceData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const data = sheet.getRange('A2:C' + sheet.getLastRow()).getValues(); // A=ID, B=File, C=RawText
  
  data.forEach((row, i) => {
    const [id, fileLink, rawText] = row;
    if (!rawText || rawText.trim() === '') return; // skip empty
    
    const prompt = `You are an invoice parsing assistant. Extract the following fields from the text below and return a JSON object with keys: vendor, amount, invoice_date, po_number. If a field is not present, set its value to null.
    
    Text:
    ${rawText}
    
    JSON:`;
    
    const payload = {
      model: 'gpt-3.5-turbo',
      messages: [{ role: 'user', content: prompt }],
      temperature: 0
    };
    
    const options = {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: `Bearer ${OPENAI_KEY}` },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };
    
    const response = UrlFetchApp.fetch(OPENAI_ENDPOINT, options);
    const result = JSON.parse(response.getContentText());
    
    let fields = { vendor: null, amount: null, invoice_date: null, po_number: null };
    try {
      const content = result.choices[0].message.content.trim();
      // Expect pure JSON; if extra text, try to locate braces
      const jsonStr = content.match(/\{[\s\S]*\}/)[0];
      fields = JSON.parse(jsonStr);
    } catch (e) {
      Logger.log('Parse error for ID ' + id + ': ' + e);
    }
    
    // Write back to sheet
    sheet.getRange(i + 2, 4, 1, 4) // D-G columns
         .setValues([[fields.vendor, fields.amount, fields.invoice_date, fields.po_number]]);
     
    // Set status to Pending for review
    sheet.getRange(i + 2, 8).setValue('Pending'); // H column
  });
}

What the script does

1. Reads each row where column C holds raw invoice text.
2. Sends a concise prompt to OpenAI asking for JSON output.
3. Parses the response, writes the four fields into columns D–G, and marks the row as Pending.

4. Add simple validation rules

You can flag likely errors without extra AI calls. In column I (Notes) use a formula like:

=IF(OR(ISBLANK(D2), ISBLANK(E2), E2<=0), "Missing vendor or invalid amount", 
 IF(AND(NOT(ISDATE(F2)), NOT(ISBLANK(F2))), "Date format issue", ""))

Drag down; the formula auto‑fills for new rows. If a note appears, change column H (Status) to Needs Review via another Apps Script trigger or a simple conditional formatting rule that copies the note into a helper column and then updates status.

5. Route for human approval

Create a second sheet named Approvals with columns: Invoice ID, Vendor, Amount, Invoice Date, PO Number, Status, Approver Email, Decision, Comments.

Add an Apps Script onEdit trigger that watches column H (Status). When it changes to Pending or Needs Review, copy the row to the Approvals sheet and send an email to the approver (use MailApp.sendEmail).

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Sheet1') return;
  const range = e.range;
  if (range.getColumn() !== 8 || range.getRow() === 1) return; // column H, not header
  
  const status = range.getValue();
  if (status !== 'Pending' && status !== 'Needs Review') return;
  
  const row = range.getRow();
  const data = sheet.getRange(row, 1, 1, 10).getValues()[0]; // A-J
  
  const approvalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Approvals');
  approvalSheet.appendRow([data[0], data[3], data[4], data[5], data[6], data[7], data[8], '', '']);
  
  const approver = data[8]; // column I
  if (approver) {
    MailApp.sendEmail({
      to: approver,
      subject: `Invoice ${data[0]} needs your review`,
      body: `Please review invoice ${data[0]} (Vendor: ${data[3]}, Amount: $${data[4]}). Open the spreadsheet: ${SpreadsheetApp.getActiveSpreadsheet().getUrl()}`
    });
  }
}

6. Close the loop

When the approver edits the Decision column in the Approvals sheet (e.g., Approved or Rejected), another onEdit can write that decision back to the original sheet’s Status column and add any comments.

7. Free / cheapest alternatives

• OpenAI: Use the gpt-3.5-turbo model (cheapest). If you need zero cost, replace the AI call with a rule‑based parser using regular expressions (works only for standardized invoice layouts).
• OCR: Google Drive’s built‑in OCR is free; alternatively, use Tesseract OCR via a free Google Cloud Function (you pay only for execution time).
• Automation: Zapier’s free tier allows 100 tasks/month—enough for low volume. Make’s free plan offers 1,000 operations/month. If you prefer no third‑party service, keep everything in Apps Script and Drive triggers (no extra cost).

8. Maintenance tips

• Quota: Monitor OpenAI usage in your account dashboard; set a monthly budget alert.
• Error handling: The script logs parse errors; review View → Logs periodically.
• Scaling: For >500 invoices/month, consider batching requests (send up to 10 invoices per prompt) to reduce API calls.
• Security: Store the OpenAI key in Apps Script’s Properties Service (PropertiesService.getScriptProperties().setProperty('OPENAI_KEY', '…')) and retrieve it at runtime to avoid exposing it in the script editor.

Summary

1. Put invoice text (or OCR output) into Google Sheets.
2. Use Apps Script to call OpenAI and extract vendor, amount, date, PO.
3. Flag missing or invalid data with simple formulas.
4. Route rows needing review to an approval sheet and email the stakeholder.
5. Close the loop by writing decisions back.

All steps rely on widely available, low‑cost tools and can be fully implemented in a few hours without writing a custom app. Adjust the prompts, validation rules, and approval logic to match your specific invoice formats and business policies.