Custom recurring invoices with Google Scripts

All I wanted to do was send a monthly invoice that covered the whole of the previous month.  It was important that it call out the full dates the invoice covered.  For example, on May 1, I wanted to generate an invoice that said "Services rendered April 1, 2021 - April 30, 2021".

What Didn't Work

I figured lots of people use invoices, there must be an existing solution I can use to do this easily...

Harvest

I've used Harvest in the past, and it's quite decent.  It supports recurring invoices, however, the variables you can use are very limited.

Harvest Variables

What they also fail to mention, is you can't use all those variables everywhere.  For example, if you try to use %company_name% in a recurring invoice subject line, it doesn't get replaced and it sends that verbatim to your client.  Oops!

Invoice Ninja

Some searching led me to Invoice Ninja, an open source project you can self host or pay them to host for you.  Excellent, I love that model.  Plus you can play with it without even creating an account!  I really wanted this to work...

Invoice Ninja Variables

The variables almost work.  I could use :MONTH-1, but I couldn't get the first and last days.  Plus the year will become an issue at boundaries.  For example, my January 1 invoice for December would be wrong.

Email PDFs only available in pro

The other issue was I really wanted to email a PDF.  The way it worked by default was similar to how Quickbooks works: it emails your client a link they have to click on to view the invoice online.  I don't like that.  It appears the only way to email a PDF is to use the paid pro version or self-host.

I briefly looked at self-hosting.  They have a docker compose and it doesn't look too bad, but it needs mysql, nginx, and a few others.  Way more than I wanted to deal with for this project.

Wave

I use Wave for other projects and really like it.  I use their invoices regularly, but never used a recurring invoice.  I even run code for other projects that generates invoices via their API.  However, their invoices don't support variables, and it doesn't appear they will anytime soon.

Zapier

I also use Zapier for other things already.  I thought maybe I could use them to force another site to do what I wanted.  You can create a zap to run every month, but it's also limited in manipulating the dates.

Zapier date options

You might be able to get this to work using the Add/Subtract time trigger, but after playing with for a bit, I gave up.  It was getting too complex, and oddly, they don't have any way to specify you want the month name, instead of number.

The Rest

I also took a look at all of these:

All of these either didn't describe how recurring invoices work or were just way too expensive.

What worked: Google Scripts

After spending all the time searching, I decided I could just code it up in Google Scripts!  A few hours later, I had Invoicer ready and doing everything I need.

How it works

I created a spreadsheet with two sheets: Summary and 1.  The Summary page has settings and some basic tracking for all the invoices generated.

Spreadsheet Summary Sheet

The second sheet, 1, is based on this invoice template:

A blank invoice

So, using this, the code generates and emails PDF invoices based on a duplicate of Sheet 1.  All you need to do is fill in the EMAIL_TO, SUBJECT_PREFIX, and PDF_NAME_PREFIX settings.  As you mark invoices as paid, the corresponding sheet is automatically hidden on the next run.  Setup a time-based trigger to call it and done!  

Time-based monthly trigger

The nice things about this are its free, easily extendable, and very low maintenance.  I invite you to check out the code an adapt to your needs.