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());
}
@tistudios
Copy link

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

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

@jdflament
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
Copy link

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
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
Copy link
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
Copy link

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

@MotorCityCobra
Copy link

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
Copy link
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
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