How to connect Quickbooks to Google Sheets

QuickBooks is an online accounting and payment services platform, marketed mainly to small and medium-sized businesses. They offer on-premises and in-cloud software packages, but also a capable API that allows developers to create new applications on the platform.

The QuickBooks API provides access to various business accounting entities managed on the platform, such as accounts, suppliers, customers, employees, bills, invoices, taxes and transactions.

This tutorial will instruct you on how you can import QuickBooks API data into Google Sheets using the Apipheny app.

  • Step 1: Create a Sandbox QuickBooks App
  • Step 2: Get an OAuth 2.0 Access Token
  • Steps 3-5: Run your API request in the Apipheny Add-on for Google Sheets

Step 1: Create a Sandbox QuickBooks App

To use the QuickBooks API in Google Sheets, you have to create a dedicated app in your account. To get started, you will create a sandbox app that allows you to develop using their API, before you publish a production version.

This tutorial will show you how you can use such a sandbox app, to import the QuickBooks API information you want in Google Sheets.

First, log in to your QuickBooks Developer Account, using your account credentials. If you don’t yet have an account yet, click here to sign up for a Quickbooks developer account.

Create a Quickbooks Developer Account
Create a Quickbooks Developer Account

Once logged in, click on My Apps menu item and then click the +Create an app button.

Create an app in Quickbooks
Create an app in Quickbooks

On the next page, choose QuickBooks Online and Payments:

Quickbooks Developer Account
Quickbooks Developer Account

Next, create a name for your app and select a scope in the combo box. If you are not in the US, you need to choose Accounting. Click the Create app button to finish the step:

Choose a name and scope
Choose a name and scope

After you’ve created your app, choose your language and scenario. You have the possibility to develop it in Java, PHP or .NET. For this example we are choosing Java and Getting Started.

Choose a language and scenario
Choose a language and scenario

Next, navigate to Keys & OAuth section from the Development menu. There, you will find the client ID and client secret necessary to connect the QuickBooks API to Google Sheets:

Client ID and Client Secret
Client ID and Client Secret

Step 2: Get an OAuth 2.0 Access Token

In order to access most QuickBooks API endpoints, you will need an access token. To obtain an access token, follow the next steps:

First, go to https://developer.intuit.com/app/developer/playground and get an authorization code for your app:

Get authorization code
Get authorization code

In the OAuth settings section, choose the required scopes and then click the Get authorization code button:

Click Get authorization code
Click Get authorization code

You will be asked to connect your app to a Sandbox. Click the Connect button to continue:

Click connect
Click connect

Next, you have to get an access token from the authorization code you previously obtained. Click the Get tokens button to retrieve the access token. The access token will be shown in the Response section, on the right side of the screen:

Click Get tokens
Click Get tokens
Access token
Access token

Step 3: Install and Open the Apipheny add-on in Google Sheets

Apipheny is an API integrator add-on for Google Sheets.

Install Apipheny by clicking the Install button on this page: https://apipheny.io/install

After you’ve installed Apipheny, open a new or existing Google Sheet and then in the menu at the top, click Add-Ons or Extensions > Apipheny > Import API.

After you click Import API, the Apipheny add-on will open up as a side panel in your Google Sheet:

Install and open the Apipheny Add-on
Install and open the Apipheny Add-on

Step 4: Enter your QuickBooks API request into Apipheny

First, at the top of the Apipheny add-on, select the HTTP method (GET or POST) required by your API endpoint. For this example we are using GET.

Next, enter your quickbooks API URL into the API URL Path field of Apipheny, followed by any parameters required for your query.

Then, in the Headers section of Apipheny, you should add two rows with the following keys and values:

Header 1
Key:
Content-Type

Value:
application/json
Header 2
Key:
Authorization

Value:
Bearer your_access_token

The Content-Type and Authorization headers are both required, so make sure they are entered in Apipheny. your_access_token is the value from the accessToken field in the JSON response you obtained previously, in Step 2. There should be a space between “Bearer” and your access token.

As an example, we’ll use the company info endpoint. This endpoint requires an additional parameter, RealmId, a unique identifier for your company. You can find the company info value in the the Developer Playground page where you obtained your access token.

A correctly formatted URL for this example endpoint, with the required parameters, would look like this:

https://sandbox-quickbooks.api.intuit.com/v3/company/<RealmId>/companyinfo/<RealmId>

And here’s what our API request looks like entered into Apipheny:

Step 5: Run the API Endpoint Request

Click the Run button at the bottom of the Apipheny add-on and then wait for the Quickbooks API data to be retrieved and added to your spreadsheet:

Quickbooks API data in Google Sheets
Quickbooks API data in Google Sheets
Token Expiration:
Please note that your QuickBooks' Oauth2.0 access token will expire after 60 minutes and you will need to get a new token if it's been longer than 60 minutes. Please see this page for more information:
https://help.developer.intuit.com/s/article/Handling-OAuth-token-expiration

API Tutorials



API Knowledge

What is an API?
What is an API URL?
What are parameters?
What is an endpoint?
What is an API key/token?
What is basic authentication?
What are headers?
What is a GET request?
What is a POST request?