Connecting Google Sheets With Quickbooks

Google Sheets is a great tool to interactively work with an assortment of APIs to work as an intermediary database for experiments or small scale data processing. Here we’re going to look at how we can query our Quickbooks data and display it in a sheet to then merge with any other data set like analytics or time logs, etc.

First we’ll head to the “Tools” tab and select “Script Editor”. Once the new script editor window has opened, we’ll want to add the Oauth library that we’ll use to authenticate with Quickbooks. Go to “Resources” and select “Libraries…” and add Oauth1.

Install Google Addon

We created a Google Addon with easy to use filters so you don’t have to code your own script.

Check out Quickbooks Connect

Next we’ll have to go ahead and create an app under the Quickbooks developer console. You will be provided a consumer key and consumer secret token. Save those values along with your company id and we’ll store those values as variables for use in querying Quickbooks.

// Quickbooks Oauth Credentials
var QB_CONSUMER_KEY = 'yourconsumerkey';
var QB_CONSUMER_SECRET = 'yourconsumersecret';
var QB_COMPANY_ID = 'yourcompanyid';
// Different url for sandbox environment
var QB_API_URL = 'https://sandbox-quickbooks.api.intuit.com';
// Live url
//var QB_API_URL = 'https://quickbooks.api.intuit.com';

Next we’ll create a couple functions to interact with our Quickbooks app to authenticate with Oauth:

function getQBService() {
  return OAuth1.createService('quickbooks')
    // Set the endpoint URLs.
    .setAccessTokenUrl('https://oauth.intuit.com/oauth/v1/get_access_token')
    .setRequestTokenUrl('https://oauth.intuit.com/oauth/v1/get_request_token')
    .setAuthorizationUrl(' https://appcenter.intuit.com/Connect/Begin')

    // Set the consumer key and secret.
    .setConsumerKey(QB_CONSUMER_KEY)
    .setConsumerSecret(QB_CONSUMER_SECRET)

    // Set the name of the callback function in the script referenced
    // above that should be invoked to complete the OAuth flow.
    .setCallbackFunction('authCallback')

    // Set the property store where authorized tokens should be persisted.
    //.setPropertyStore(PropertiesService.getUserProperties());
    .setPropertyStore(PropertiesService.getScriptProperties());
}

function authCallback(request) {
  var service = getQBService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

And lastly, we’ll go ahead and query payment data for a particular customer from our Quickbooks database and add it to our spreadsheet:

function getQbData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var service = getQBService();
  var accountId = 'customerId';
  var url = QB_API_URL + '/v3/company/' + QB_COMPANY_ID + '/query';

  var query = "SELECT * FROM Payment WHERE CustomerRef = '" 
    + accountId;

  url = url + '?' + 'query=' + encodeURIComponent(query);
  
  var options = {
    "headers" : { "Accept" : "application/json" },
    "muteHttpExceptions" : true
  };
   
  if (!service.hasAccess()) {
    var authorizationUrl = service.authorize();
    var html = HtmlService.createHtmlOutput(
        '&lt;a href="' + authorizationUrl + '" target="_blank"&gt;Authorize&lt;/a&gt;. ' +
        'Reopen the sidebar when the authorization is complete.');
  } else {
    var response = service.setMethod("post").fetch(url, options);

    if (response.getResponseCode() == 200) {
      var responseObject = JSON.parse(response.getContentText());
      
      if(responseObject["QueryResponse"]["Payment"] != undefined) {

        var paymentRows = responseObject["QueryResponse"]["Payment"].map(function(payment) {
          var date = payment["TxnDate"];
          var amount = payment["TotalAmt"];
          var ref = payment["PaymentRefNum"] ? payment["PaymentRefNum"] : "";
          return [date, amount, ref];
        });

        // Add data to quickbooks raw sheet
        sheet.getRange(sheet.lastRow + 1, 1, paymentRows.length, 3).setValues(paymentRows);
      }

    } 
  } 
  
}

Install Google Addon

We created a Google Addon with easy to use filters so you don’t have to code your own script.

Check out Quickbooks Connect

And there you have it. Now you can get any data available via Ituit Quickbooks API and store it however you like in Google Sheets and even run calculations with other data sets.

2 responses to “Connecting Google Sheets With Quickbooks”

Jonathan

Mar 12, 2019

Hello!

Thank you for this resource! Have you been successful in connecting Google Sheets to Quickbooks API with OAuth2?

https://gist.github.com/goelp/945ee0583e1df9663cc9e17ae5a2b9bb
Sebastien Benoit

Apr 28, 2019

Thanks so much for this! Since they don’t allow OAuth1 anymore I had to tweak it to work with OAuth2, which wasn’t too bad. I wish I saw Jonathan’s comment before because I was looking for a link like that, it would have helped!

Any plans to write a similar post to help folks get set up for QBO to Sheets with OAuth2? Quickbooks API documentation is really tough for a newbie like me and the closest they get to Google Scripts is Node.JS, which doesn’t help me much…

Oh, and thank you for your Quickbooks Connect Sheets add-on! It works perfectly, and opened my world for some automation possibilities.