Generate Invoice PDFs From Google Sheets Orders

Short answer: Use a reusable invoice-template sheet and Google Apps Script. The script reads customer, product, and transaction rows; fills the template; exports it as a PDF; saves the file in Drive; and writes the PDF URL back to an Invoices sheet. You can then email the generated files. This approach is free to build with a Google account, keeps your invoice layout in Sheets, and can process one invoice per order or group several rows into one invoice per customer.

Google’s official sample follows this same pattern: “Automatically create PDFs with information from sheets in a Sheets spreadsheet.” See the Google Apps Script PDF generation sample before adapting the code below.

What you will build

The finished workbook has four functional areas:

Customers: customer ID, name, billing address, email, and optional tax details.
Products: SKU, description, unit price, and tax category.
Transactions: order ID, customer ID, SKU, quantity, order date, and status.
Invoice Template and Invoices: a printable layout plus a tracking table containing generated PDF links.

Apps Script fills one template repeatedly. It flushes pending spreadsheet changes, waits briefly for Sheets to finish recalculating, exports the template to PDF, stores the file in a Drive folder, and records the URL. Reusing one template keeps layout changes separate from data and avoids creating a new worksheet for every invoice.

Prepare the Google Sheet

1. Create the source sheets

Create these headers in row 1. Header spelling matters if you use the sample’s lookup logic.

A reusable template turns grouped order rows into consistent invoice PDFs.

Sheet	Suggested columns	Purpose
Customers	Customer ID, Name, Email, Address, Tax ID	One row per bill-to customer
Products	SKU, Description, Unit Price, Tax Rate	Catalog and pricing
Transactions	Order ID, Customer ID, SKU, Quantity, Order Date	One row per purchased line
Invoices	Invoice ID, Order ID, Customer ID, PDF URL, Status, Created At, Error	Audit and delivery tracking

Use stable IDs rather than row numbers. Sorting a source sheet should never change which customer or product an order references. Store money as numbers, then apply currency formatting in the template. Keep dates as actual Sheets dates, not text strings.

2. Design the Invoice Template sheet

Make the template print-ready before writing code:

Put your business name, address, payment terms, and tax registration details in fixed cells.
Reserve cells for invoice number, invoice date, customer name, customer address, and customer email.
Create a line-item table with enough rows for the largest order you expect, or have the script insert rows.
Add subtotal, tax, discount, shipping, and total cells with formulas.
Set print area, paper size, margins, orientation, repeating headers, and a footer.
Hide helper cells or sheets that should not appear in the PDF.

Name the cells you plan to fill, or document their coordinates in one place in the script. Named ranges make future layout edits safer than scattering references such as B7 throughout the code.

Install the Apps Script

Open Extensions > Apps Script, create a script file, and paste the following baseline. Change the sheet names, cell addresses, Drive folder ID, and email override before running it.

const CONFIG = {
  customersSheet: 'Customers',
  productsSheet: 'Products',
  transactionsSheet: 'Transactions',
  invoicesSheet: 'Invoices',
  templateSheet: 'Invoice Template',
  outputFolderId: 'PUT_DRIVE_FOLDER_ID_HERE',
  // During testing, every email goes here instead of to customers.
  emailOverride: 'YOUR_TEST_EMAIL@example.com'
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Generate and send PDFs')
    .addItem('Process invoices', 'processInvoices')
    .addItem('Send emails', 'sendInvoiceEmails')
    .addItem('Reset template', 'resetTemplate')
    .addToUi();
}

function processInvoices() {
  const ss = SpreadsheetApp.getActive();
  const customers = readTable_(ss.getSheetByName(CONFIG.customersSheet));
  const products = readTable_(ss.getSheetByName(CONFIG.productsSheet));
  const transactions = readTable_(ss.getSheetByName(CONFIG.transactionsSheet));
  const invoiceSheet = ss.getSheetByName(CONFIG.invoicesSheet);
  const template = ss.getSheetByName(CONFIG.templateSheet);
  const folder = DriveApp.getFolderById(CONFIG.outputFolderId);

  const customerById = indexBy_(customers, 'Customer ID');
  const productBySku = indexBy_(products, 'SKU');
  const groups = groupBy_(transactions, 'Order ID');
  const existing = existingInvoiceIds_(invoiceSheet);

  Object.keys(groups).forEach(orderId => {
    if (existing.has(orderId)) return; // Makes reruns safe.
    const lines = groups[orderId];
    const customer = customerById[lines[0]['Customer ID']];
    if (!customer) return appendError_(invoiceSheet, orderId, 'Customer ID not found');

    try {
      resetTemplate();
      fillTemplate_(template, orderId, customer, lines, productBySku);
      SpreadsheetApp.flush();
      Utilities.sleep(500); // Allow formulas and formatting to settle.

      const pdf = exportSheetAsPdf_(ss.getId(), template.getSheetId(), orderId);
      const file = folder.createFile(pdf);
      const invoiceId = 'INV-' + orderId;
      invoiceSheet.appendRow([
        invoiceId, orderId, customer['Customer ID'], file.getUrl(),
        'Ready', new Date(), ''
      ]);
    } catch (error) {
      appendError_(invoiceSheet, orderId, error.message || String(error));
    }
  });
}

