Auto-Generate PDF Invoices from a Google Sheet
For a freelancer, a small agency, or a side-project owner, the gap between "I should send an invoice" and "I have a branded PDF invoice in the customer's inbox" is annoyingly wide. The full-featured tools (QuickBooks, FreshBooks, Xero) start at $15-30 per month and assume you have a real accounting setup. The free tools tend to put their watermark on your invoices. The middle ground - which is what most people actually want - is a small Google Sheet that you fill in, a click, and a tidy PDF lands in your Drive ready to send.
The setup below is exactly that. It uses a Google Doc as the invoice template, a Google Sheet as the data source, and about 40 lines of Apps Script to glue them together. Total setup time: 30 minutes. Marginal cost per invoice after that: zero.
The pieces
You'll create three things:
- A Google Doc template with placeholders like
{{InvoiceNumber}},{{ClientName}},{{Total}}, etc. - A Google Sheet where each row is one invoice's data.
- An Apps Script attached to the Sheet that copies the template, fills in the placeholders, saves a PDF, and (optionally) emails it.
Step 1: The Doc template
Create a new Google Doc. Style it however you want your invoice to look - logo at the top, your business address, the standard invoice fields. Where the dynamic values should go, type placeholders surrounded by double curly braces:
INVOICE {{InvoiceNumber}}
Date: {{InvoiceDate}}
Due: {{DueDate}}
Bill To:
{{ClientName}}
{{ClientAddress}}
Description: {{Description}}
Quantity: {{Quantity}}
Rate: {{Rate}}
Subtotal: {{Subtotal}}
Tax: {{Tax}}
TOTAL: {{Total}}
Payment terms: Net 30
Bank details: ...Save the doc. Note its ID - the long string in the URL between /d/ and /edit. You'll need it in the script.
Step 2: The Sheet
Create a new Google Sheet with these columns in row 1:
InvoiceNumber | InvoiceDate | DueDate | ClientName | ClientEmail | ClientAddress | Description | Quantity | Rate | TaxPercent | StatusAdd a few rows of test invoice data. Leave Status blank.
Step 3: The script
In the Sheet, click Extensions → Apps Script. Paste this in, replacing the two ID constants:
const TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID_HERE';
const OUTPUT_FOLDER_ID = 'YOUR_DRIVE_FOLDER_ID_HERE';
function generateInvoices() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const headers = data[0];
const idx = (name) => headers.indexOf(name);
for (let i = 1; i < data.length; i++) {
if (data[i][idx('Status')]) continue;
const row = {};
headers.forEach((h, j) => row[h] = data[i][j]);
const subtotal = Number(row.Quantity) * Number(row.Rate);
const tax = subtotal * (Number(row.TaxPercent) / 100);
const total = subtotal + tax;
const copy = DriveApp.getFileById(TEMPLATE_ID)
.makeCopy('Invoice ' + row.InvoiceNumber + ' - ' + row.ClientName,
DriveApp.getFolderById(OUTPUT_FOLDER_ID));
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
body.replaceText('\\{\\{InvoiceNumber\\}\\}', String(row.InvoiceNumber));
body.replaceText('\\{\\{InvoiceDate\\}\\}', formatDate(row.InvoiceDate));
body.replaceText('\\{\\{DueDate\\}\\}', formatDate(row.DueDate));
body.replaceText('\\{\\{ClientName\\}\\}', row.ClientName);
body.replaceText('\\{\\{ClientAddress\\}\\}', row.ClientAddress);
body.replaceText('\\{\\{Description\\}\\}', row.Description);
body.replaceText('\\{\\{Quantity\\}\\}', String(row.Quantity));
body.replaceText('\\{\\{Rate\\}\\}', '$' + Number(row.Rate).toFixed(2));
body.replaceText('\\{\\{Subtotal\\}\\}', '$' + subtotal.toFixed(2));
body.replaceText('\\{\\{Tax\\}\\}', '$' + tax.toFixed(2));
body.replaceText('\\{\\{Total\\}\\}', '$' + total.toFixed(2));
doc.saveAndClose();
const pdf = DriveApp.getFileById(copy.getId()).getAs('application/pdf');
const pdfFile = DriveApp.getFolderById(OUTPUT_FOLDER_ID).createFile(pdf)
.setName('Invoice ' + row.InvoiceNumber + '.pdf');
// Optional: delete the intermediate Doc, keep only PDF
DriveApp.getFileById(copy.getId()).setTrashed(true);
sheet.getRange(i + 1, idx('Status') + 1)
.setValue('Generated ' + new Date().toISOString());
}
}
function formatDate(d) {
return Utilities.formatDate(new Date(d), Session.getScriptTimeZone(), 'MMM d, yyyy');
}Save. Run generateInvoices from the editor. Authorize when prompted. Within a few seconds you'll see PDFs appear in the output folder.
How it works, briefly
Template copy. For each invoice row, the script makes a copy of the master template (so the original is never modified). The copy lives in your specified output folder.
Placeholder replacement. body.replaceText finds each {{Placeholder}} string and substitutes the row data. The backslashes in the script are because replaceText takes a regex, and curly braces have special meaning in regex.
PDF export. getAs('application/pdf') renders the doc as a PDF and creates a separate file. We then trash the intermediate Doc so the output folder only contains the final PDFs - tidy.
Step 4 (optional): Email the PDF automatically
Add this inside the loop, right after the PDF is created, to email each invoice to its client:
if (row.ClientEmail) {
MailApp.sendEmail({
to: row.ClientEmail,
subject: 'Invoice ' + row.InvoiceNumber + ' from Your Business',
body: 'Hi ' + row.ClientName + ',\n\n' +
'Please find your invoice attached. ' +
'Payment is due by ' + formatDate(row.DueDate) + '.\n\n' +
'Thanks!',
attachments: [pdfFile.getBlob()]
});
}Each invoice goes out individually with the PDF attached. The Status column gets a "Sent" timestamp, so re-running the script won't re-send.
A few practical refinements worth adding
Sequential invoice numbers. Instead of typing invoice numbers manually, use a formula in your sheet: =ARRAYFORMULA(IF(A2:A="","","INV-" & TEXT(ROW(A2:A)-1, "0000"))) in column A. You get INV-0001, INV-0002, etc., automatically.
Multi-line item invoices. The simple version above handles one line item per invoice. For multi-line invoices, you have two options: (a) use a separate "line items" sheet with an invoice-number foreign key, and have the script assemble lines into the doc table - significantly more code, or (b) keep using one row per line item and let one customer have multiple lines on one invoice by grouping during script run. Option (b) is usually easier for small operations.
Currency formatting. The script above uses $ hardcoded. Make it configurable via a column like Currency in your sheet so the same template works for USD, EUR, etc. Then in the script: '$' + ... becomes row.Currency + ' ' + ....
Logo and branding. The Doc template already supports inline images. Put your logo in the template once; it propagates to every generated PDF.
What this replaces, in dollars
For a small freelance operation sending 5-20 invoices per month, the most common paid tool is FreshBooks at roughly $19/month entry tier, or QuickBooks Self-Employed at $20/month. Over a year, that's $240. Over five years, $1,200. The script above does the invoice-generation piece of all those tools, for free, and the data lives in your own Google account with no vendor lock-in.
The piece those paid tools also do that this script doesn't: bookkeeping, tax categorization, financial reports for accountants. If you need those, pay for the tool. If you just need clean PDFs going to clients, you don't.
The maintenance burden
Essentially zero. We have a version of this script that's been running unchanged for over two years. The Google Doc template needs updates when you change your branding or address, which is a 30-second edit. The script itself doesn't need to be touched. The Sheet structure can be extended (add new columns, expose them as placeholders) without breaking what's already there.
The deepest reason to do it this way isn't the cost saving - it's the fact that all the data stays in formats (Sheets, Drive, PDF) that you can read and back up and migrate forever, with no proprietary file formats or accounts holding it hostage. That's worth more than any specific dollar amount.