Automated invoice updates and income forecasting with Harvest, Zapier & Google Sheets

At the end of 2016, I was in search to get a better pulse on my business. I wanted to be able to set goals, plan, view reports, and easily track my efforts with my web consulting business. I was tired of looking at several tools to find this information. I needed a tool I could quickly glance at that stays updated with minimal effort. I started looking at Harvest competitors that would scratch this itch.

At the end of 2016, I was in search to get a better pulse on my business. I wanted to be able to set goals, plan, view reports, and easily track my efforts with my web consulting business. I was tired of looking at several tools to find this information. I needed a tool I could quickly glance at that stays updated with minimal effort. I started looking at Harvest competitors that would scratch this itch.

I trialed the new Freshbooks and Harpoon in December with the hopes of starting clean at the beginning of 2017. These apps are fairly comparable–some do things better than the others. However, through this process I was really impressed and inspired by some of Harpoon’s features:

• Goal setting and tracking
• Scheduling
• Forecasting Revenue
• Business analytics

It does a fantastic job of keeping a pulse on your business. This app is really designed to help you achieve your goals.

In the end, I ended up staying with Harvest because of these reasons:

1. Deeply invested (customer since 2007)
2. Integrations with other tools
3. API

Native limitations with Harvest triggers

I’ve been using a Google sheet the past several years to help me distribute and set aside money for quarterly taxes. This was very basic and high level. Every time an invoice was payed, I would manually add a new row to the sheet. If an invoice was paid online with Stripe, a zap from Zapierwould automatically add the payment (row) to the spreadsheet. This was as far as I got with automating that sheet.

I always wished I could do more and the available triggers for Harvest in Zapier are limited to just new client|project|invoice|task|etc triggers.

Automating Harvest invoices in Google Sheets

I was really inspired by Harpoon’s business analytics reporting, but I wasn’t able to switch nor did I want to manage two platforms to benefit from the analytics. Harpoon really gave me a new perspective on how I should be looking at my business. So I thought to myself “how can I recreate this with my current set of tools?”

The “light bulb moment” finally hit me a couple days ago as I’ve been thinking about this for several weeks. I had the idea, now I just need to test it.

First step: import all Harvest invoices into Google worksheets.

I needed to get a complete historical snapshot of invoices in a worksheet to be able to run reports and analytics accurately. This was the easy part. I logged into Harvest and exported out all my invoices in CSV format and imported them into the worksheet.

I ended up removing unnecessary columns to clean it up and organize it a bit. Here are the modified columns that I kept:

1. Status (I added this column)
2. Issue Date
3. Last Payment
4. Invoice ID
5. Client
6. Subject
7. Invoice Amount
8. Paid Amount
9. Balance
10. Recurring ID

Most of these columns are used in some sort of fashion to run reports. A few takeaways:

1. The Status column can be queried to show projected vs. actual income.
2. Invoice ID is used to search, select, and update the spreadsheet row with a Zapier webhook.
3. Recurring ID is the unique ID Harvest assigns to all recurring invoices. This basically flags monthly recurring revenue (MRR) so it can be reported.

Second step: add new invoices in the worksheet automatically.

This is a straight forward zap to setup–biggest takeaway is to map Harvest data into the correct columns in the worksheet. Now, I can start logging all invoices and update them in the future with the next step without having to do manual data entry.

Last step: update invoice status in the spreadsheet when an invoice is sent or paid in Harvest.

This is where thinking outside the box helped me solve the limited trigger issue of updating an invoice on sent or paid. I’ve done a lot of very cool things with Zapier, but ’m most proud of this zap since it's the most creative.

Remember Greasemonkey? The Add-on for Firefox that allows you to customize a web page or how it behaves. Well, Chrome has one too, Tampermonkey.

I thought: “What if I could capture the data I needed while updating an invoice in Harvest and send that off to Zapier?”

So I wrote a Userscript to do just that and post the data to the Zapier Webhook URL. Update line 61 with your unique webhook URL from Zapier.

The only caveat are invoices that are paid with Stripe are automatically marked as paid in Harvest, so I can’t update the row in the spreadsheet. Still tinkering to see if I can find a solution for this.

Creating Business Analytics in Google Sheets

Harpoon really shed a new perspective on how I should be thinking about my business and tracking progress.

Before, I haphazardly set a yearly goal and would “see” if I could reach it. I would occasionally login to my accounting software and look at my income year to date and compare it to the prior period to see if I was ahead or behind. I didn’t know what my projected income was. I could only see outstanding invoices in Harvest. I was tired of this lax approach. I wanted more predictability with my revenue.

The thing I really liked about Harpoon was the way it reported and planned your revenue. I could quickly see collected revenue vs. expected revenue. I could see monthly what was expected to come in. Harpoon breaks your yearly goal out into quarters and months. No longer am I trying to hit a yearly goal in one big chunk. I’m now looking at my income monthly and trying to hit those targets and adjust accordingly.

Honestly, I can’t believe I’m just now doing this. It’s great to be able to see ahead a couple months and plan business development.

I put together a video that explains everything in greater detail if your interested. There is too much going on in that spreadsheet to detail it here ;)