-
-
Save goelp/945ee0583e1df9663cc9e17ae5a2b9bb to your computer and use it in GitHub Desktop.
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()); | |
} |
@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?
@goelp Are you able to provide help in setting up the connection so that data can be synced from google sheets to QBO?
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 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.)
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.
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)]);
}
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.
@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!
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
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'
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:
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.