Establishing a Connection

Configure a Connection Profile

From the CData ribbon, click Get Data and select From QuickBooks Online connection/s to launch the CData Query window. To setup a new connection, you will have to click the New QuickBooks Online Connection button. Here you can set the connection settings, test the connection, and save the connection profile.

Authenticating to QuickBooks Online

QuickBooks Online supports connecting and authenticating via a desktop application, a web browser, or a headless machine. The CData Excel Add-In for QuickBooks Online includes embedded OAuth credentials that simplify connection from the desktop or a headless machine and provide limited functionality. However, to experience fuller functionality via desktop or headless machine connections OR to connect via the web, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

The following subsections describe how to authenticate to QuickBooks Online from all OAuth workflows. For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in QuickBooks Online, see Connection.

Desktop Applications

The properties required for connection depend on whether you want to connect via the embedded OAuth credentials (limited use) or connect via a custom OAuth Application (full functionality):

Before you connect, set the required properties:

InitiateOAuth (all users): GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
OAuthClientId (custom OAuth only): The client Id assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
OAuthClientSecret (custom OAuth only): The client secret assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
CallbackURL (custom OAuth only): The redirect URI defined when you registered your custom OAuth application (see Creating a Custom OAuth Application).

When you connect, the add-in opens QuickBooks Online's OAuth endpoint in your default browser. Log in and grant permissions to the application.

When the access token expires, the add-in refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the add-in automatically refresh the OAuth access token, do the following:

The first time you connect to data, set these connection parameters:
- InitiateOAuth: REFRESH.
- OAuthClientId: The client Id assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
- OAuthClientSecret: The client secret assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
- OAuthAccessToken: The access token returned by GetOAuthAccessToken.
- OAuthSettingsLocation: The path where you want the add-in to save the OAuth values, which persist across connections.
On subsequent data connections, set:
- InitiateOAuth
- OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAUth access token is the OAuth refresh token.

To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
Set these connection properties:
- OAuthClientId: The Client Id assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
- OAuthClientSecret: The Client Secret assigned when you registered your custom OAuth application (see Creating a Custom OAuth Application).
Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Connection Properties

The Connection properties describe the various options that can be used to establish a connection.

Managing Connections

After successfully authenticating to QuickBooks Online you will be able to customize the data you are importing. To learn more about this, see Managing Connections.