QuickBooks Online

QuickBooks Online is a cloud-based accounting solution for managing and controlling finance-related processes.

Data integration: Skyvia supports importing data to and from QuickBooks Online, exporting QuickBooks Online data to CSV files, replicating QuickBooks Online data to relational databases, and synchronizing QuickBooks Online data with other cloud apps and relational databases.

Backup: Skyvia Backup supports QuickBooks Online backup.

Query: Skyvia Query supports QuickBooks Online.

For QuickBooks Desktop connector, see QuickBooks Desktop.

Establishing Connection

When creating a QuickBooks connection, log in with QuickBooks. Skyvia stores only the OAuth authentication token. Your QuickBooks account login and password are not stored on the Skyvia side.

Creating Connection

To connect to QuickBooks, perform the following steps:

  1. Select the QuickBooks environment type, which can be Production or Sandbox, and click Connect to QuickBooks.

    connection

  2. Enter your QuickBooks credentials and click the Sign In button.

    sign-in

  3. Select a company to query data from (click the link with the corresponding company name) and proceed further.
  4. Wait until the token is generated, and then click Create Connection.

Additional Connection Parameters

Optionally, change the Metadata Cache parameter value.

Connector Specifics

Custom Fields

Skyvia does not support custom QuickBooks fields having double quotation marks in their names.

Complex Structured Data

Some of the QuickBooks tables store complex structured data. These are the following tables: Invoice, Estimate, Bill, BillPayment, CreditMemo, JournalEntry, Payment, Purchase, PurchaseOrder, RefundReceipt, SalesReceipt, VendorCredit.

For example, an invoice or bill can have several lines. Skyvia represents this information in such tables as a JSON field Line. Here is an example of the Lines field value from the Invoice table:

[
  {
    "Id": "1",
    "LineNum": 1.0,
    "Description": "Weekly Gardening Service",
    "Amount": 140.0,
    "DetailType": "SalesItemLineDetail",
    "SalesItemLineDetail_ItemRefId": "6",
    "SalesItemLineDetail_ItemRefName": "Gardening",
    "SalesItemLineDetail_UnitPrice": 35.0,
    "SalesItemLineDetail_Qty": 4.0,
    "SalesItemLineDetail_TaxCodeRefId": "NON"
  },
  {
    "Id": "2",
    "LineNum": 2.0,
    "Description": "Pest Control Services",
    "Amount": 35.0,
    "DetailType": "SalesItemLineDetail",
    "SalesItemLineDetail_ItemRefId": "10",
    "SalesItemLineDetail_ItemRefName": "Pest Control",
    "SalesItemLineDetail_UnitPrice": 35.0,
    "SalesItemLineDetail_Qty": 1.0,
    "SalesItemLineDetail_TaxCodeRefId": "NON"
  },
  {
    "Amount": 175.0,
    "DetailType": "SubTotalLineDetail"
  }
] 

For user convenience, lines of these objects are also available as separate records via read-only tables with names containing LineItem suffixes, like InvoiceLineItem, BillLineItem, etc. They allow you to view these lines in a tabular form with Query, export them to CSV with Export, import them from QuickBooks to a cloud application or database, where these lines should be stored in a separate table, etc.

Since these tables (that have the LineItem suffix in their name) are read-only, they are not available in Import packages as a target or in Synchronization packages. To modify lines of a bill, journal entry, etc., you need to provide values in JSON format for the Lines field of the corresponding main table - Bill, JournalEntry, etc. For example, to import bills from a CSV file to QuickBooks, you need to specify bill lines in the JSON format in one of the CSV file columns and import this file to the Bill table. You need to map the Lines field of the Bill table to this column of the CSV file.

However, for the Invoices table, you can use our Nested Objects mapping feature in Import packages. For this, you need to select the Use new runtime checkbox in your package, then select the Nested Objects checkbox in the package. Then, in the mapping settings, you can map the fields of invoice line items.

The tables with the LineItem suffix in their name are available in backup packages, but you cannot restore data to these tables because they are read-only. Since they store the same information as the Lines field of the corresponding main tables, you don’t actually need to create backup for them. All the information in *LineItem tables is present in the corresponding main tables, and you can backup and restore data in the main table only.

Incremental Replication and Synchronization

Synchronization and Replication with Incremental Updates enabled are not supported for objects without MetaData_CreateTime or MetaData_LastUpdatedTime fields. Both fields must be present for this functionality.

Supported Actions

Skyvia supports all the common actions for QuickBooks.

Data Integration Tutorials

There are several common use cases of QuickBooks integration.

Troubleshooting

New Custom field is Not Available in Skyvia

Open your QuickBooks connection in Skyvia and try to clear the metadata cache. After that, make sure the custom field is available in Skyvia.