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.

Contents

• 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: Other Reports
• 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.

1. Select QuickBooks from the drop-down list of applications
   
2. Under Authorization, click Connect to QuickBooks
   
3. You will be directed to Intuit.com and asked to accept the connection.
4. 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.

1. Under Endpoint, choose an endpoint. Let’s start by querying accounts.
   
2. Enter your company ID in the company_id parameter. 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:
3. Select a destination sheet, name your request, and click Run.
   

Part 3: Create a Custom API Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the requests shown in the API documentation. This is useful for more advanced queries that include filters, pagination, and other elements not yet supported by our preset integration. Here's an example request setup:

• Application: Custom
• Method: GET
• Request URL: https://quickbooks.api.intuit.com/v3/company/9130355007498326/query?query=SELECT * FROM Item WHERE Metadata.LastUpdatedTime >= '2022-01-01' STARTPOSITION 1 MAXRESULTS 1000&minorversion=65
• OAuth: Quickbooks
• Headers:
  • Content-type:application/json

Part 4: Other Reports

By default, several of Quickbook's reports come through in a format that doesn't work well with the tabular nature of sheets, so they haven't yet been added to API Connector's list of preset endpoints. We'll add some automated pre-processing for this in the near future, but for now you can grab this data with custom requests, and clean them up with a JMESPath expression. Here are some examples:

P&L Report

• Application: Custom
• Method: GET
• Request URL: https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/reports/ProfitAndLoss?start_date=2022-01-01&end_date=2022-12-31
• OAuth: Quickbooks
• Headers:
  • Content-type:application/json
• JMESPath: Rows.Row[].Rows.Row[].ColData.{account:[0],money:[1]}

TransactionList Report

• Application: Custom
• Method: GET
• Request URL:  https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/reports/TransactionList?start_date=2022-01-01&end_date=2022-12-31&group_by=Customer
• OAuth: Quickbooks
• Headers:
  • Content-type:application/json
• JMESPath: Rows.Row[].Rows.Row[].ColData.{tx_date:[0],txn_type:[1],doc_num:[2],is_no_post:[3],name:[4],memo:[5],account_name:[6],other_account:[7],subt_nat_amount:[8]}

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), e.g.

• https://quickbooks.api.intuit.com/v3/company/111111111/query?query=SELECT * FROM Invoices STARTPOSITION 1 MAXRESULTS 1000
• https://quickbooks.api.intuit.com/v3/company/111111111/query?query=SELECT * FROM Invoices STARTPOSITION 1001 MAXRESULTS 1000
• https://quickbooks.api.intuit.com/v3/company/111111111/query?query=SELECT * FROM Invoices STARTPOSITION 2001 MAXRESULTS 1000

Part 6: API Documentation

Official API documentation: https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/account