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

If you haven’t connected API Connector to QuickBooks before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
  2. In the list of available connections, find QuickBooks and click Connect.
    quickbooks-img2
  3. You will be directed to Intuit.com and asked to sign in, if you haven’t already. You’ll then be returned to your Google Sheet, and can verify that your QuickBooks connection is active in the Connections screen.

Part 2: Create Your QuickBooks API Request URL

For our first request, we’ll get some basic information about your QuickBooks account.

  • API root: https://quickbooks.api.intuit.com
  • Endpoint: /v3/company/YOUR_COMPANY_ID/companyinfo/YOUR_COMPANY_ID

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 Subscriptions. You’ll see your company ID listed at the top:
quickbooks-img4

Putting it all together, we get the full API Request URL. Note that you enter your company ID twice:

https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/companyinfo/YOUR_COMPANY_ID

Part 3: Pull QuickBooks API Data into Sheets

Now let’s add this URL into API Connector.

  1. Back in the Create Request interface, enter the Request URL we created above.
    quickbooks-url
  2. Under OAuth, choose QuickBooks from the dropdown menu. You should see the green “Connected” badge.
    quickbooks-connected
  3. In the Headers section, enter the following sets of key-value pairs:
    acceptapplication/json
    content-typeapplication/json
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see some information about your company populate your sheet.
    quickbooks-results1

Part 4: More Example API URLs

The QuickBooks API uses a query language similar to SQL. You can access the full API documentation here, but only the “query” endpoint responses work well with Sheets. Therefore, it’s recommended to start with the following examples. Try the following URLs (one at a time), substituting in actual values where indicated.

  • Chart of Accounts
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Account
  • Bills
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Bill
  • Customers
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Customer
  • Employees
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Employee
  • Invoices
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Invoice
  • Items
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Item
  • Payments
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Payment
  • Vendors
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/query?query=select * from Vendor

    quickbooks-img8
  • P&L Report
    https://quickbooks.api.intuit.com/v3/company/YOUR_COMPANY_ID/reports/ProfitAndLoss?start_date=2021-01-01&end_date=2021-12-31
    • for the P&L report, you’ll probably want to clean up the data with a JMESPath expression. The specifics depend on your exact data structure, but in general it should look like one of the following for granular data:
      • Rows.Row[].Rows.Row[].ColData
      • Rows.Row[].Rows.Row[].Rows.Row[].ColData
      • Rows.Row[].Rows.Row[].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, can you please check if adding the 0 resolves 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 Comment

Table of Contents