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.
Table of Contents
If you have not already done so, go to QuickBooks Online and setup your developer account. From the link below, you can also follow the steps to begin creating your first API request, create a QuickBooks Online app, and begin coding using your provided production keys. Keep in mind that https protocol is required to be able to connect to Production QuickBooks data. Follow the link below to begin.
Sign In to your QuickBooks Online account. There may be additional setup required if your account is not already a developer account.
Once you are logged in, click on Select APIs.
Now pick the API that you want to pull data from.
You will now see a new window with several tabs. Click on the Settings tab first.
Now fill in a unique App Name and Terms of Service details as seen below and click the SAVE button.
Clicking on the Keys tab will take you to the page where you can get the Client ID and Client Secret code. The top of the page shows you the Development keys. Scroll down to get to the Production keys section. Click the Show button to make the keys visible and then click the Copy button to copy them. 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. This page will also be where you go to paste the Redirect URI from the Data Hub. Keep this page open for future reference.
Log into the iDashboards Data Hub and select the API Accounts option from the System Drop Down menu.
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 the Add Account button.
Now select the QBO QuickBooks Online option and click Next.
Keep this page open for later use. Copy the Redirect URI from the iDashboards window and go back to Intuit Developer. Click Add URI and then paste this value into the Redirect URI 2 and click Save.
Now copy the Client ID and Secret from QuickBooks (Click the Show button to make the keys visible, then click the Copy button to copy them) and paste them into the iDashboards Data Hub Add Account window.
Click Next. Then click Login after choosing the Production radio button.
Now enter in your username and password for the QuickBooks developer account in the new window.
Next, a Lets make sure it’s you window will appear. Choose an option to get your confirmation code and 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 QBO, you will get a selection of which company to connect you.
Now you will see the Authorize window. 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 and, if necessary refresh the connection, you will see the newly added QuickBooks Online connection in that window.
You have 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. First create a new Data Set.
Select Cloud API and choose the QuickBooks Online option.
Select the query type you want to use.
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, modify your column selections, or click Save to move onto the next step. 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 to save the Data Set to an available Data Sets folder.
Your new Data Set will now be available in the Data Hub and you can access it under Edit Data Sets.
Click Open. Next, create a new ETL job to add the data from the data Set into a database table.
Click Save. Verify your information in the next window and Click Save again.
Now you can begin building out your ETL job. Create a simple job that moves the data from the data set into a database table of your choosing with just an Extract task and Load task. Once that is done, click Save or Save As.
Once the job has been run to create the table and it has added the data to the data store target table you selected, you now have an available data source that contains your QBO data and you are ready to build a chart from this data in the User application.
Log into the User application and right click on any dashboard and select New Chart, then find the data hub data source you added the table to and select it from the list. Now pick the correct data set table you created in the ETL tool and click Next.
Now, select the columns you want to use in your chart and click Next.
Finally, choose a chart type and click Finish.
You will now see your new chart filled with data from your QuickBooks Online API connection in your new chart.
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.