Connection Qlik Sense SAAS to Quickbooks online

[badrharfoush198]

hi all,

anyone have a way to connect Qlik Sense Saas to Quickbooks online differently than using a gateway to servers?

[Vicky_Z]

So far there is no dedicated connector for Quickbooks. ODBC is always the generic solution if the data source has ODBC interface. 

[GregWoodard]

We have a connection working between Qlik Sense SaaS and Quickbooks Online (QBO).  The complexity in getting Qlik connecting to QBO is related to creating the token since the QBO API requires a REDIRECT which the Qlik REST API connector does not support.   The following enabled us to create a connection to our Sandbox QBO environment and use Qlik to keep the TOKEN alive (once created).  Our next step will be how to create the TOKEN the first time in some automated manner.  Open to ideas on that.  We are going to go down the Application Automation path and try and use the generic Web URL connector (not the OAuth2 connector since the connection is hidden).

Here are the steps taken to get it to work as a first step:

•  Create an Intuit Developer Account
•  Setup a Sandbox App here:  https://developer.intuit.com/app/developer/sandbox
•  Use Playground to get a Valid Token:  https://developer.intuit.com/app/developer/playground
•  Use the Refresh Token using "Step 4" in the playground.
  • When you hit "Refresh Access Token", you'll be able to see the request and the response.
  • We used the Request to build a REST API connection in Qlik to Refresh and keep the Token alive.  
  • 
  • In Qlik, we created 2 data connections.
    • Data Connection #1 refreshes the token that was created in the Playground above:
      • Data Connection #1 (TOP)
      • Data Connection #1 (BOTTOM)
      • In Qlik, execute the REST API call with the above connection and store the Access Token and Refresh Token.  Refresh Token needed in next API call below.
      • 
    • Data Connection #2 - API Call to retrieve data
      
      • Note:  The company # below is from the Sandbox or Production QBO company
      • Note - the Bearer below is the "Access Token" from above REST API Call
      • 
      • 
      • Customize the CONNECTION with the refreshed TOKEN variable from above
        • 

Please reply if you get it working for others and improve on my directions if I missed anything.  Also, if you have an approach for creating the TOKEN through Qlik SaaS (Application Automation or otherwise).

Thanks to Qlik (Dan Pilla and Jim Smith) for helping us troubleshoot.

Greg

[badrharfoushTruist]

hi Greg!

thank you for the info. I will try it and get back to you. I assume the above solution work on both on Prem and on SAAS.

[GregWoodard]

I would think so.  With on-premise you might also be able to use Powershell or another "External Task" to initially generate the Token.  In SaaS, we are going to look into Application Automation further.

[badrharfoushTruist]

great! trying it now.

what did you use for authentication? I tried basic and enter the user and pass I have to the app and intuit site, I am getting http error 401 error and states requested resource requires authentication.

[GregWoodard]

The prerequisites for connection to Quickbooks Online is that you have to setup a APP in the Quickbooks Developer area:  https://developer.intuit.com/app/developer/dashboard 

When you first create an APP, it provides a Sandbox area and a Production Area.  You can immediately start testing with the Sandbox area here:  https://developer.intuit.com/app/developer/playground. 

For the Production APP you need to fill out a questionnaire and it can take up to a day to get approved.

The standard practice we've done is as follows (for now):

1) Setup APP in the Developer Site for Intuit

2) Authorize (Step 1)  and Generate a Token (Step 2) in the Playground (this completes the authentication and intial token generation that cannot be done from Qlik due to limitations of Qlik's REST API connector).

3)  Refresh the Token (Step 4) in the Playground

4)  Use the variables/headers/url from the call to refresh the token to create a REST API call in Qlik to keep the Token alive.  See my pictures abovet.  Note body and header settings specifically.  

5)  Everytime a token is refreshed it extends it back to 100 days.  The call to refresh the token requires a "refresh_token" and returns a "refresh_token" and "access_token".  Store both since the "refresh_token" changes daily.  We have a separate Qlik app that runs hourly, refreshes the Token and stores the returned values (refresh_token & access_token) in a QVD.  We then read the latest refresh_token from the QVD to do the token refreshes and use the access_token from the QVD to make calls to get data.

6)  Using this refresh token method, we have been able to keep a token alive for the past few months.  If it ever doesn't refresh, we have to go back to the playground and generate a new token to "keep alive".

It is not a process I recommend to the average user.  Developer experience is helpful if not required.  It is fairly technical and can be frustrating.  Feedback always welcome.