Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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());
}
@tistudios

This comment has been minimized.

Copy link

tistudios commented Oct 30, 2018

AppScript is giving me this error: OAuth2 on line 40 is not defined.

screen shot 2018-10-30 at 9 10 09 am

@jdflament

This comment has been minimized.

Copy link

jdflament commented Nov 9, 2018

Hi @tistudios,

If you want to get the OAuth2 library, you need to go to Ressources => Libraries at the top on the page, and on the "Find library" field, put this script ID : 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF (ref : https://github.com/gsuitedevs/apps-script-oauth2#setup).
Then click on the "Add" button, select a version for the library, save, and rerun your script.

@dianaalbert

This comment has been minimized.

Copy link

dianaalbert commented Jan 11, 2019

Hi @goelp,
I'm using your code to connect to QBO and it's working just fine - what we're planning is to set up some functions to be executed using time-driven triggers.
But as far as I understand, the QBO Token expires every 100 days - is there a way to handle this without manually set up the OAuth2 connection each time it expires?
Thanks!

@sajox5

This comment has been minimized.

Copy link

sajox5 commented Jun 16, 2019

Is there a way to sync data from a Google Sheet to a Quickbooks? I can’t find any free script for this online, and i’ve been searching for hours. I have found tons of ways to sync data from Quickbooks to Sheets, but not the reverse.

@goelp

This comment has been minimized.

Copy link
Owner Author

goelp commented Jun 16, 2019

@sajox5 yes there using their APIs. You may not find code for it since it would depend gharelu on what you would like to achieve. For eg, I have a script that manages multiple invoicing series in Google Sheets which then creates invoices based on that back in QBO. What are you after?

@bryanmitchiner

This comment has been minimized.

Copy link

bryanmitchiner commented Nov 14, 2019

@goelp Are you able to provide help in setting up the connection so that data can be synced from google sheets to QBO?

@MotorCityCobra

This comment has been minimized.

Copy link

MotorCityCobra commented Dec 15, 2019

I'd like to make calls to the API with this. I'm getting a 401 error with

function API_Call() {
  var url2 = "https://quickbooks.api.intuit.com/v3/company/9130347509111876/payment/11?minorversion=42"

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

I've passed the ID and secret to APIs different than Intuit in a GAS before. I tried a few variations and always get a 401 when I run the function.

(The call to the API will eventually go inside a doPost function when it receives a webhook but I'm leaving that part out for simplicity.)

@goelp

This comment has been minimized.

Copy link
Owner Author

goelp commented Dec 16, 2019

@MotorCityCobra Have you tried running the same with Postman? See if it is working via that to confirm that things are set up correctly in Quickbooks. Try This: https://developer.intuit.com/app/developer/qbo/docs/develop/postman

I'd like to make calls to the API with this. I'm getting a 401 error with

function API_Call() {
  var url2 = "https://quickbooks.api.intuit.com/v3/company/9130347509111876/payment/11?minorversion=42"

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

I've passed the ID and secret to APIs different than Intuit in a GAS before. I tried a few variations and always get a 401 when I run the function.

(The call to the API will eventually go inside a doPost function when it receives a webhook but I'm leaving that part out for simplicity.)

@MotorCityCobra

This comment has been minimized.

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

This comment has been minimized.

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

This comment has been minimized.

Copy link

MotorCityCobra commented Dec 23, 2019

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

This comment has been minimized.

Copy link

jarednova commented Feb 13, 2020

@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

This comment has been minimized.

Copy link

jarednova commented Feb 13, 2020

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

This comment has been minimized.

Copy link

machinsk commented Feb 16, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.