How to access your QuickBooks Online data via API from the command line

As is so often with this blog, I’m documenting something that took me a long time to figure out.

The problem: the company I work for has data in QuickBooks Online (QBO) that we’d like to distribute elsewhere in our organization.

The answer: QBO has an API. Yahoo! They have several different SDKs (for .NET, Java and PHP) to make access even easier. (The APIs and surrounding docs are called the Intuit Partner Platform, or IPP.)

However…

The issue is that requests to the QBO API must be authenticated with OAuth (I believe it is OAuth 1.0a). And the entire Intuit Partner Platform documentation is focused on needs of developers who are building SaaS applications that consume or augment QBO data and are accessed via the QBO webapp. Which means there’s a heavy focus on web applications and OAuth flows via web applications.

But all I wanted was a command line client that could use the API’s query interface.

I was stymied by OAuth. In particular, I couldn’t find a way to get the accessToken and accessTokenSecret. I tried a number of different tacks (I beat my head against this for the better part of day).

I read through the Intuit OAuth docs a number of times
I looked atthe Intuit Anywhere Developer Playground
I reviewed the sample code
I searched on the Intuit StackOverflow community
I looked at recommended Java OAuth providers
I created a numerous apps and a couple of sample companies (signing up for QBO trials 2-3 times)
I reviewed other OAuth documentation
I found a command line OAuth client
I spent time in the API Explorer

But I just couldn’t find a way to generate the needed tokens, with either the PHP or Java SDK clients (most of the links in this post are for the Java client, because that’s more mature–the PHP client doesn’t support JSON output or have reference documentation).

Desperate, I turned to the IPP forums, which were full of advanced questions. I did stumble on this gem: “Simple way to integrate with our Quickbooks Account”, which took me to the OAuth Playground for IPP Developers. And, voila, if you follow the steps in the playground (I used IE because FireFox failed), you will end up with a valid accessToken and accessTokenSecret.

So, with that sad story told, here’s exactly how you can take access your own QBO data via the command line (I only cover a trial account here, but believe the process is much the same with a paid account):

Start your IE browser (I’m using IE 10 on windows 8)
Go to https://developer.intuit.com/us
Sign up (click the ‘join’ link), click ‘remember me’
Go to your email and find the verify link that was sent to you. Paste it into your IE browser.
Sign in again
Click on ‘My Apps’
Click on ‘Create New App’, then ‘QuickBooks API’
Fill out the name of the app, and the other required items. You can change these all later (I think). I know you can change the URLs later.
Select the level of data access you need. Since this is a test app, you can select ‘All Accounting’
Click ‘Save’
Open up another tab in IE and go to the QuickBooks Online site (We are just adding some dummy data here, so if you have an account, you can skip this.)
Click on ‘Free Trial’
Click on ‘QuickBooks Online Plus’
Click on ‘Already have an Intuit user ID’
Fill out the username and password you used on when you signed up for your developer account.
Ignore the upsell, if any
Click the customers tab
Click on the ‘new customer’ button
Enter a first name and last name then press save
Open a new tab and go to the API Console
Choose the company that you want to access, and note the number next to that name. That is the company ID or the Realm ID.
Open a new tab and go to the OAuth playground
Go back to the developer.intuit.com tab
Grab your app token (looks like b3197323bda36333b4b5fb17774440fe34d6)
Go to the OAuth playground tab and put your app token in the proper field (called ‘App Token’). You’ll also want to have that later, so note it now.
Click ‘Get Key and Secret using App Token’
Note the consumer key and consumer secret, you’ll need them later.
Click ‘Get Request Token Using Key and Secret’
Click ‘Authorize Request Token’
You should see a message like ‘testapp3 would like to access your Intuit company data’
Click ‘Authorize’
You should see a message like ‘You are securely connected to testapp3’
Click ‘Return to TestApp3’
Scroll down to the bottom, and you should see entries in the ‘Access Token’ and ‘Access Token Secret’ fields. Copy those, as you’ll need them later as well.
Go to the SDKs page of developer.intuit.com
Pick your language of choice, and follow the installation instructions.
Follow the instructions in the ‘Data Service APIs’ section about setting up your environment. For Java, you’ll need to pull a few jar files into your classpath. Here’s my list of jar files in my Eclipse build path: ipp-java-qbapihelper-1.2.0-jar-with-dependencies.jar, ipp-v3-java-devkit-2.0.3-jar-with-dependencies.jar
Write and run a class (like the one below) that runs a query, plugging in the six variables the values you captured above.

