Quick Books Online

Introduction

The Connector for QuickBooks Online provides easy access to QuickBooks Online data, including Customers, Transactions, Invoices, Sales Receipts, Reports, and more! The QuickBooks Online Connector makes this QuickBooks Online data look like SQL tables in your applications.

Connection String

QuickBooks Online uses the OAuth authentication standard. Additionally, if you want to connect to sandbox data, set UseSandbox to true.

Connection string Parameters

To connect to Quickbooks Online Data, set the following properties:

Parameter Description
OAuthAccessToken The access token for connecting using OAuth.
OAuthAccessTokenSecret The OAuth access token secret for connecting using OAuth.
CompanyId The unique identifier of a given company in QuickBooks Online.

To automatically refresh the token, additionally set OAuthRefreshToken to the token returned and set InitiateOAuth to “REFRESH”.

Connecting to QuickBooks Online

  1. Create an account in https://developer.inituit.com. Login using the account created.

  2. Create a Sandbox. Your Intuit Developer account comes automatically provisioned with a private US sandbox environment for testing your app features and integration. You can have up to four additional sandboxes distributed as you like across the Australia (AU), Canada (CA), France (FR), India (IN), United Kingdom (UK), and US development regions.

  3. Create an app using the create new App option.

  4. After creating the app, generate ClientId, ClientSecret from the Keys Tab. The ClientId and ClientSecret helps in generating the OAuthAccessToken and OAuthAccessTokenSecret. The Redirect URL come from web Server.

  5. To Generate CompanyId You can get the CompanyId by pressing Ctrl+Alt+? from any screen in QuickBooks Online. Do not set a value for CompanyId when using InitiateOAuth .

Data Migrations

To Check for the data migrations:

  1. Add a new connection in data governor as shown.

    • Go to tasks and click on Connections.

    • Add a new connection Using Add New Connection Option.

    • Choose Quickbooks Online Connector from the available Connector Options.

  2. Using the connection string parameters created, Verify the connection using the VERIFY CONNECTION Option in Data Governor. Once the Connection is verified, Insert the Connection Using Insert Option.

    After Clicking Verify Connection the OAuth Tokens are generated in the Connection String., You will be taken to the following Window. Choose the account, and give access by clicking on authorize. You will be displayed with authorization successful window.

    Once the Connection is verified, Insert the Connection Using Insert Option.

  3. Create a Job Using ADD A JOB option in data governor, add a data migration task to the job.

  4. Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

  5. Choose a Data Migration Task.

  6. Choose the source and the destination for the migration of data. Name the Task.

  1. Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

  2. Execute the Job. Check for results and the details of the data migrated in Execution History.

Data Model

The Connector for QuickBooks Online models entities in the QuickBooks Online API as tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files. API limitations and requirements are documented in the following sections; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The provider exposes tables for data sources that support both retrieving and updating data. The tables are normalized and contain an Id column, which is the primary key. This behaviour is consistent across all tables in the product.

Name Description
Accounts Create, update, delete, and query QuickBooks Accounts.
AttachableRefs Attachables
Attachables Attachables
BillLineItems Create, update, delete, and query QuickBooks Bill Line Items.
BillPaymentLineItems Create, update, delete, and query QuickBooks Bill Payment Line Items.
BillPayments Create, update, delete, and query QuickBooks Bill Payments.
Bills Create, update, delete, and query QuickBooks Bills.
Class Create, update, delete, and query QuickBooks Classes.
CompanyCurrency Create, update, delete, and query QuickBooks CompanyCurrency. Applicable only for those companies that enable multicurrency, a companycurrency object defines a currency that is active in the QuickBooks Online company. One or more companycurrency objects are active based on the company’s multicurrency business requirements and correspond to the list displayed by the Currency Center in the QuickBooks Online UI
CreditMemoLineItems Create, update, delete, and query QuickBooks Credit Memo Line Items.
CreditMemos Create, update, delete, and query QuickBooks Credit Memos.
Customers Create, update, delete, and query QuickBooks Customers.
Departments Create, update, delete, and query QuickBooks Departments.
DepositLineItems Create, update, delete, and query QuickBooks Deposit Line Items.
Deposits Create, update, delete, and query QuickBooks Deposits.
Employees Create, update, delete, and query QuickBooks Employees.
EstimateLineItems Create, update, delete, and query QuickBooks Estimate Line Items.
EstimateLinkedTransactions Create, update, delete, and query QuickBooks Estimate Line Items.
Estimates Create, update, delete, and query QuickBooks Estimates.
InvoiceLineItems Create, update, delete, and query QuickBooks Invoice Line Items.
InvoiceLinkedTransactions Create, update, delete, and query QuickBooks Invoice Line Items.
Invoices Create, update, delete, and query QuickBooks Invoices.
Items Create, update, delete, and query QuickBooks Items.
JournalEntries Create, update, delete, and query QuickBooks Journal Entries.
JournalEntryLineItems Create, update, delete, and query QuickBooks Journal Entry Line Items.
PaymentLineItems Create, update, delete, and query QuickBooks Payment Line Items.
PaymentMethods Create, update, delete, and query QuickBooks Payment Methods.
Payments Create, update, delete, and query QuickBooks Payments.
PurchaseLineItems Create, update, delete, and query QuickBooks Purchase Line Items.
PurchaseOrderLineItems Create, update, delete, and query QuickBooks Purchase Order Line Items.
PurchaseOrders Create, update, delete, and query QuickBooks Purchase Orders.
Purchases Create, update, delete, and query QuickBooks Purchases.
RefundReceiptLineItems Create, update, delete, and query QuickBooks RefundReceiptLineItems.
RefundReceipts Create, update, delete, and query QuickBooks RefundReceipts.
SalesReceiptLineItems Create, update, delete, and query QuickBooks Sales Receipt Line Items.
SalesReceipts Create, update, delete, and query QuickBooks Sales Receipts.
TaxAgency Create and Query QuickBooks Tax Agency.
Terms Create, update, delete, and query QuickBooks Terms.
TimeActivities Create, update, delete, and query QuickBooks Time Activities.
Transfers Create, update, delete, and query QuickBooks Transfers
VendorCreditLineItems Create, update, delete, and query QuickBooks Vendor Credit Line Items.
VendorCredits Create, update, delete, and query QuickBooks Vendor Credits.
Vendors Create, update, delete, and query QuickBooks Vendors

Views

Views are similar to tables in the way that data is represented; however, views do not support updates. Data sources that are represented as views are typically read-only data sources.

Name Description
BudgetDetails Query QuickBooks BudgetDetails.
Budgets Query QuickBooks Budgets.
CompanyInfo Retrieve information about the QuickBooks company.
Entitlements Retrieves QuickBooks Entitlements.
Preferences Query QuickBooks Preferences.

The Preferences table contains settings for company-wide preferences, which affect all users. TaxCodes | Query QuickBooks Sales Tax Codes. TaxRates | Query QuickBooks Tax Rates