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
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. For more information about available importers, please visit the Coupler.io home page. 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:
- 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.
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:
- 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:
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:
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.
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! 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:
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
- 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
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!Back to Blog