Skip to content

Want your next software purchase to ‘talk’ to your existing systems?

I wrote a post, over at the Geek Estate Blog (one of the best real estate tech blogs, imho) about APIs and why if you are a realtor or a broker, you should look for software with an API

Well, first, let’s talk about what an API is. API stands for “Applications Programming Interface”–not very illuminating. What that means is that the software providing the API has a way to allow other software (applications) to interact (interface) with it, with no human involvement. That is, the two software systems can ‘talk’ to each other.

Full post here.

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).

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):

  1. Start your IE browser (I’m using IE 10 on windows 8)
  2. Go to https://developer.intuit.com/us
  3. Sign up (click the ‘join’ link), click ‘remember me’
  4. Go to your email and find the verify link that was sent to you. Paste it into your IE browser.
  5. Sign in again
  6. Click on ‘My Apps’
  7. Click on ‘Create New App’, then ‘QuickBooks API’
  8. 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.
  9. Select the level of data access you need. Since this is a test app, you can select ‘All Accounting’
  10. Click ‘Save’
  11. 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.)
  12. Click on ‘Free Trial’
  13. Click on ‘QuickBooks Online Plus’
  14. Click on ‘Already have an Intuit user ID’
  15. Fill out the username and password you used on when you signed up for your developer account.
  16. Ignore the upsell, if any
  17. Click the customers tab
  18. Click on the ‘new customer’ button
  19. Enter a first name and last name then press save
  20. Open a new tab and go to the API Console
  21. 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.
  22. Open a new tab and go to the OAuth playground
  23. Go back to the developer.intuit.com tab
  24. Grab your app token (looks like b3197323bda36333b4b5fb17774440fe34d6)
  25. 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.
  26. Click ‘Get Key and Secret using App Token’
  27. Note the consumer key and consumer secret, you’ll need them later.
  28. Click ‘Get Request Token Using Key and Secret’
  29. Click ‘Authorize Request Token’
  30. You should see a message like ‘testapp3 would like to access your Intuit company data’
  31. Click ‘Authorize’
  32. You should see a message like ‘You are securely connected to testapp3’
  33. Click ‘Return to TestApp3’
  34. 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.
  35. Go to the SDKs page of developer.intuit.com
  36. Pick your language of choice, and follow the installation instructions.
  37. 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
  38. 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).