Automate Invoice Generation in Google Sheets with Apps Script

Manual invoicing—copying line items into a template, exporting PDFs, and emailing one by one—scales poorly. Automating invoice generation in Google Sheets with Apps Script lets you turn rows (from a Sheet or Form) into PDF invoices and send them via Gmail on a schedule or on submit. You keep one source of truth, a reusable template (Doc or HTML), and no per-invoice SaaS cost. The main constraints are execution time and daily email/Drive quotas, so you need a clear flow: read pending rows, generate PDFs in batches, send and mark processed. This guide covers common pitfalls, architecture, implementation with code, and scaling when volume grows.

Common mistakes when automating invoice generation

• Generating one PDF per row in a long loop—hitting execution time or Drive quota.
• No trigger; invoices only run when someone clicks Run.
• Building PDF from scratch in code instead of using a Doc template with placeholders.
• Not marking rows “invoiced” so the same row is processed again on the next run.
• Storing sensitive client data in script code instead of the sheet or Script Properties.

Architecture and data flow

Typical flow: a Sheet holds invoice data (client, items, amounts, status). A time-driven or on Form Submit trigger runs a function that reads rows where status = “Pending”, fills a Google Doc template (copy, replace placeholders, export as PDF), attaches the PDF to an email, sends it, and sets status = “Sent”. Data flow is Sheet → script → Doc (template) → PDF → Gmail. For form-to-invoice, the Form writes to the Sheet and an installable on Form Submit trigger runs the same generation logic for the new row. For high volume, process in chunks and use a “last processed” cursor or status column so each run has a bounded workload. For form-to-PDF patterns, Automate Google Form to PDF Invoice covers the submit path; here we focus on batch generation and scaling.

Implementation strategy

Template and batch size

Use a Google Doc as template: placeholders like {{CLIENT_NAME}}, {{TOTAL}}. Copy the Doc, replace placeholders with body.replaceText(), export as PDF with DriveApp.getFileById(id).getAs("application/pdf"), then attach to GmailApp.sendEmail(). Process up to N rows per run (e.g. 20–30) so the run stays under 5 minutes; persist “last processed” or set status so the next run skips done rows.

function generateInvoicesBatch() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Invoices");
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  const numRows = lastRow - 1;
  const data = sheet.getRange(2, 1, 2 + numRows - 1, 8).getValues();
  const templateId = PropertiesService.getScriptProperties().getProperty("INVOICE_TEMPLATE_ID");
  const BATCH = 20;
  var processed = 0;
  for (var i = 0; i < data.length && processed < BATCH; i++) {
    if (data[i][7] === "Sent") continue;
    var copyFile = DriveApp.getFileById(templateId).makeCopy();
    var copy = DocumentApp.openById(copyFile.getId());
    copy.getBody().replaceText("{{CLIENT}}", data[i][0]).replaceText("{{AMOUNT}}", data[i][5]);
    copy.saveAndClose();
    var pdfBlob = DriveApp.getFileById(copy.getId()).getAs("application/pdf");
    GmailApp.sendEmail(data[i][1], "Invoice", "Please find your invoice attached.", { attachments: [pdfBlob] });
    sheet.getRange(2 + i, 8).setValue("Sent");
    processed++;
  }
}

Triggers

Use an installable time-driven trigger (e.g. daily) for batch; use on Form Submit if each submission should trigger one invoice immediately. Store template ID and any config in Script Properties. For execution limits when generating many PDFs, see the execution time limit guide.

Example: A consultancy sent 80–120 invoices per month from a Sheet. We automated: time-driven trigger every 6 hours, read rows with status “Pending”, generate PDF from Doc template (batch of 25), email and set “Sent”. Monthly manual time dropped from ~6 hours to near zero; runs stay under 4 minutes.

In similar setups, batching invoice generation (20–30 per run) typically keeps runtime under 5 minutes and avoids Gmail daily send limits when spread across multiple runs.

Scalability and quotas

Execution time (6 min) and daily Gmail/Drive limits apply. Batch generation (e.g. 20–30 per run), use a status column so each run only processes new rows, and schedule triggers so total daily volume stays under email quota. For very high volume, split across multiple scripts or use a queue sheet with a cursor. API integrations can push invoice metadata to an external system if needed.

Enterprise scaling

Use one trigger per job; log last run and count generated in a status sheet. Document template and sheet ownership. For multi-entity invoicing, separate workbooks or scripts per entity give separate quota pools.

FAQ