Search API Connector Documentation

Print

Import QuickBooks Data to Google Sheets

Last Updated On
byAna
< Knowledge Base
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: 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.
    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, 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:

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:

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

16 thoughts on “Import QuickBooks Data to Google Sheets”

  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.

    • I don’t know much 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

  4. 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

    • Hi Mike, sorry for the confusion, the QuickBooks integration uses OAuth (aka the “Connect” button) which is only available on the Business plan. If it was working before and now it’s not, most likely you were in your 2-week free trial that activates all features. Sorry for the inconvenience but I hope that clarifies.

      Reply

  6. The OAuth function in API Connector appears to only allow one active Quickbooks Online account connection at a time. This is problematic as I would like to pull data from multiple client accounts to my google sheets. Is there anyway to resolve and have two (or more) QBO accounts connected to API Connector at once? I can only toggle back and forth manually with no automatic scheduling.

    Reply

    • Hi Seth, sorry, API Connector currently doesn’t support multiple OAuth connection at the same time. This is on the to-do list, please check back in the future (or feel free to message support with your email address if you’d like me to send you an update when it’s ready).

      Reply

Leave a Comment

Table of Contents