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.
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.
Once logged in, click on My Apps menu item and then click the +Create an app button.
On the next page, choose QuickBooks Online and Payments:
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:
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.
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:
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:
In the OAuth settings section, choose the required scopes and then click the Get authorization code button:
You will be asked to connect your app to a Sandbox. Click the Connect button to continue:
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:
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:
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:
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:
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:
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:
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