Skip to content

Instantly share code, notes, and snippets.

@goelp
Last active January 28, 2024 11:36
Show Gist options
  • Star 10 You must be signed in to star a gist
  • Fork 7 You must be signed in to fork a gist
  • Save goelp/945ee0583e1df9663cc9e17ae5a2b9bb to your computer and use it in GitHub Desktop.
Save goelp/945ee0583e1df9663cc9e17ae5a2b9bb to your computer and use it in GitHub Desktop.
Google Apps Script OAuth2 script for QuickBooks integration with Google Sheets
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());
}
@MotorCityCobra
Copy link

MotorCityCobra commented Dec 16, 2019

Have you been able to make calls to the API from this app? Not just to authorize the app but for details about individual sales and things? What is the in the function? What are you passing into UrlFetchApp? One of many examples trying to pass credentials in the request...

function leather() {
  var url2 = "https://quickbooks.api.intuit.com/v3/company/9130347509975336/payment/11?minorversion=41"

var headers = {
  "Accept":"application/json", 
  "Content-Type":"application/json"
};

var payload = {
  "client_id" : CLIENT_ID,
  "client_secret" : CLIENT_SECRET
};

var headers2 = {
      "method" : "GET",
      "payload" : payload,
      "headers" : headers,
    };

  var response2 = UrlFetchApp.fetch(url2, headers2);
  var data3 = JSON.parse(response2.getContentText());
  sheet.appendRow([data3]);
}

Intuit has this "app playground" which seems to make the kinds of API calls I want to make from the app, but I don't understand what is going on on this page enough to extrapolate it to Google Apps Script. Any help would be greatly appreciated.

Have you tried running the same with Postman?

I went through the authorization window in Postman as per your suggestion after I found this link by Intuit.
https://developer.intuit.com/app/developer/qbo/docs/develop/postman
Funny, same thing with the GAScript. No matter what I try I get 401 unauthorized. Much like the GAScript, I don't know what else I'm supposed to do since the app is already authorized and the 401 doesn't give me specific answers. Could this be because I'm in the free trial? I'm going in circles trying to figure this out.

EDIT: I can get a 200 code with sandbox only in Postman. Still not working through Postman in production.

I have a doPost function setup in the Google App Script app to receive webhooks and it does work. I was able to authorize the Quickbooks Online account. It does receive webhooks from payments or things I do in the Quickbooks account linked to the app. I have them show up in the spreadsheet.

@MotorCityCobra
Copy link

MotorCityCobra commented Dec 16, 2019

Okay, figured it out. Happened upon the correct way to send Intuit the credentials in a way it would authorize. No more 401 errors.
Now I can make API calls and get JSON returned, (which also took some work to properly request JSON and not XML or whatever it was sending by default.

function callAPI() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var url2 = "https://quickbooks.api.intuit.com/v3/company/9130000009975336/account/11?minorversion=42"

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

  var response2 = UrlFetchApp.fetch(url2, headers2);
  var data3 = JSON.parse(response2.getContentText());
  sheet.appendRow([JSON.stringify(data3)]);
}

@MotorCityCobra
Copy link

If I can ask a slightly off topic question,
What would this be if I wrote and deployed this in NodeJS? Would it be called a REST API? Where would you host it? Where do you put the redirect URI and the address for the webhooks? Google Apps Script has been very spotty receiving webhooks and executing functions.
Leaning how to build an app that uses Oauth2 this way is very hard to learn about since I don't specifically know what to do.

@jarednova
Copy link

@goelp: you are a godsend! I'm damn close, I think I'm just not entirely understanding what should be the proper value for url inside of run(). When I use the script as-is (after entering the correct CLIENT_ID, REDIRECT_URI, etc.) and authorizing with QBO, I get this response:

<!DOCTYPE html>
<html class="">
    <head>
        <meta charset="utf-8"/>
        <meta name="application-name" content="QuickBooks"/>
        <meta http-equiv="x-ua-compatible" content="IE=edge" />
        <meta name="google-site-verification" content="hiEXDzwqUxxMY5KZkAkeHBn6J0gy2Ne1gJdm77RkGbk"/>
        <meta id="viewPortMetaTag" name="viewport" content="width=1024, initial-scale=1, maximum-scale=1" />
        <meta name="apple-mobile-web-app-title" content="QuickBooks" />

        <title>QuickBooks</title>
        <link rel="apple-touch-icon icon shortcut" type="image/png" href="https://uiclassic.intuitcdn.net/v1928.229/scripts/harmony/images/qbo-ball.png"/>
        <link rel="stylesheet" type="text/css" href="https://uiclassic.intuitcdn.net/v1928.229/scripts/harmony/css/harmony.css"/>

        <style>
            body, html {
                width: 100%;
                height: 100%;
                background: #193048;
                overflow: auto;
            }
            body.is-ie8 {
                font-size: 12px;
            }
            .qboform {
                background: #fff;
                width: 450px;
                margin-top: 10%;
                margin-left: auto;
                margin-right: auto;
            }
            .msgTitle {
                text-align: center;
                padding-bottom: 20px;
                padding-top: 35px;
            }
            .upgradeContent {
                background: #edeef0;
                height: 285px;
                padding-top: 35px;
                padding-left: 50px;
            }
            .qbhero {
                height: 260px;
                width: 360px;
                background: url("https://uiclassic.intuitcdn.net/v1928.229/scripts/harmony/images/qbodevices.png") no-repeat;
                float: left;
                background-size: 350px;
                background-position: 0 -50px
            }
            .msgFooter {
                clear:left;
                padding-top: 35px;
                padding-bottom: 50px;
                text-align: center;
            }
        </style>
    </head>

    <body>
        <div class="qboform">
            <div class="msgTitle page-title-30light">Page not found!</div>
            <div class="upgradeContent">
                <div class="qbhero"></div>
            </div>
            <div class="msgFooter subsection18TitleTextLight">Go back to <a href="/">qbo.intuit.com</a></div>
        </div>
    </body>
</html>

is the proper value for url ...
var url = 'https://sandbox-quickbooks.api.intuit.com/';
or
var url = 'https://sandbox-quickbooks.api.intuit.com/v3/company/4620816365035886270/'; (my sandboxed company ID)
or something else?

THANK YOU SO MUCH!

@jarednova
Copy link

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/

@machinsk
Copy link

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

@AmauryVanEspen
Copy link

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

@asandrini
Copy link

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!!!

@pgoswami3
Copy link

pgoswami3 commented Jul 21, 2021

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?

@saisub
Copy link

saisub commented Aug 18, 2021

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

@saisub
Copy link

saisub commented Aug 18, 2021

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

@augustodelucena
Copy link

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.

@saisub
Copy link

saisub commented Aug 26, 2021

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

@augustodelucena
Copy link

augustodelucena commented Aug 26, 2021

@saisub so now it's working again??

I still get

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

@saisub
Copy link

saisub commented Aug 26, 2021 via email

@sylphrena0
Copy link

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

@goelp
Copy link
Author

goelp commented Jan 13, 2022

@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/

@Crushtests
Copy link

Crushtests commented Oct 5, 2022

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

@arthurbrazil
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment