Build your own email-based expense tracking with Google Apps Script

My wife recently started a new job as an independent contractor. This form of self-employment comes with some very interesting tax implications and potentially significant write-offs for expenses related to doing business.

This presented me with an opportunity to combine two of my greatest hobbies — software engineering and personal finance — into an automated expense tracking system to assist in the preparation of Schedule C.

Initially I had an elaborate plan that involved Amazon SES; Lambdas processing SQS queues, performing OCR on email attachments, calling Google Sheet APIs and saving the receipts to S3… Yes, a little excessive for tracking some receipts and it would require a lot of engineering know-how to deploy (i.e. not something that would be easily shareable).

Instead, one afternoon in a coffee shop on a recent federal holiday, I decided to play around with the power of Google Apps Script and ended up building a heavily simplified version of what is described above. The end result is a fairly basic scheduled script that reads labelled emails, saves the attachments to Google Drive and stores an expense row with receipt URL in a Google Sheet.

See below for how you can set this up for yourself!

Saving attachments from email to Google Drive

1. Gmail filter configuration to manage labelling of incoming receipts

A goal of this project was to ensure that everything could be performed without specialized apps or websites: the entire process should be email driven.

There is a well-known “quirk” of Google mail that allows you to include anything you like after the + sign of your GMail address. This is extraordinarily useful for setting up automatic filters to apply labels.

This is the filter that needs to be setup:

GMail filter for youremail+expense@gmail.com

Notes:

• Optionally specify that there must be an attachment
• You could add some specific Subject filtering if that’s helpful
• +expense is just an example: pick whatever makes sense for you

Then, simply configure this Filter to add a label: in this example, that label is 1099expense but it can be anything you like (just make sure to update the Apps Script code)

2. Create an empty expense tracking spreadsheet

I used a fairly basic 1099 Schedule C expense tracking sheet I found online: the specifics are not important: just make sure it has a name you’ll recognize and a sheet called All Business Expenses

The columns output by the Apps Script are (in order):

• Date
• Merchant
• Expense amount
• Schedule C category
• Expense category subtype
• Email ID
• Link to email receipt

Schedule C Expense Tracking “All Business Expenses” Sheet

3. Setup the Apps Script

Now the fun part! Here’s a brief overview of what the script does:

• Creates labels in your GMail if they do not exist
• Searches for everything with the 1099expense label (the unprocessed queue)
• For each message: parse the subject to extract the expense amount (numeric) and (optionally) the category and merchant name (separated by a — in the email subject)
• The script will also copy all attachments in the email into a unique subdirectory of your Google Drive (based on the date of the email and the email ID)
• Finally, insert a new expense row in your Google Sheet with the merchant name, date (of the email), amount, expense category, email ID and a link to the uploaded receipts

Step-by-step instructions

1. Create a new project in Apps Script
2. Copy the script into your new project and configure the variables at the top (the only thing you have to change is SPREADSHEET_URL — link to the spreadsheet you created above)
3. Configure the “Triggers” — I opted to have it run every hour

Triggers Menu

Trigger Configuration

4. Usage

Once you’ve setup the script above to run every hour, start emailing your receipts to your chosen email: youremail+expense@gmail.com with the subject format: dollar_amount — expense_category-merchant_name (e.g. 40-Meals-Souvla )

Your GMail filter will add the label 1099expense and then, hourly (or however you configured it), the Apps Script will read through that folder and insert any new expenses into your spreadsheet (and move the processed emails to 1099expense_done afterwards to prevent duplicate processing)

That’s it! Happy expensing!

Certainly there’s a lot more that could be done with this script to handle errors more cleanly; support more configurations and options but hopefully this is enough to get you started with Apps Script.

Comment below how you adapted this basic example for your own use-case. Please feel free to propose improvements (such as better error handling or multi-message thread support) either in the comments below or in the Gist directly.