import static com.intuit.ipp.query.GenerateQuery.$;
import static com.intuit.ipp.query.GenerateQuery.select;

import com.intuit.ipp.core.Context;
import com.intuit.ipp.core.ServiceType;
import com.intuit.ipp.data.Customer;
import com.intuit.ipp.exception.FMSException;
import com.intuit.ipp.query.GenerateQuery;
import com.intuit.ipp.security.OAuthAuthorizer;
import com.intuit.ipp.services.DataService;
import com.intuit.ipp.services.QueryResult;

public class TestQBO {

	public static void main(String[] args) throws FMSException {
		String consumerKey = "...";
		String consumerSecret = "...";
		String accessToken = "...";
		String accessTokenSecret = "...";
		String appToken = "...";
		String companyId = "...";
		
		OAuthAuthorizer oauth = new OAuthAuthorizer(consumerKey, consumerSecret, accessToken, accessTokenSecret);           
		
		Context context = new Context(oauth, appToken, ServiceType.QBO, companyId);
		
		DataService service = new DataService(context);
		
		Customer customer = GenerateQuery.createQueryEntity(Customer.class);
		
		String query = select($(customer.getId()), $(customer.getGivenName())).generate();
		
		QueryResult queryResult = service.executeQuery(query);
	
		System.out.println("from query: "+((Customer)queryResult.getEntities().get(0)).getGivenName());      
	}
}

This code gets the first name and id of the first customer in your database, and prints it to stdout. Obviously just a starting point.

I am also not sure how long the accessToken and accessTokenSecret are good for, but this will actually give you command line access to your QBO data.

(Of course, I could have just used zapier, but that has its own limitations (limited ability to query data in an adhoc manner being the primary one).

APIs Business Web Applications | moore | January 12, 2014

26 thoughts on “How to access your QuickBooks Online data via API from the command line”

Yo says:
Hi,

I am getting following error:

Exception in thread “main” com.intuit.ipp.exception.FMSException: javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated
at com.intuit.ipp.interceptors.ConnectionInterceptor.execute(ConnectionInterceptor.java:157)
at com.intuit.ipp.interceptors.IntuitInterceptorProvider.executeRequestInterceptors(IntuitInterceptorProvider.java:76)
at com.intuit.ipp.interceptors.IntuitInterceptorProvider.executeInterceptors(IntuitInterceptorProvider.java:62)
at com.intuit.ipp.services.DataService.executeQuery(DataService.java:323)
at com.quickbooks.QuickBooksTest.main(QuickBooksTest.java:45)
Caused by: javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated
at com.sun.net.ssl.internal.ssl.SSLSessionImpl.getPeerCertificates(SSLSessionImpl.java:352)
at org.apache.http.conn.ssl.AbstractVerifier.verify(AbstractVerifier.java:126)
at org.apache.http.conn.ssl.SSLSocketFactory.connectSocket(SSLSocketFactory.java:437)
at org.apache.http.impl.conn.DefaultClientConnectionOperator.openConnection(DefaultClientConnectionOperator.java:180)
at org.apache.http.impl.conn.ManagedClientConnectionImpl.open(ManagedClientConnectionImpl.java:294)
at org.apache.http.impl.client.DefaultRequestDirector.tryConnect(DefaultRequestDirector.java:643)
at org.apache.http.impl.client.DefaultRequestDirector.execute(DefaultRequestDirector.java:479)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:906)
at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:827)
at com.intuit.ipp.interceptors.ConnectionInterceptor.execute(ConnectionInterceptor.java:150)
… 4 more
moore says:
Hi,