function fillTemplate_(sheet, orderId, customer, lines, productBySku) {
  // Replace these cells with the coordinates in your template.
  sheet.getRange('B4').setValue('INV-' + orderId);
  sheet.getRange('F4').setValue(new Date());
  sheet.getRange('B6').setValue(customer['Name']);
  sheet.getRange('B7').setValue(customer['Address']);
  sheet.getRange('B8').setValue(customer['Email']);

  const firstLineRow = 12;
  const maxLines = 30;
  sheet.getRange(firstLineRow, 1, maxLines, 5).clearContent();
  lines.forEach((line, i) => {
    const product = productBySku[line['SKU']];
    if (!product) throw new Error('SKU not found: ' + line['SKU']);
    const row = firstLineRow + i;
    const quantity = Number(line['Quantity']);
    const price = Number(product['Unit Price']);
    sheet.getRange(row, 1, 1, 5).setValues([[
      line['SKU'], product['Description'], quantity, price, quantity * price
    ]]);
  });
}

function exportSheetAsPdf_(spreadsheetId, sheetId, orderId) {
  const url = 'https://docs.google.com/spreadsheets/d/' + spreadsheetId +
    '/export?format=pdf&gid=' + sheetId +
    '&size=A4&portrait=true&fitw=true&sheetnames=false&printtitle=false' +
    '&pagenumbers=false&gridlines=false&fzr=false';
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
    muteHttpExceptions: true
  });
  if (response.getResponseCode() !== 200) {
    throw new Error('PDF export failed: HTTP ' + response.getResponseCode());
  }
  return response.getBlob().setName('invoice-' + orderId + '.pdf');
}

function sendInvoiceEmails() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CONFIG.invoicesSheet);
  const rows = readTable_(sheet);
  rows.forEach(row => {
    if (row['Status'] !== 'Ready' || !row['PDF URL']) return;
    const recipient = CONFIG.emailOverride || row['Email'];
    if (!recipient) return;
    const fileId = extractDriveId_(row['PDF URL']);
    const file = DriveApp.getFileById(fileId);
    GmailApp.sendEmail(recipient, 'Invoice ' + row['Invoice ID'],
      'Your invoice is attached.', { attachments: [file.getBlob()] });
    markStatus_(sheet, row, 'Sent');
  });
}

function resetTemplate() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CONFIG.templateSheet);
  ['B4','F4','B6','B7','B8'].forEach(a => sheet.getRange(a).clearContent());
  sheet.getRange(12, 1, 30, 5).clearContent();
}

function readTable_(sheet) {
  const values = sheet.getDataRange().getValues();
  const headers = values.shift().map(String);
  return values.filter(row => row.some(v => v !== '')).map(row =>
    Object.fromEntries(headers.map((h, i) => [h, row[i]])));
}
function indexBy_(rows, key) { return Object.fromEntries(rows.map(r => [r[key], r])); }
function groupBy_(rows, key) { return rows.reduce((g, r) => ((g[r[key]] ||= []).push(r), g), {}); }
function existingInvoiceIds_(sheet) {
  return new Set(readTable_(sheet).map(r => String(r['Order ID'])));
}
function appendError_(sheet, orderId, message) {
  sheet.appendRow(['', orderId, '', '', 'Error', new Date(), message]);
}
function extractDriveId_(url) { return url.match(/[a-zA-Z0-9_-]{20,}/)[0]; }
function markStatus_(sheet, row, status) { /* update the matching row in production */ }

Run it safely

Copy the official sample spreadsheet or create your workbook from the structure above.
Set emailOverride to your own address while testing.
Save the script, return to Sheets, and reload the document.
Choose Generate and send PDFs > Process invoices. Complete the authorization prompts for Sheets, Drive, URL Fetch, Script, and Gmail services.
Open the Invoices sheet and inspect each PDF link before sending anything.
Choose Send emails only after checking the rendered PDF, recipient, totals, and tax treatment.
Use Reset template when you need to clear the working layout for another run.

A Google account is required. Google notes that some Workspace accounts may require administrator approval for Apps Script scopes. Ask an administrator before treating an authorization error as a code defect.

Grouping, numbering, and data rules

One invoice per order

