Hourly invoice extraction from Google Drive to Sheets with Gemini

Build me a code-based workflow that runs on a cron trigger every hour and turns a Google Drive folder full of PDF invoices into a running Google Sheets ledger, with zero manual data entry.

Inputs I'll configure: the Google Drive folder ID for my invoices inbox, the Google Sheets spreadsheet ID for the tracker, the name of the visible tracker tab (e.g. Invoices), and the name of a hidden tab (e.g. _processed) used as a dedupe log. Also let me pick the Gemini model (default to gemini-2.5-pro).

Step 1. Read the last-run watermark. Load the hidden _processed tab from Google Sheets and compute the max modifiedTime already seen, plus the set of Drive file IDs already processed. If the tab is empty, use a watermark of the workflow's first-run timestamp.

Step 2. List new PDFs in Google Drive. Call List Files scoped to the configured folder ID with a q filter that requires mimeType = 'application/pdf', trashed = false, the folder as parent, and modifiedTime > the watermark. Request id, name, modifiedTime, and webViewLink. Skip any file whose ID appears in the processed set.

Step 3. For each unseen PDF, use Google Drive Download File Content to fetch the raw PDF bytes.

Step 4. Extract structured data with Google Gemini Generate Content. Pass the PDF as inline data (base64) with mimeType application/pdf, and use a response schema (responseMimeType application/json + responseSchema) requiring: vendor_name, invoice_number, invoice_date (ISO), due_date (ISO or null), currency (ISO 4217), subtotal (number), tax (number), total (number), and line_items as an array of { description, quantity, unit_price, amount }. Include a short system instruction telling Gemini to return null for missing fields and to never invent values.

Step 5. Append one row per invoice to the tracker tab via Google Sheets Append Values. Columns, in order: Processed At, Source File (a HYPERLINK formula wrapping the Drive webViewLink with the file name as label), Vendor, Invoice Number, Invoice Date, Due Date, Currency, Subtotal, Tax, Total, Line Item Count. Flatten line_items into the row as a Line Item Count only; the full JSON is not needed in the visible tab.

Step 6. Record the file in the hidden _processed tab: append a row with file_id, file_name, modifiedTime, processed_at, and extraction_status (ok | error). If Gemini extraction fails for a file, still record it as an error row so we don't retry every hour, but include the error message and skip the tracker append for that file.

Process files sequentially in modifiedTime order so the watermark advances cleanly. Keep the code deterministic: no free-form agent reasoning, just list, download, extract-with-schema, append. Log the count of new invoices processed at the end of each run.