I don’t recall seeing this issue in my testing. From some googling, this appears to be an issue with the SSL certificate of the server. More here: http://stackoverflow.com/questions/9578129/exception-javax-net-ssl-sslpeerunverifiedexception-peer-not-authenticated

Worth posting something on the IPP support forums and seeing what people there say.
Jeson says:
I believe this SDK opens up a port to listen to the incoming oAuth https redirection. In a firewalled environment, this SDK would not work.
moore says:
Hi Jeson,

I’m not sure whether or not the OAuthAuthorizer class opens up a port. If so, it is not documented in the javadoc.

http://developer-static.intuit.com/SDKDocs/QBV3Doc/ipp-v3-java-devkit-javadoc/com/intuit/ipp/security/OAuthAuthorizer.html

Have you verified that the SDK opens up a port, or do you just suspect?
Yo says:
Hi All,
Just wanted to update that it was due to the firewall only. Probably the request or the response is not recognized by the firewall and hence the SSL issue. On open connectivity it works perfectly well. Any ideas about the expiration of access token and secret or do they remain valid always?
moore says:
Thanks, Yo, for clearing up the issue. Were you able to work around it with a hole in the firewall or a SSL proxy?

As far as the validity of the access token, I think I remember seeing something in the docs stating theyvwere valid for 180 days. I will see if I can track down the link.
h LEE says:
Thank you so much to share your experience. The documents in Quickbooks developer site give nothing but confusion and frustration
moore says:
Happy to do so. I found the developer site docs confusing too, but I think it is just because they are aimed at a different audience (SaaS app developers who will sell on the intuit marketplace).
Rob says:
Thanks a lot for this, I wish Intuit would update their docs. It clearly states that the API supports “Windows Desktop applications” but they offer no help in getting it to work.

Here’s information regarding the access token expiry (it is 180 days, but it can be renewed): https://developer.intuit.com/docs/0025_quickbooksapi/0010_getting_started/0020_connect/0010_from_within_your_app/implement_oauth_in_your_app/token_renewal_and_expiration
Sourayan says:
Thanks a lot. Your article is very useful. Its working fine for me with “Free Trial” and “Already have an Intuit user ID” that you mentioned in steps 12 to 15. But when I try to do it with a real time client QB account. The same code is not working. A error is coming “Bad request” when I try to get some customer details or try to add one. Can you please share your thoughts.
moore says:
Hi,

Not sure exactly what your issue is, but did you have the real account authorize your application’s access?
Sourayan says:
Thanks for your reply.
I have a question, when you say “did you have the real account authorize your application’s access?”,
what do you mean by “application” in this context, is it the one that is created in the “Create New App” in “https://developer.intuit.com/us” site or is it the web application that i am running in my local system where all the OAuthAuthorizer and Context related code are written by the developer?

I have the real account authorize my app with the process available in “https://developer.intuit.com/us”, and it showed successfully Connected message . But I do not know if I have to do the authorize process in my local system app where code has been written. Please let me know your thoughts.

I also have a request, it would be very nice if you write another article where it is connecting to a real time client QB account.
Opcenter (NK) Support says:
Thanks

How to read all the customers from Quick Books Online in Rest App V3? I just followed the following code to read the customers but i could read only 100 customers. So if any one find to read all the customers from QBO. Please give examples cope

Customer customer=new Customer();

List customers = service.findAll(customer);
Iterator itr = customers.iterator();
while (itr.hasNext())
{
Customer customername = (Customer) itr.next();
String customerName = customername.getDisplayName();
System.out.println(customerName);
}
moore says:
Hi Sourayan,

You need to go through the authorization process with the real user. Basically all the steps except in step 3 instead of signing up with a test account, you sign in with the real user account you’d like.

When you ask which application I meant, I’m a bit confused because they are the same application. The application you add in the ‘create new app’ is the same application you are developing in java.

Does that make sense?
moore says:
Hi Opcenter.

You need to paginate the customers. Here’s the API reference:

https://developer.intuit.com/docs/0025_quickbooksapi/0050_data_services/020_key_concepts/00300_query_operations

