Quickbooks API Google Sheets

How to connect Quickbooks API 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 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

To bring data from the QuickBooks API to Google Sheets, you’ll have to install and open the Apipheny Google Sheets add-on:

Install Apipheny by clicking the following link and then clicking the Install button: https://gsuite.google.com/marketplace/app/apipheny/966163326746

After you’ve installed Apipheny, open a new or existing Google Sheet and then in the menu at the top, click Add-Ons > 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: Add your QuickBooks API endpoint URL into the Apipheny Add-on

Copy the URL for the QuickBooks API endpoint into the Apipheny add-on.

Enter the URL into the API URL Path (JSON / CSV) field, followed by any GET parameters required for your query.

Next, select the HTTP method (GET or POST) required by your API endpoint. For this example we are using GET.

Into the Headers section, you should add two rows with the following keys and values:

Key: Content-Type 
Value: application/json
Key: Authorization
Value: Bearer <your access token>

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. Do not include the carrots “<>”.

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>

The Content-Type and Authorization headers are also required, so you should add them in the dedicated table, below the API URL Path (JSON / CSV) field:

Step 5: Run the API Endpoint Request

Click the Run button at the bottom of the Apipheny add-on and then wait for the Quickbook 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

Related Posts

Leave a Comment