Recently I was tasked with building a Quickbooks Online (QBO) integration to process the Profit and Loss (P&L) report and determine the revenues, cost of goods sold (COGS), and expenses of departments — here’s how I did it.

This article is not about building an OAuth flow or pulling data from the Quickbooks API using a cron job. That process is relatively boilerplate and has been solved by tools such as the one I’ll be using. If building that yourself interests you, check out my article on using Singer to do just that.

Instead, this article will walk you through the harder part — how to make sense of the data.

Get the data

As I mentioned above, for this article I’ll be using hotglue to handle all the boilerplate of building a QBO integration for a SaaS platform. This includes:

• creating an OAuth authorization flow
• enable users to connect Quickbooks in your UI and monitor the connection
• pulling the necessary data from the Quickbooks API

Create the authorization flow

In hotglue, I will create a new flow and add Quickbooks sandbox as a source. To do this I merely provide my OAuth credentials and login to my Quickbooks sandbox account. If you do not have OAuth credentials already, you can just select “Use testing tokens”.

Select the data we need

Now we can select what data we need from Quickbooks. We will select Account (Chart of Accounts), Item (Products and Services), Purchase (Expenses), Invoice, and Profit and Loss Report.

This will provide us with the reference data we’ll need to accurately categorize transactions.

Add the integration to your UI

With some basic JavaScript I was able to embed the hotglue widget, which enables users to connect their Quickbooks account, refresh the synced data, and monitor when it was last refreshed.

Tag the transactions

Quickbooks does not natively support tagging transactions under a specific department or project. To accomplish this, I used the Quickbooks class field to tag transactions under a Department/Project. For example, the Invoice below has been tagged Pest Control:Project X meaning Pest Control department, Project X.

Process the data

Now for the fun part. We’ll use a Python script using pandas and gluestick to convert the data from Quickbooks into a JSON file that looks something like below.

What we’re trying to produce

As you can see below, the JSON file takes every transaction in our P&L report and categorizes it under Project Xin the Pest Control department. Further, it categorizes each transaction as revenue , cogs (Cost of Goods Sold), labor , and costs (Expenses).

{
  "Pest Control": {
    "Project X": {
      "revenues": {
        "2021-11": {
          "Pest Control Services": {
            "Pest Control Services": {
              "Bed Bug Removal": {
                "total": 350,
                "qty": "10",
                "rate": 35
              }
            }
          }
        }
      },
      "cogs": {
        "2021-11": {
          "Supplies & Materials - COGS": {
            "Pest Control Services": {
              "Bed Bug Removal": {
                "total": 100,
                "qty": 1,
                "rate": 100
              }
            }
          }
        }
      },
      "labor": {
        "2021-11": {
          "Employee": {
            "Inspector": {
              "Employee Benefits": 10.8,
              "Payroll Tax": 61.2,
              "Payroll Wage Expenses": 800
            }
          }
        }
      },
      "costs": {
        "2021-11": {
          "Project Expenses": {
            "Automobile": {
              "Fuel": 100
            }
          }
        }
      }
    }
  }
}

Write the script

hotglue has built-in functionality to run a Python script every time new data is synced from the Quickbooks API, and will avoid syncing old data again (incremental sync).

You can go through each step of the script here.

If you’re using hotglue to create your integration, you can learn how to write and deploy scripts in the docs.

Run it all together

Choose where the data should go

If you’re using hotglue, you need to specify a place for the final data to be delivered (a target). By default, hotglue will email new data to you using Mailgun. I used Google Cloud Storage for our Quickbooks integration.

Run a job

Now we can run a sync job. This will connect to the Quickbooks API, query any new data, save it as a CSV, run our transformation script, and upload the resulting data to our target (Google Cloud Storage).

Now we can see the final actuals.json file in Google Cloud Storage!

Conclusion

In this article we went through creating a Quickbooks integration and processing the resulting CSV data into a JSON payload that can be used by a SaaS application.

Hopefully this helps you consider how to build your own SaaS integrations. I’d highly recommend using a tool like hotglue to handle the boilerplate integration steps so you can focus on extracting the data you need rather than authorization and orchestration issues.

Thanks for reading!