API Connector Documentation

Print

Import QuickBooks Data to Google Sheets

Last Updated OnJanuary 9, 2024
byAna
< Knowledge Base

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

quickbooks-response1

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.
quickbooks-img9

To get more, use the STARTPOSITION and MAXRESULTS parameters as shown below. MAXRESULTS accepts any number up to 1000.
quickbooks-img10

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

26 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

    • Hi Mike, 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.

      Reply

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

  6. Hi Anna,

    Thank you for the article.
    I need help with querying items, I do get most of the information but not the product Sku.

    How do I go about retrieving everything in items plus the product Sku?

    Hope this makes sense, I'm very new to this.

    Reply

    • Hey Richard, their sample response for the Items endpoint doesn't contain any field named product SKU, there's just an QueryResponse.Item.Id field. I'm wondering if the SKU is the same as the ID, can you please look for the QueryResponse.Item.Id field in your report and see if it contains the SKU?
      Update: I found this useful post that explains that it's related to the API version. By default SKU isn't returned unless you add a minorversion parameter to your request, e.g. minorversion=65.

      Reply

      • Thank you so much Works now, I never added the "&", I found that you just have to add &minoversion=65 to your query for it to work.

      • Great, I'm glad that fixed it. We also added that parameter into API Connector's preset integration for Quickbooks, so you can include it there as well.

  7. Hey All - had a question: I'm trying to pull class into a transaction report query as a column, I'm having an issue with the JMESPath to pull in the class. Would anyone know what the identifier is to pull in the class?

    Thanks all!

    Reply

    • Specifically, using the transaction list query https://quickbooks.api.intuit.com/v3/company//reports/TransactionList?start_date=2023-06-13&end_date=2023-06-19&group_by=Customer

      Reply

      • Hey Nathan, I don't see "class" as a field in the response from Quickbooks. Do you see it returned in the response you get back? (Click Edit fields > show raw response to view) If so, can you please contact support with a sample of the JSON response so I can take a look?

  8. For a custom OAuth connection, what do I enter for Name, Authorization Base URL, and Token URL?

    For the Client ID and Secret, do I use the ones under Production (and not Development)?

    Reply

    • API Connector already has a native Quickbooks connector, is there a reason you want to create your own? If it's because you're receiving an error we should address that rather than create a new custom OAuth connector. With that said, if you still want to make your own custom OAuth connection, it would be like this:
      Type: Authorization Code
      Authorization Request URL: https://appcenter.intuit.com/connect/oauth2?scope=com.intuit.quickbooks.accounting
      Token URL: https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer
      Client ID & Secret: provided by Quickbooks (you'll need to set up an app in their developer site. Use production credentials)

      Reply

  9. We are using Oauth to retrieve the QBO Invoices. But I dont see how we can use the pagination here. It looks like pagination can only be handled through the custom API calls?

    Reply

Leave a Comment

Jump To