Intuit QuickBooks Online

Applies to: Tableau Desktop

This article describes how to connect Tableau to Intuit QuickBooks Online data and set up the data source.

Note: Tableau is working on a replacement connector. After it's complete, it will be added to this page.

Warning: The Intuit QuickBooks connector is deprecated as of the 2023.1 release. You can use the connector until it’s been retired. Once the connector is retired, it’s removed from the Tableau user interface and any data sources using the connector will no longer work as expected. The deprecated connector will be retired 1-2 releases after the replacement connector is made available.

Note: For the most up-do-date information on this connector, see the Intuit QuickBooks Online help topic for the current version of Tableau.

Before you begin

Before you begin, gather this connection information:

  • Your email address or user ID and password for your Quickbooks Online account.

Note: To connect Tableau to QuickBooks Online data, you must be a Company Administrator on the QuickBooks Online company. Only one administrator at your company can connect Tableau to QuickBooks Online.

QuickBooks Online doesn't support retrieving the Inventory Adjustment transactions. For information about when Inventory Adjustment transactions will be available in the QuickBooks Online API, see the responses to this question(Link opens in a new window) on the Intuit Developer website.

Make the connection and set up a data source

  1. Start Tableau and under Connect, select Intuit QuickBooks Online. For a complete list of data connections, select More under To a Server. In the tab Tableau opens in your default browser, do the following:
    1. Enter your email address or user ID and password for your QuickBooks Online account.
    2. Select Sign In.

      QuickBooks Online periodically prompts for two-step verification. If this happens, follow the prompts to get a code to confirm your Intuit account.

    3. If more than one company is associated with your account, select the company you want to connect to.
    4. Select Authorize to authorize Intuit to securely share your data with Tableau Desktop.

      5. If Tableau can't make the connection, verify that your credentials are correct. If you still can't connect, your computer is having trouble locating the server. Contact your network administrator or database administrator for more assistance.

  2. On the data source page, do the following:

    1. (Optional) Select the default data source name at the top of the page, and then enter a unique data source name for use in Tableau. For example, use a data source naming convention that helps other users of the data source figure out which data source to connect to.

    2. Under Table, select a table and drag it to the top of the canvas.

    3. Select the Sheet 1 tab to start your analysis.

      After you select the sheet tab, Tableau imports the data by creating an extract. Note that Tableau Desktop supports only extracts for QuickBooks Online. You can update the data by refreshing the extract. For more information, see Extract Your Data.

      Creating extracts may take some time depending on the amount of data that is included.

Use the Sales and Expenses Line Items table to create accounting reports

QuickBooks Online aggregates the information needed to create a variety of reports, such as profit and loss (P&L) statements and balance sheets. Because the data is aggregated by QuickBooks Online, you can’t create these kinds of reports in Tableau. To remedy this, we’ve added a table in Tableau, Sales and Expenses Line Items, that includes line item details with all the fields you need to create these reports.

Sales and Expenses Line Items table structure

The Sales and Expenses Line Items table contains information for the following QuickBooks Online transactions (or documents):

Bill

Bill Payment

Credit Memo

Deposit

Estimate

Invoice

Journal Entry

Payment

Purchase

Purchase Order

Refund Receipt

Sales Receipt

Vendor Credit

Most of the transactions have children (or sub-items), for example, an invoice has detail items. Like this example:

Some transactions have group items, which consist of a group of other line items. Like this example:

The Sales and Expenses Line Items table denormalizes the "transaction to items" relationship by including one row per item and duplicating the transaction fields on each row. In the case of a group line item with children, the table includes the group child items instead of the group.

The following image shows how these two invoices appear in Tableau with the denormalized data:

In addition to the transactions tables, the Sales and Expenses Line Items table contains information for the Account table.

Join tables with the Sales and Expenses Line Items table

Using the Sales and Expenses Line Items table, you can join the Account and transactions tables with it to get the detailed information you need for the report you want to create.

To join the Account table with each line in a transaction, join [Line Detail Account Ref Value] in the Sales and Expenses Line Items table to [ID Account] in the Account table.

For the QuickBooks Online transactions tables (for example, Bill, Estimate, Purchase Order and so on), join [Transaction Type] + [ID] + [Line ID] in the Sales and Expenses Line Items table to the equivalent fields in the transactions table. For example, if you create a join with the Bill transactions table, the [ID] field to join on for Bill is [(ID) Bill]; the [Line ID] field to join on for Bill is [Line ID (Bill)], and so on.

The following image shows multiple joins with the Sales and Expenses Line Items table on the left, as well as examples of the join clauses for the Account table and the Bill table on the right.

Create the QuickBooks Online data source

These are the general steps to get you started with making a data source that you can use to create various reports, such as P&L reports:

  1. Connect to QuickBooks Online.
  2. On the Data Source page, under Table, select the Sales And Expenses Line Items table.
  3. Join one or more of the tables listed in Sales and Expenses Line Items table structure to the Sales And Expenses Line Items table to include the data you need for the report you want to create.
  4. After you create your data source, you can build your statements and analyze your data.

Troubleshoot QuickBooks Online errors

You might see one of the following errors when you try to connect Tableau to your QuickBooks Online data.

Workbooks saved in previous versions of Tableau

Workbooks and data sources created prior to version 2018.2 of Tableau will not be able to access tables or fields added in later versions of the connector. To resolve this, open a new workbook and create a new connection to your QuickBooks Online data source. Copy and paste the worksheets you want to keep from the previous connection, or recreate them in Tableau.

Sorry, only administrators can buy apps…

QuickBooks Online requires that each user who connects an app to QuickBooks Online be a Company Administrator. The following Intuit error appears if you are not an administrator on the company account:

Sorry, only administrators of <email address>’s Company can buy apps in the company. Please contact administrator in the company or else choose another company.

To resolve this issue, ask your company account owner to make you a Company Administrator on the account.

Error Code: app_already_purchased

Only one Company Administrator is allowed to connect per application, for example to Tableau Desktop. If someone in your company has already connected Tableau to your QuickBooks Online account, the following Intuit error appears:

Oops! An error has occurred. Please close this window and try again.

Error Code: app_already_purchased

Message: The application has already been subscribed to by another user for this company. Please contact <email address> to make changes to this subscription.

To resolve this issue, you need to ask that Company Administrator to give up the application privileges for Tableau Desktop and provide them to you. If that’s not possible, for example, because the administrator actively uses Tableau, another option is for the administrator to publish the data source to Tableau Server or Tableau Cloud, so that anyone with permission can access the data.

Changing a Connection Opens the My Apps Page

If you need to access another QuickBooks Online company, after you sign in (and possibly go through the two-step authentication process), Intuit might show the My Apps page, rather than the “select the company to connect to” page. To resolve this issue, follow these steps:

  1. Close the My Apps page.

  2. On the Tableau Connect pane, select Intuit QuickBooks Online.

  3. If more than one company is associated with your account, select the company you would like to connect to.

  4. Select Authorize to open the Tableau data source page.

 

See also

Back to top
Thanks for your feedback!Your feedback has been successfully submitted. Thank you!