How to Export QuickBooks to Google Sheets

QuickBooks is the all-in-one solution for accounting and finance management. Nevertheless, experienced accountants prefer to combine this software with a tried-and-true spreadsheet. There are different reasons for this, and several options for how to do it. We’ll introduce a simple yet advanced tool to pull data from QuickBooks to Google Sheets. It will let you automate data import without any coding (just a few clicks). Interested? Let’s go.

Disclaimer: QuickBooks Online Advanced is NOT REQUIRED at all.

How to set up QuickBooks Google Sheets integration

Install Coupler.io

Coupler.io is a Google Sheets add-on, which connects and fetches data from different sources, such as QuickBooks, Xero, Pipedrive, CSV and many more. Find Coupler.io on the G Suite Marketplace or use this direct link. You can also install it from the Add-ons menu in your spreadsheet.

Set up a QuickBooks importer

Coupler.io connects Google Sheets to a specific data source via importers. Check out the available importers.

To pull data from QuickBooks, you’ll need to set up a QuickBooks importer.

Coupler.io is progressing and our team is working on new importers. If you need to connect a specific data source to Google Sheets, tell us about this by filling out this form. Your voice will be considered to decide which importer will be created next.

Open a spreadsheet, go to the Add-ons menu, select Coupler.io, and click on Open dashboard. After that, click on the +Add Importer button and choose QuickBooks.

Now, you need to fill in the following fields:

Required fields

Title – Add the name of your importer.
Sheet Name – Add the name of the sheet that will be receiving data.
Data Entity – Select the data category to import. You can choose from a number of options: Invoice, Purchase, Customer, and many more. Account comes as the default entity.
Automatic data refresh – Choose the frequency of automatic data import. If you don’t need this feature, disable it by clicking on the toggle or just choose Never.

Additional fields

For additional manipulations of data, check out the Additional section:

Where – Specify a guided query against the chosen entity to retrieve a filtered set of elements. For example, to retrieve all Accounts Receivable from Account entity, use the following query string:

AccountType='Accounts Receivable'

Order – Use data query to retrieve elements in ascending or descending order. For example, to retrieve data in descending order by email address, use the following query string:

EmailAddress DESC

For more about data queries, read the QuickBooks official docs.

Split By – Use this parameter to split data with multiple lines into separate rows. For example, an invoice may have several line items, which will be returned in a single row at a regular data import. Here is how it may look:

856.52856.52

To split items into separate rows, type Line in the field. After the import, you’ll get separate rows with 856.52 in each one. For more on this, check out the Coupler.io knowledge base.

Settings

The Settings section contains three fields:

Mode – Pick the data importing mode for consequent imports:
- replace – to fully replace data at each consequent import
- append – to put newly imported data below the previously imported data

Replace mode is the default value.

Cell address – Specify the first cell where the data range will be imported. A1 is the default value.
Last updated – Select “Yes” to add a column, which will display the date of the last data refresh. “No” is the default value.

Once you’ve filled in all the required fields, click Connect to connect your QuickBooks account to Coupler.io.

After that, click Add Importer and run it afterwards to import data:

The setup flow is similar to Xero importer, which lets you pull data from another accounting software. For more on this, read our blog post, How to Integrate Xero and Google Sheets.

Use cases: Why do I connect QuickBooks to Google Sheets?

That’s a very good question! You can export your QuickBooks data to make a backup copy as we explained in QuickBooks Data Backup.

Another reason is reporting. QuickBooks provides a very informative dashboard, as well as “…lots of pre-created reports you can customize to show you the information you need.” However, customization options in QuickBooks are not as flexible as in Google Sheets. For example, here is how you can customize a Profit and Loss report:

However, it’s still more convenient to use a dashboard that will contain this P&L report along with other financial information. Check out the P&L dashboard examples and free templates by Coupler.io.

With Google Sheets, you can do way more for not only reporting but also data visualization, tracking and so on. Check out some use cases that may be helpful to make a decision.

Use case #1: Create a customized report for Stamps.com

Let’s say you need to download a report from QuickBooks and then upload it to a third-party software. In our case, this is Stamps.com. The report should contain the following data:

Order ID
Date
Customer name
Shipping address
Product price
Product name

The flow in QuickBooks will look as follows:

Run the Customer Contact List report
Export the report to Excel
Run the Product/Service List report
Export the report to Excel (if you have QuickBooks Online Plus or Advanced, you can go with the Physical Inventory Worksheet report)
Merge both reports manually to create a single one. You’ll probably need to check out How to Merge Cells and Combine Columns in Google Sheets.
Upload the created report to Stamps.com.

Here is how it will look with Coupler.io:

Set up a QuickBooks importer with the following parameters:
- Data Entity: SalesReceipt
- Split By: Line
Run the importer to pull data to Google Sheets

Create a report for Stamps.com by querying the required fields. We encourage you to create the report in a separate sheet in order to keep your data organized. Check out the formula we used for that:

=query('Report from QuickBooks'!A:AN,"select C,H,W,M,Q,X,Y,Z,AA where Q is not null")

Read more about what you can do using the QUERY function in Google Sheets.

The best part is that you can schedule automatic data imports and link raw data with your final report, so everything will update automatically! This blog post might be helpful for that: How to Link Data Between Multiple Spreadsheets.

Use case #2: Share your P&L report with stakeholders for budget collaboration

Have you ever tried to collaborate with your teammates or partners in QuickBooks? It may be uncomfortable and even frustrating! However, if you export the raw data to Google Sheets, you’ll be able to share it with stakeholders and synchronize your work.

Let’s say you need to elaborate a budget based on the Profit and Loss report from QuickBooks. The report itself is great and allows you to zoom in to check out details.

Unfortunately, you can’t export it unless you have QuickBooks Online Advanced. But here is a workaround using Coupler.io:

The QuickBooks importer lets you pull raw data from Quickbooks. This means that you won’t be able to export Quickbooks premade reports including Profit and Loss. However, you can build a customized P&L report based on the exported data.

Import QuickBooks Expenses

Set up a QuickBooks Importer with the following parameters:

Data entity: Purchase
Split By: Line

Run it to import purchase data (expenses) from Quickbooks.

Import QuickBooks Income

QuickBooks income may include different data entities. In our example, it looks like this:

QuickBooks income = (Invoices + Sales Receipts) – Credit Memos

Check out your P&L report in QuickBooks if you need to figure out which data entities are included in your income.

To import this data, we need to set up three separate QuickBooks Importers with the following Data entity:

Invoice
SalesReceipt
CreditMemo

Run them to pull data into the spreadsheet. You’ll get many imported columns, but here are the most crucial ones you’ll find for each data entity:

TxnDate – transaction date
CustomerRef.name – customer’s name
TotalAmt – total amount

After that, you can make your customized P&L report and feed data to it. You can use the template we’ve made in one of our blog posts, Simple Budget Template in Google Sheets, and adapt it for QuickBooks imported data.

Once the report is ready, you can share it with stakeholders to collaborate on a budget or forecast.

To wrap up

We do not suggest you give up on QuickBooks! It’s a great tool for accounting and financing. However, you can get synergy from using QuickBooks together with Google Sheets, and Coupler.io will let you do this. Export your data at ease and do the trickiest manipulations with data in spreadsheets. Good luck!

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries??