Google Apps Script OAuth2 script for QuickBooks integration with Google Sheets

gas_qbo_oauth2.js

var CLIENT_ID = '...'; // Get from Quickbooks Developer Console
var CLIENT_SECRET = '...'; // Get from Quickbooks Developer Console
var BASE_AUTH_URL = 'https://appcenter.intuit.com/connect/oauth2';
var TOKEN_URL = 'https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer';
var API_SCOPE = 'com.intuit.quickbooks.accounting';
var REDIRECT_URI = '...'; // Generate using the logRedirectUri() function mentioned at the end of this script
var RESPONSE_TYPE = 'code';

/**
 * Authorizes and makes a request to the Quickbooks API using OAuth 2.
*/ 
function run() {
  var service = getService();
  if (service.hasAccess()) {
    var url = 'https://sandbox-quickbooks.api.intuit.com/';
    var response = UrlFetchApp.fetch(url, {
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      }
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
*/ 
function reset() {
  getService().reset();
}

/**
 * Configures the service.
 */
function getService() {
  return OAuth2.createService('Quickbooks')
      .setAuthorizationBaseUrl(BASE_AUTH_URL)
      .setTokenUrl(TOKEN_URL)
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      .setScope(API_SCOPE)
      .setCallbackFunction('authCallback')
      .setParam('response_type', RESPONSE_TYPE)
      .setParam('state', getStateToken('authCallback')) // function to generate the state token on the fly
      .setPropertyStore(PropertiesService.getUserProperties());
}

/**
 * Handles the OAuth callback
 */
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    Logger.log("Success!");
    return HtmlService.createHtmlOutput('Success!');
  } else {
    Logger.log("Denied!");
    return HtmlService.createHtmlOutput('Denied.');
  }
}

/** 
 * Generate a State Token
 */
function getStateToken(callbackFunction){
 var stateToken = ScriptApp.newStateToken()
     .withMethod(callbackFunction)
     .withTimeout(120)
     .createToken();
 return stateToken;
}

/**
 * Logs the redirect URI. Run this function to get the REDIRECT_URI to be mentioned at the top of this script. 
 */
function logRedirectUri() {
  Logger.log(getService().getRedirectUri());
}

For future Googler's I was able to find the answer in this article: https://boopis.com/blog/2018/10/02/connecting-google-sheets-quickbooks/

Can I get the RealmID (CompanyID) from the service response object?

Hi @jarednova

For future Googler's I was able to find the answer in this article: https://boopis.com/blog/2018/10/02/connecting-google-sheets-quickbooks/

the content is linked to OAuth1 and not OAuth2
have you got access to a working google apps script with Oauth2 ?
Thank you
Amaury

Don't forget to add
https://script.google.com/macros/d/{SCRIPT ID}/usercallback
to the Redirect URIs where {SCRIPT ID} has to be replaced with your script id that you can find running this simple function:
function ScriptID(){Logger.log(ScriptApp.getScriptId())}

MANY THANKS for this script!!!

Being a beginner to Apps Script, QB, and Oauth2 I tried to run your code @goelp in Apps Script but didn't work.

Generate callback uri using logRedirectUri() and updated the variable REDIRECT_URI . Called the run() but it went to the else statement Open the following URL and re-run the script:- may be because of aurthorization? Can you please help me?

On the other hand @MotorCityCobra, I tried to run your code in postman as well as in Apps script but ran into https://quickbooks.api.intuit.com/v3/company/___realm id___/companyinfo/ ___realm id____ 401 error(SRV-110-Authentication Failure , statusCode: 401). Would you mind sharing your code/hint that worked for you?

It's been working well for a year but today I am getting a server error in the getStateToken(callbackFunction) function

I can get the service but service but when I test if it has access I get a server error

Hey @saisub! Have you find the solution for this? I have just tried to apply the script and I'm having the same issue. Please anybody help us.

It just started working after some time. So I chalked it up to errors at Google

@saisub so now it's working again??

I still get

Exception: Argument cannot be null: method getStateToken @ Code.gs:72

yes

…

On Thu, Aug 26, 2021 at 8:58 AM augustodelucena ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @saisub <https://github.com/saisub> so now it's working again?? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/945ee0583e1df9663cc9e17ae5a2b9bb#gistcomment-3872815>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGNQTGX4KAXHCRAEFCO6A6LT6ZCBTANCNFSM4HYQGFPQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> .

Works great. Any idea how to modify to work with queries?

This code:

function doRequest() {
  var service = getService();
  if (service.hasAccess()) {
    var url = 'https://quickbooks.api.intuit.com/v3/company/<myid...>';
    var url = url + '/query?query=select%20*%20from%20Account%20where%20Metadata.CreateTime%20>%20\'2014-12-31\'' + '&minorversion=63'
    var response = UrlFetchApp.fetch(url, {
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      }
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(result)
    return result; //eats into json response, leaving only acct info
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
    return "Authorize URL in logger";
  }
}

gives me this error:
Exception: Invalid argument: https://quickbooks.api.intuit.com/v3/company/123145927198904/query?query=select%20*%20from%20Account%20where%20Metadata.CreateTime%20>%20'2014-12-31'&minorversion=63

@Nightwielder1 may be its got with the encoding of the URL properly.
Example, when encoded correctly:
This:
https://quickbooks.api.intuit.com/v3/company/1234/query?query=SELECT FROM Customer WHERE Metadata.LastUpdatedTime > '2011-08-10T10:20:30-0700'

Becomes:
https://quickbooks.api.intuit.com/v3/company/1234/query?query=SELECT%20FROM%20Customer%20WHERE%20Metadata.LastUpdatedTime%20%3E+%272011-08-10T10%3A20%3A30-0700%27

You could try using an online encoder like this one: https://meyerweb.com/eric/tools/dencoder/

Hi, @goelp!
Thank you for the code, it's all working.
But when I'm trying to get some info from the QB it sends me the 403 error.
Basically, I have some problem with authorization, but I still can't find where might be the issue.

I use the code to get - as the @MotorCityCobra wrote:

function getInvoices() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TEST');
var url = "https://quickbooks.api.intuit.com/v3/company/" + companyID + "/account?minorversion=65"

var headers = {
"muteHttpExceptions": true,
"headers": {
"Accept": "application/json",
"Content-Type": "application/json",
"Authorization": "Bearer " + getService().getAccessToken()
}
};

var response = UrlFetchApp.fetch(url, headers);
var data = JSON.parse(response.getContentText());
sheet.appendRow([JSON.stringify(data)]);
}

I've googled the error code - but I see that everything is correct with token, redirect link, scope...
this API blows my mind :-)

I keep getting this.
`Exception: Request failed for https://quickbooks.api.intuit.com returned code 404. Truncated server response:

... (use muteHttpExceptions option to examine full response)`