How to Automate Invoices in Google Sheets and Send Them

Most spreadsheet invoicing advice stops at the document. You get a nice template, and then you still have to remember to open it, fill it in, export it, and email it on the right day.

Google Sheets closes that last gap. Because a script attached to the file can run whether or not anybody has it open, the spreadsheet stops being a document and starts being a small billing system.

That said, this is still a spreadsheet holding your billing together. It is a genuinely good answer for a small, steady set of clients, and a genuinely bad one once the volume or the number of people involved grows.

What can Google Sheets automate that Excel cannot?

Scheduled work. Both tools fill in an invoice from a client list equally well, but Google Sheets can attach a script that runs on a clock, so invoices can be created, exported, and emailed on the first of the month with nobody present.

Google describes these as installable triggers, and the time-driven kind behaves like a scheduled job. There are also triggers that fire when the sheet is edited, when its structure changes, or when a form is submitted, which is how a client intake form can start the billing chain.

Build the sheet before you touch any code

The structure is the same skeleton that works in any spreadsheet: one sheet for the invoice layout, one for clients, one for items, and one log of everything you have issued. Get this right and the script is short.

The spreadsheet part

1. Create the four tabs — Invoice, Clients, Items, Log. The Invoice tab holds the layout and almost no data. The Clients and Items tabs hold one row each per client and per thing you sell. The Log records every invoice number you have issued.
2. Give every client a short code — Names get typed inconsistently and break lookups. A code such as ACME never changes, and the display name is looked up from it. Do the same for items.
3. Add a dropdown for the client — Use data validation on the client code cell, with the list pointing at the code column of the Clients tab. Selecting a client is now one click and cannot be misspelled.
4. Pull the details in with VLOOKUP — Look the selected code up against the Clients range and return the column you need. Google's own documentation recommends passing FALSE as the last argument for an exact match, and writing it explicitly rather than relying on the default.
5. Repeat for line items and totals — A dropdown of item codes per line, a lookup for description and unit price, quantity times price for the line total, then a sum, tax, and grand total. Wrap the lookups so empty lines stay blank.
6. Read the next invoice number from the Log — Never type it. The number comes from the Log tab, and the script writes a new row there when an invoice goes out. Sequence problems are the most common failure in spreadsheet invoicing.

Column numbers are the weak point of VLOOKUP. Insert a column into the Clients tab and every formula that counted past it now returns the wrong field, silently. Keep the client columns fixed once the sheet is live, or add new fields only at the end.

How do you make it send by itself?

You attach a script and give it a time-driven trigger. From inside the sheet, open the script editor with Extensions then Apps Script, write a function that builds and sends the invoice, then create a trigger that runs it on the schedule you want.

Google's documentation is clear that installable triggers can call services requiring authorisation, which is why this can send mail while a simple trigger cannot. The first run asks you to grant permission, and that is expected.

• Write one function that does one cycle. Read the due clients from the Clients tab, set the invoice fields, export the Invoice tab as a PDF, email it, and append a row to the Log.
• Test it by hand first. Run the function manually with your own address as the recipient until three consecutive runs produce exactly the file you expect.
• Add the time-driven trigger. Google documents time-driven triggers as running anywhere from every minute to once a month, so a monthly billing run is well within normal use.
• Log every run. Write what happened into the Log tab, including failures. A silent scheduled job is how you discover in March that nothing sent since December.
• Know whose account it runs as. Google states that installable triggers always run under the account of the person who created them, so invoices will send from that person's mailbox and stop if their access is removed.

When the spreadsheet stops being the right tool

A scripted Google Sheet is a real automation, and it has real edges. These are the ones that actually bite, in roughly the order people hit them.

• It runs as one person. The trigger belongs to whoever made it. When that person leaves or loses access, the billing silently stops.
• Nobody maintains the script. It works until Google changes something or the sheet layout shifts, and then somebody has to debug code they did not write.
• It cannot read a reply. A client answers asking for a purchase order number, and the script has no idea. That thread sits unanswered for a week.
• It does not know who paid. Payment status stays a manual reconciliation, so chasing is still on you.
• Shared editing breaks numbering. Two people in the file at once, and the next invoice number is a guess.

What to do instead of maintaining a script

The reason spreadsheet automations decay is that they only do what you spelled out, and billing is full of small exceptions nobody spells out in advance. That is the gap we built for at Sistava. You hire an AI Employee, keep your data in the sheet you already have, and describe the routine in plain English instead of in code.

The practical difference shows up in the exceptions. When a client replies asking for a reference number, or the retainer changed halfway through the month, the run does not fail silently. You get a question, or a corrected draft waiting for your yes. Reading files, sending mail, and working on a schedule are ordinary capabilities on Sistava, so none of it depends on a script somebody has to keep alive.

None of this means abandoning the spreadsheet. Keep it as your record if you like it, and hand over only the schedule and the sending. If you would rather see what that looks like before deciding, Sistava lets you describe the routine and watch one cycle run before anything reaches a client.

The decision usually comes down to who will still understand the setup in a year. A spreadsheet with a script is cheap to build and expensive to inherit, because the person who wrote it is the only one who knows why line forty exists. A routine described in ordinary sentences can be read, corrected, and taken over by anyone, which matters far more than it sounds like it should when your billing depends on it.

Whatever you build, add one habit that costs nothing. Once a month, open the Log tab and check that the number of rows matches the number of clients who should have been billed. Scheduled jobs fail quietly, and a thirty second count is the cheapest alarm you will ever install.

Sources

• Google for Developers, Installable triggers, types, behaviour, and account ownership
• Google for Developers, Extending Google Sheets with Apps Script, custom functions and macros
• Google Docs Editors Help, VLOOKUP function syntax and exact match guidance