Google Sheet Invoice Template Automation: How We Send Invoices + Emails Automatically

TL;DR: We automated our client invoicing process using a Google Sheet invoice template and Google Apps Script. This setup generates invoices as PDFs and sends them via email—saving time, reducing errors, and handling follow-ups. No third-party invoicing tool needed.

How We Automated Our Invoice Generation Using Google Sheets & Apps Script

If you're managing multiple clients—especially on monthly retainers—you know how chaotic it gets keeping up with invoicing dates and follow-ups. That’s why, at HB Lance, we decided to build a Google Sheet invoice template system that handles everything for us, automatically.

In this post, I’ll show you exactly how we did it.

What You’ll Need:

• A Google Sheet (your custom invoice template)

• Google Apps Script (for automation)

• Gmail (for sending emails)

• Basic understanding of spreadsheet formulas

Step 1: Create a Google Sheet Invoice Template

We started by creating a Google Sheet invoice template with placeholders for dynamic data such as:

• Client Name

• Address

• Invoice Date

• Invoice Number

• Project Description

• Monthly Amount

You can design it to match your branding or use a simple format—whatever suits your business.

Step 2: Add a Client Data Sheet

In the same spreadsheet, we created another tab to store client data:

• Company Name

• Email(s) of the contact person

• Project or Service details

• Retainer Amount

• Location

This allows the script to pull details for each client dynamically.

Step 3: Write an Apps Script to Automate the Workflow

Here’s what our Google Apps Script does:

• Loops through each client in the spreadsheet

• Inserts their data into the invoice template

• Converts the completed invoice into a PDF

• Sends it via email to the contact(s) listed

• Keeps track of invoice numbers month-to-month for each client

You can even set up conditional logic to send follow-up emails if the payment isn’t received on time.

Step 4: Set Up Monthly Triggers

To make this a true automation system, we set a time-driven trigger in Apps Script to run the process on specific dates each month. This way, invoices go out without us touching anything.

Demo Results: What the Output Looks Like

After running the script:

• A personalized invoice PDF is created for each client

• The invoice includes the name, location, invoice ID, description, and amount

• Each client gets their invoice in their inbox—on time, every time

And yes, the invoice number updates automatically each month.

Google Sheet Invoice Template Automation:

Why We Chose Google Sheets Over Invoicing Tools

Most invoicing platforms come with limitations or costs when scaling. We wanted something:

• Free and flexible

• Easy to update

• Integratable with other marketing and sales workflows

A Google Sheet invoice template gave us full control—and zero subscription fees. Template and code