This document goes over the process required to create an API connection to Quick Books 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.
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.
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.
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 SAVE
Now, 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 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
Copy the Redirect URI from the iDashboards Data Hub window
Go back to Intuit Developer
Click Add URI
Paste this value into the Redirect URI 2 and click Save
Now copy the Client ID from Intuit Developer
Paste into the iDashboards Data Hub Add Account window
Now copy the Client Secret from Intuit Developer
Paste into the iDashboards Data Hub Add Account window
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
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
Now you will see the Authorize window
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
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
Select Cloud API on the left
Choose QuickBooks Online
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
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
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.