Group transaction rows by Order ID. Every row in that group must have the same customer ID. Generate the invoice number from the order ID or from a dedicated sequence sheet. A deterministic number makes retries idempotent: if the script stops after creating a file, a rerun can detect the existing order and avoid duplicates.

One invoice per customer

Group by customer ID and add a date-range or order-range column to the invoice. This is useful for statements, but make sure each line retains its original order ID for reconciliation.

Tax, rounding, and currency

Calculate line extensions from numeric quantity and unit price. Decide whether tax rounds per line or on the subtotal, then use the same rule in the sheet, PDF, and accounting system. Store currency explicitly if orders can cross borders. Never parse formatted strings such as “$1,200.00” as numbers without removing symbols and separators first.

Bulk processing, quotas, and reliability

Batch reads: load each source sheet once, as the script does, instead of calling getRange for every cell.
Template latency: call SpreadsheetApp.flush() before export and keep a short delay when formulas or images need time to render.
Retries: record status and error text per order. Retry only rows marked Error. Do not blindly rerun every row.
Drive permissions: the file is created under the authorizing account. Set sharing deliberately; a Drive URL is not automatically public.
Large runs: Apps Script execution time and service quotas limit how many invoices one invocation can create. Process in batches using a status column, time-driven triggers, or continuation tokens.
Partial failure: a PDF can exist even if the final tracking-row write fails. Before retrying, search the output folder by deterministic filename or store a creation key in PropertiesService.
Privacy: request only the scopes you need, restrict the spreadsheet and Drive folder, and avoid logging customer addresses or payment data.

Troubleshooting

Symptom	Likely cause	Fix
Authorization required or access denied	New OAuth scopes or Workspace administrator policy	Run the function once interactively and approve scopes; ask the administrator to allow the project.
PDF is blank or shows old values	Formula recalculation or export occurred before updates settled	Call `SpreadsheetApp.flush()`, increase the delay, and verify the template sheet ID.
Some lines are missing	More lines than the template range or an empty SKU	Increase the line range, validate required columns, and reject incomplete orders before filling.
SKU or customer not found	ID mismatch, spaces, or numeric/text coercion	Normalize IDs with `String(value).trim()` and use consistent column types.
Duplicate invoices after rerun	No idempotency check	Use Order ID as a unique key and skip rows already marked Ready or Sent.
Email quota or invalid recipient	Too many messages, malformed addresses, or missing email	Validate addresses, send in controlled batches, and leave failed rows in Error status.
Drive file cannot be opened	Wrong folder ID or insufficient permission	Confirm the folder belongs to the authorizing account and test `DriveApp.getFolderById` separately.
Totals differ from the order system	Different tax or rounding rules	Document one rounding policy and compare a known order line by line.

Or skip the browser setup

If your invoice is already rendered as a web page, or you need a screenshot/PDF capture step after a Sheets-to-HTML workflow, ScreenshotNeo provides a single HTTP request. Its capture API can return PNG, JPEG, WebP, or PDF, and the API documentation lists the options.

Clean capture removes consent and interruption elements before rendering.

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

Cookie banners, newsletter popups, and chat widgets are removed before the shot. Bot checks, blank pages, failed loads, timeouts, and cache hits are not billed, and response headers identify the page verdict and billing result. An MCP server lets Claude, Cursor, and other AI agents call take_screenshot, get_page_info, and capture_pdf. The Free plan includes 1,000 screenshots per month with no card; paid plans start at $5 for 3,000. Create a free ScreenshotNeo account.

Managed add-on option

If maintaining Apps Script is not appropriate, the Google Workspace Marketplace listing for Bulk Invoice Generator describes merging Sheets data into Google Docs templates, producing PDF or Docs invoices, emailing them, filtering records, and writing status updates back to the sheet. Treat those as publisher-advertised capabilities: verify current pricing, quotas, permissions, support, and partner terms in the listing before rollout. Compare it with custom Apps Script on setup effort, template and grouping control, authorization scope, bulk status tracking, email workflow, maintenance, and total cost.

FAQ

Can I create invoices without sending email?

Yes. Run Process invoices and omit Send emails. The PDF links remain in the Invoices sheet for manual delivery or another system.

Can the PDF contain a logo?

Yes. Place the logo in the Invoice Template sheet and confirm it appears in the print preview before automating a batch.

How do I reissue a corrected invoice?

Keep the original row and create a revision or credit-note policy. Do not overwrite an issued invoice silently; preserve the original PDF URL and record the replacement.

Can I use Google Docs instead of a Sheet template?

Yes, a Docs merge workflow can be easier for narrative invoices. The Marketplace add-on described above uses Docs templates; custom Apps Script can also generate Docs files before converting them to PDF.

Where should I check current limits?

Check Google’s current Apps Script, Gmail, Drive, and Workspace quota documentation for the account type running the script. Limits and administrator policies can change.