How to Build an Automated Invoice Generator in Google Sheets (Free Template)

Published on | ProSheetHub

Creating and sending client invoices manually in spreadsheets is time-consuming and prone to human calculation errors. A single missed line item or formula mistake can delay payments and damage client relationships.

In this guide, you will learn how to build an automated invoice generator in Google Sheets that pulls customer information, calculates item subtotals, tax, and grand totals automatically, and assigns sequential invoice IDs—ready to export or print in seconds.

Professional invoice template in Google Sheets dynamically populated with client and product details.

The 3-Tab Architecture for Invoice Automation

To keep your invoicing clean and scalable, structure your workbook into three dedicated sheets:

  • 1. Clients: Stores Client ID, Company Name, Contact Person, Email, and Billing Address.
  • 2. Items_Catalog: Stores Item SKU, Description, and Unit Price.
  • 3. Invoice: The front-facing printable template that dynamically pulls data using dropdowns and formulas.

Step 1: Auto-Populating Client Details

Add a client selection dropdown in cell B6 using Data > Data validation > Add rule > Dropdown (from a range) pointing to Clients!A2:A.

Populate the client name, email, and address automatically using XLOOKUP:

=IFNA(XLOOKUP(B6, Clients!A:A, Clients!B:D, "Select Client"), "")
Client Details Auto-Filling via Dropdown and XLOOKUP

Step 2: Dynamic Line Items & Pricing

In your itemized invoice rows (e.g., row 12 downwards), select the Item SKU in Column A. In Column B (Description) and Column C (Unit Price), pull the catalog values:

=IFNA(XLOOKUP(A12, Items_Catalog!A:A, Items_Catalog!B:B, ""), "")

To calculate the line total in Column E (Quantity in Column D × Unit Price in Column C):

=IF(OR(A12="", D12=""), "", C12 * D12)

Step 3: Calculating Subtotal, Tax, and Grand Total

At the bottom right of the invoice table, set up your calculation summary block:

  • Subtotal: =SUM(E12:E30)
  • Tax (e.g., 8%): =E31 * 0.08
  • Discount (if applicable): =E31 * D33
  • Grand Total: =E31 + E32 - E33

Step 4: Generate a Dynamic Payment QR Code

Make it easy for clients to pay by generating an automatic payment or invoice link QR code directly in the sheet:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=https://yourpaymentlink.com/pay?inv=" & B4)

(Read our full walkthrough on How to Generate and Track QR Codes in Google Sheets for more customization options.)

Best Print & PDF Export Settings

  • Set Print Area: Highlight the invoice bounds (e.g., A1:F38) > click File > Print (or Ctrl + P).
  • Paper & Scale: Set paper to Letter or A4, orientation to Portrait, and Scale to Fit to width.
  • Formatting: Uncheck Show gridlines under the Formatting panel for a clean PDF look.

Related Business Automation Tutorials

Comments

Post a Comment