Search API Connector Documentation

Print

Import QuickBooks Data to Google Sheets

premium

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

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
    quickbooks-application
  2. Under Authorization, click Connect to QuickBooks
    quickbooks-authorization
  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.
    quickbooks-connected

Part 2: Fetch Data from QuickBooks

Now that we’re connected, let’s pull some data into Sheets.

  1. Under Endpoint, choose an endpoint. Let’s start by querying accounts.
    quickbooks-endpoints
  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:quickbooks-img4
  3. Select a destination sheet, name your request, and click Run.
    quickbooks-response

Part 3: Create a Custom API Request

Alternatively, you can create a custom request instead of using API Connector’s built-in integration. 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:

acceptapplication/json
content-typeapplication/json


quickbooks-results1

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:

Request URL

https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/reports/ProfitAndLoss?start_date=2021-01-01&end_date=2021-12-31

Clean up the data with a JMESPath expression. The specifics depend on your exact data structure, but in general it should look like this:

Rows.Row[].Rows.Row[].ColData

For a high-level summary of Cost of Goods, Revenue, Profit, etc, use the “Summary” node like this:

Rows.Row[].Summary.ColData

Part 5: Handle Pagination

By default, QuickBooks will only return 100 records at a time, as described in their documentation.
quickbooks-img9

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).
quickbooks-img10

Part 6: API Documentation

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

Previous Import Quaderno Data to Google Sheets
Next Import Quora Ads Data to Google Sheets

14 thoughts on “Import QuickBooks Data to Google Sheets”

    • Can you please double-check that you have access to the account you’re querying? That’s the only way I can produce a 403 error in my own tests. If you still have issues, please message support with a link to your sheet or screenshot of your request setup.

      Reply
  1. Hi Ana,

    Did you find how to get invoices from a custom period:

    This is what I was using:

    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Invoice Item MAXRESULTS 1000

    Reply
      • Awesome, yeah that looks right to me. Based on their docs you can use MetaData.CreateTime or MetaData.LastUpdatedTime in your query.

      • I saw this example in their docs, does it work for you?
        SELECT * FROM Invoice WHERE MetaData.CreateTime >= '2009-10-14T04:05:05-07:00' AND MetaData.CreateTime <= '2012-10-14T04:05:05-07:00'

      • Hi Ana,

        I used: https://quickbooks.api.intuit.com/v3/company/+++Inputs!C3+++/query?query=SELECT * FROM Invoice WHERE MetaData.CreateTime >= '2021-01-01T00:00:00-00:00' AND MetaData.CreateTime <= '2021-04-01T00:0:00-00:00'

        I get :

        Completed with errors
        – Server responded with an error (400) show response{"Fault":{"Error":[{"Message":"Demande non valide","Detail":"Erreur de validation de demande : value 2021-04-01T00:0:00-00:00 is not valid for property 'MetaData.CreateTime'","code":"4001"}],"type":"ValidationFault"},"time":"2021-04-19T18:04:48.423-07:00"}

        Do you see why?

      • It looks like you’ve written 2021-04-01T00:0:00 instead of 2021-04-01T00:00:00, adding the 0 should resolve the issue.

    • Sorry, I don’t really know anything about Quickbooks desktop, but I don’t think it will work here since API Connector connects to services on the internet while it sounds like this would be something local on your computer.

      Reply
  2. Hi Ana,

    With regards to JMESpath:
    Rows.Row[].Rows.Row[].ColData
    Rows.Row[].Rows.Row[].Rows.Row[].ColData
    Rows.Row[].Rows.Row[].Rows.Row[].Rows.Row[].ColData

    My JSON results seem to return data for each of the JMESpath queries above.

    I’ve tried researching how to combine queries, but alas.

    Do you have any reccomendation how to use JMES to structure all the JSON data at once?

    (I already tried this –> https://mixedanalytics.com/knowledge-base/filter-specific-fields-values/#articleTOC_8 but it didn’t work)

    Thanks!

    Reply
    • Sorry, you can’t combine these queries, they were just examples on how to retrieve data at different levels. Unfortunately this particular response data isn’t designed for a tabular output, it needs to be processed and reconfigured with some custom code.

      Reply

Leave a Reply to Pascal Flamand Cancel reply

Table of Contents