And here’s the javadoc: http://developer-static.intuit.com/SDKDocs/QBV3Doc/ipp-v3-java-devkit-javadoc/com/intuit/ipp/query/QueryMessage.html#setStartposition(java.lang.Integer)

and http://developer-static.intuit.com/SDKDocs/QBV3Doc/ipp-v3-java-devkit-javadoc/com/intuit/ipp/query/QueryMessage.html#setMaxresults(java.lang.Integer)

Hope that helps.
moore says:
I just spent a day or so trying to find out how to do token renewal via the java SDK and ran into a wall. So if you are thinking of doing this, look to the REST API directly. Here’s the SO question I asked: http://stackoverflow.com/questions/23634945/how-can-i-reconnect-to-the-qbo-api-after-token-expiration-using-the-java-sdk/23635558
Binu says:
Can any one help me with the query to get the open invoices of a specific customer in Quickbooks api?

Thanks in advance

Binu
moore says:
Hi Binu,

Have you played around with the QB API Explorer? Are you working with the java sdk or the .NET sdk or the REST API? What have you tried so far?

This is definitely possible because I’ve searched for all closed invoices by customer.
Binu says:
Hi All,

How can we get the Consumer Secret and c ustomer key from Intuit for production release (for own use, not for an Saas)

Thanks in advance

Regards

Binu
moore says:
Hi Binu,

I wasn’t able to find a way to do this. I think this is a question best asked directly of the IPP team. Note I’ve had zero luck getting questions answered on StackOverflow, so I’d try liveconnect (https://intuitpartnerplatform.lc.intuit.com/), a blog comment ( https://developer.intuit.com/blog ) or twitter.

Good luck, and please let us know what you find!
Binu Mathai says:
Hi All,

This is what I have done.

Published the app (in private channel to bypass the review processes) to make the status of app to published from staging. Clicked on the “*” to get the consumer key and secret.

Ref : https://developer.intuit.com/docs/0025_quickbooksapi/0056_marketing_your_business/0005_publishing_your_app

Then followed the steps in

https://developer.intuit.com/docs/0025_quickbooksapi/0010_getting_started/0020_connect/0010_from_within_your_app

to connect to company account.
moore says:
Gosh, seems like it should work. You could try repeating the process and getting a different set of keys. Do you have a real account or a trial account?
Karthik says:
how can i integrate Quickbooks and open ERP by using pentaho data integration (a tool )
moore says:
Hi Karthik, not sure what kind of integration you are trying to achieve, but I would start out by looking for connectors for both QuickBooks and openerp for pentaho kettle. If they don’t exist, you’ll have to write them.
James says:
This is a much better solution :

‘use strict’;

var request = require(‘request’);
var qs = require(‘querystring’);
var config = require(‘./environment’);
var QuickBooks = require(‘node-quickbooks’);

var postBody = {
url: QuickBooks.REQUEST_TOKEN_URL,
oauth: {
// This callback url is just a placeholder so QuickBooks doesn’t get upset
callback: “http://localhost:9000/oauth/quickbooks/callback”,
consumer_key: config.quickbooks.consumerKey,
consumer_secret: config.quickbooks.consumerSecret
}
}
request.post(postBody, function (e, r, data) {
var requestToken = qs.parse(data)

var postBody = {
url: QuickBooks.ACCESS_TOKEN_URL,
oauth: {
consumer_key: config.quickbooks.consumerKey,
consumer_secret: config.quickbooks.consumerSecret,
token: requestToken.oauth_token,
token_secret: requestToken.oauth_token_secret,
verifier: config.quickbooks.companyId, // This part is key
realmId: config.quickbooks.companyId
}
}
request.post(postBody, function (e, r, data) {
var accessToken = qs.parse(data)

GLOBAL.qbo = new QuickBooks(config.quickbooks.consumerKey,
config.quickbooks.consumerSecret,
accessToken.oauth_token,
accessToken.oauth_token_secret,
config.quickbooks.companyId,
true);
console.log(‘Quickbooks Integration Complete’);
})
})
Emily Duncan Tobar says:
Will this work using Desktop QuickBooks Pro 2014?