Search API Connector Documentation
Import QuickBooks Data to Google Sheets
In this guide, we’ll walk through how to pull QuickBooks data data directly into Google Sheets, using the API Connector add-on for Sheets.
- Before You Begin
- Part 1: Connect to the QuickBooks API
- Part 2: Pull Data from QuickBooks to Sheets
- Part 3: Create a Custom API URL
- Part 4: P&L Report
- Part 5: Handle Pagination
- Part 6: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Connect to the QuickBooks API
The easiest way to get started with the QuickBooks API is through API Connector’s built-in integration.
- Select QuickBooks from the drop-down list of applications
- Under Authorization, click Connect to QuickBooks
- You will be directed to Intuit.com and asked to accept the connection.
- You’ll then be returned to your Google Sheet, and can verify that your QuickBooks connection is active in the Connections screen.
Part 2: Pull Data from QuickBooks to Sheets
Now that we’re connected, let’s pull some data into Sheets.
- Under Endpoint, choose an endpoint. Let’s start by querying accounts.
- Enter your company ID in the
company_idparameter. To get your company ID, click the Gear icon in the top right corner of your navigation bar while logged into QuickBooks, and navigate to Billing and Subscription. You’ll see your company ID listed at the top:
- Select a destination sheet, name your request, and click Run.
Alternatively, you can create a custom request instead of using API Connector’s built-in integration, using any of the endpoints and parameters shown in the API documentation. When you create a custom request, you add your complete URL into the request URL field, select QuickBooks from the OAuth menu, and add 2 headers:
Custom requests are useful for creating more complex queries that include dates and other filters. To easily convert your preset request to a custom request, click Output Options > More Options > Add Request URL. This will print your URL into your sheet where you can copy and paste it into a custom request.
Part 4: P&L Report
By default, the P&L report comes through in a format that doesn’t work well with the tabular nature of sheets. We’ll add some custom processing for this in the future, but for now you can grab this data as follows:
Clean up the data with a JMESPath expression. The specifics depend on your exact data structure, but in general it should look like this:
For a high-level summary of Cost of Goods, Revenue, Profit, etc, use the “Summary” node like this:
Part 5: Handle Pagination
By default, QuickBooks will only return 100 records at a time, as described in their documentation.
To get more, use the STARTPOSITION and MAXRESULTS parameters as shown below. MAXRESULTS accepts any number up to 1000. These URLs can be stacked to retrieve multiple blocks of 1000 (info: Multi-Query Requests).
Part 6: API Documentation
Official API documentation: https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/account