Receipt capture often lands as images in Drive. Apps Script can send new files to an OCR vendor and log structured fields.
receiptOcrToSheet skips fileIds already present in Receipts, filters to images/PDFs, and POSTs bytes with a Bearer API key.
OCR_ENDPOINT and OCR_API_KEY live in Script Properties — never hardcode secrets.
Adapt JSON field names (merchant/date/total) to your OCR provider’s schema.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| FOLDER_ID | Drive folder | Inbox for receipts |
| Receipts | A fileId | Dedupe key |
| Receipts | B name | File name |
| Receipts | C merchant | OCR field |
| Receipts | D date | OCR field |
| Receipts | E total | OCR field |
| Receipts | F status | OK / OCR_ERROR |
| Properties | OCR_ENDPOINT / OCR_API_KEY | Vendor config |
What this script does
Incremental Drive→OCR→Sheet pipeline with fileId dedupe.
Prerequisites
Drive folder; OCR vendor; UrlFetch + Drive scopes.
- Script properties set
- Receipts headers
- Folder shared to script user
Walkthrough
Drop a test image in the folder; run; see a new Receipts row.
Edge cases
Vendor schemas differ — map json fields explicitly.
- Errors still append a row
- Non-image files skipped
Privacy
Receipts contain personal and payment data. Restrict sheet sharing and scrub card numbers if the OCR returns them.
How to test
Re-run should not duplicate the same fileId.
Hardening for production
Move processed files to a Done subfolder; retry OCR_ERROR later.
Variations
Google Cloud Vision annotate instead of third-party OCR.
Full code: receiptOcrToSheet()
Set FOLDER_ID and OCR properties, authorize Drive/UrlFetch, then run on a schedule.
/**
* Send a receipt image Drive file to an OCR API and append fields to Receipts.
* Uses UrlFetchApp; set OCR_ENDPOINT + OCR_API_KEY in Script Properties.
*/
const FOLDER_ID = "YOUR_RECEIPTS_FOLDER_ID";
const DEST = "Receipts";
function receiptOcrToSheet() {
const props = PropertiesService.getScriptProperties();
const endpoint = props.getProperty("OCR_ENDPOINT");
const apiKey = props.getProperty("OCR_API_KEY");
if (!endpoint || !apiKey) throw new Error("Set OCR_ENDPOINT and OCR_API_KEY");
const folder = DriveApp.getFolderById(FOLDER_ID);
const sheet = SpreadsheetApp.getActive().getSheetByName(DEST);
const processed = {};
sheet.getDataRange().getValues().slice(1).forEach(function (r) {
if (r[0]) processed[r[0]] = true; // fileId
});
const files = folder.getFiles();
while (files.hasNext()) {
const file = files.next();
const id = file.getId();
if (processed[id]) continue;
if (file.getMimeType().indexOf("image/") !== 0 && file.getMimeType() !== "application/pdf") continue;
const blob = file.getBlob();
const resp = UrlFetchApp.fetch(endpoint, {
method: "post",
contentType: blob.getContentType(),
payload: blob.getBytes(),
headers: { Authorization: "Bearer " + apiKey },
muteHttpExceptions: true,
});
if (resp.getResponseCode() >= 300) {
sheet.appendRow([id, file.getName(), "", "", "", "OCR_ERROR " + resp.getResponseCode()]);
continue;
}
const json = JSON.parse(resp.getContentText());
sheet.appendRow([
id,
file.getName(),
json.merchant || "",
json.date || "",
json.total || "",
"OK",
]);
}
}- Line 3: Vendor HTTP endpoint from Script Properties.
- Line 25: Skips files already logged.
- Line 28: Loads file bytes for upload.
- Line 32: Raw body POST.
- Line 44: Map these keys to your OCR response.
- Line 37: Status when HTTP fails.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: receipt OCR
- 1FOLDER_ID correct
- 2OCR_ENDPOINT and OCR_API_KEY set
- 3Receipts sheet exists
- 4Drive + UrlFetch authorized
- 5JSON field mapping verified with one sample
- 6Sharing locked down for PII
Frequently asked questions
Any HTTP API accepting image bytes/JSON. Adjust headers and parse paths.
Enable Vision API and POST to images:annotate with base64 content — different payload shape.
Some OCRs need page splitting; check vendor docs.
Process only new files; move to Done; cap files per run.
Filter status OCR_ERROR, delete those rows or clear fileId dedupe, re-run.
Yes via UrlFetch to Document AI REST with a service account token.
Many JSON APIs want base64; this sample uses raw bytes — match your vendor.