Skip to Main Content
Official App
Free – Google Play
Get it
FreshBooks is Loved by American Small Business Owners
FreshBooks is Loved by Canadian Small Business Owners
FreshBooks is Loved by Small Business Owners in the UK
Dev Blog

Track Freshbooks Expenses in Google Docs with PHP and XML

by Guest Writer on May 4/2011

Post contributed by Ben Buckman from New Leaf Digital

Following the days of calculations that went into my taxes for last year, I decided to try automating as much of my financial forecasting and calculating as possible for the future. Deducting business expenses was a particularly time-consuming task, with different categories of expenses being deducted at different percentages. So I thought, why not mashup the expenses which I already record and categorize in FreshBooks, with spreadsheets in Google Docs that do the rest of the math.

In a nutshell, the solution I came up with uses a PHP script to pull expenses from the Freshbooks API into XML, then GDocs reads the XML into a spreadsheet, and calculates deductions based on expense categories, aggregated for each quarter’s estimated taxes. Here’s the process:

1. FreshBooks Expenses to XML

Building on an existing freshbooks-php library, I wrote a PHP script called freshbooks_expenses_xml. (Link goes to GitHub.)

To set it up, create a keys.php file, and put the whole package on your server somewhere. Play with the parameters described in the README to get different XML output.

2. XML to Google Docs

In cell A1 of a clean spreadsheet, enter this function:

=importXML(“”, “//expenses/*”).

GDocs will fetch the data and populate the spreadsheet. (Note: I had some trouble making the headers consistent with the columns, and worked around it; you might want to do the same by omitting headers=1 in the function and putting in your own headers.)

3. Making useful tax calculations with the data

Different expense categories are deducted at different rates, for example, “Meals and Entertainment” is generally deducted at 50%, but hosting services are 100% deductible. (Disclaimer: Do not take this as tax advice! Do your own research on the percentages.) This kind of calculation is easy to do with custom GDocs functions. (To write a custom function, go to Tools > Scripts > Script Editor.) For example:

function taxDeductionByCategory(category, amount) {
  switch(category) {
    case 'Meals & Entertainment':
      return amount / 2;
      return amount;

I run this function in a Deduction column alongside each expense record.

Next to deductions. I have a column for each Tax Quarter that’s pulled into a separate Income sheet. I use sumif() on the range in the expenses sheet containing the calculated deductions for that quarter, times my expected tax rate, and I know how much tax to pay each quarter.

The next step would be to pull in revenue from Freshbooks as well. Anyone is welcome to add to the project in Github!