iDashboards is able to connect to the QuickBooks Online API by using a built-in API within iDashboards Data Hub. This document goes over the process required to create an API connection to QuickBooks Online. This process will flow from initializing the API connection to creating a job in the ETL tool that transfers that API data into a database table/data source, to adding a chart connecting to that data source.
Please Note: QB recently switched from OAuth 1.0 to 2.0 and iDashboards only supports 2.0. If your developer account is an older one, only new developer accounts get 2.0 right now so developer accounts created prior to the rollout don't get 2.0 yet. A new developer account will have to be created with the 2.0 OAuth in order to be able to connect.
Connecting to QuickBooks Online
If you have not already done so, go to QuickBooks Online and setup your developer account. Keep in mind that https protocol is required to be able to connect to Production QuickBooks data. Your iDashboards website must have SSL set up and a fully qualified domain name (domain.com) using localhost or your IP address will not work with this API.
Follow the link to Sign In or Create your Developer Account.
- Sign In to your QuickBooks Online account
- Once you are logged in select Create App
- Click the QuickBooks Online and Payments box
- Give your app a name here we are calling it iDashboards
- Click Create App
- Congratulations! You've now successfully created the iDashboards QuickBooks Online App for your API Connection! Your screen will now look like this:
- Select Production in the left column
- Enter the Terms of Service Links
- End User License Agreement URL
- Then scroll down and click SAVENow, click on Keys & OAuth will take you to the page where you can get the Client ID and Client Secret. ***Keep this page open for future reference.***
- The Client ID and Client Secret from this page will be added to the same fields in the Add Account window in the iDashboards Data Hub.
Log into the iDashboards Data Hub http://<server:port>/idbdata/
- Select System then API Accounts
- You will now see a new screen that shows you the API Accounts options as well as any existing API connections you might have already configured.
- Click Add Account
- Now select the QuickBooks Online then click Next
- Copy the Redirect URI from the iDashboards Data Hub window
- Go back to Intuit Developer
- Click Add URIPlease Note: The Redirect URI need to be in the format of
- https:<server.domain>:<port>/idbdata/console/api/callback using
- http://localhost:<port>/idbdata console/api/callback will ONLY be able to connect to Development data
- Paste this value into the Redirect URI 2 and click Save
- Now copy the Client ID from Intuit Developer and paste into the iDashboards Data Hub Add Account window
- Now copy the Client Secret from Intuit Developer and paste into the iDashboards Data Hub Add Account window
- Click Next and select Production
- Click Log in
- Now enter in your username and password for the QuickBooks Developer account in the new window
- Click Sign In
- Next, a Lets make sure it’s you window will appear. choose an option to get your confirmation code
- Click Continue.
- After you click Continue, a new window will appear
- Enter the code that was sent to you
- If you have multiple companies in QuickenBooks Online you will get a selection of which company to connect
- Click Authorize
- You now see the Account Created Successfully window. You can close that window.
- Now you can go back to iDashboards Data Hub tab, if necessary refresh the connection. You will see the newly added QuickBooks Online connection
Congratulations! You have successfully completed adding your QuickBooks Online API connection
Creating Data Sets
From here you are now ready to proceed to creating a new data set in the ETL tool.
- Select Data Sets
- Select New Data Set then Cloud API on the left
- Choose QuickBooks Online and click Next
- Select the Query Type Report was selected in this example
- Select your Report from the drop down menu
- Balance Sheet was selected in this example
- Now Select a Date Range
- Select the Columns you would like to be a part of this Data Set by Selecting them on the left and using the top arrow to move it over to the right Selected Columns
- Click Save
- You now see the following screen to verify your new Data Set in the Data Hub.
- You can also make modifications to the Data Set in this window. For now, just click Save
- Congratulations you just created your first QuickBooks Online Data Set!
- Your new Data Set will now be available in the Data Hub and you can access it under Edit Data Sets
Creating an ETL job
You will now be on the Data Hub Lobby screen. From here you can select the +NEW ETL JOB icon.
What is an ETL job?
- E - Extract Data from your source
- T - Transform Data (make changes)
- L - Load Data (Place into a target table)
- Here are the various types of transformations available:
Now looking back at the previous picture if you Click and Drag the E, T, and L you can set up your ETL job.
Once you click on the E to Extract your data, you get the Extract Data (1) box. When you hoover your mouse over it you get the gear on the left (configure the task), the up arrow in the middle to send your data to be transformed (T) or loaded (L). If you do not have any transformations that need to be done, you can pull down the L box and connect the E and L with the up arrow by dragging it from the E to the L box.
Connecting the E and L are as shown below, doing so allows your data to be used to make dashboards, charts and picklists.
Once the job has been run to create the table, it has added the data to the data store target table you selected. You now have a data source that contains your QuickBooks Online data and you are ready to build a chart from this data.
Redirect URI is not correct:
This could be as simple as matching your callback URL to the redirect URI in Quickbooks as shown below.
This could also be due to the regional limitation for connection. Only the following countries can connect to the API.
- United States
- United Kingdom
If you reach an error such as the error below when attempting to authorize the API connection, the new iDashboards URI has not been added to QuickBooks.
Disclaimer: iDashboards Technical Support Engineers are not proficient in the Management and Integration of every API that exists as new APIs are being created everyday with each company that wants to provide seamless integration with other software. However, we do our best to help you make these connections because we want to assist you in your success. If you do not see an article for a connection you would like to make please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this integration.