API Calls to ServiceNow from Google Apps Script with OAuth

Michael
Michael
Mar 7 · 7 min read

Have you ever thought that it might be useful to connect a Google Apps Script to your ServiceNow instance? Perhaps you’d like a new way to quickly download a list of configuration items from your change management database to a sheet, or you’d like to upload a selected range of cells from a sheet as new records in SNOW, one record per row? In my world we heavily utilize both ServiceNow and Google Apps Script, so I decided to investigate what is possible.


Prerequisites for Authenticating to ServiceNow APIs

Before going too far into this guide, please understand that I make the assumption that you already have an identity provider that supports OAuth/OpenID, and that you can or have someone you know who can configure ServiceNow to trust that identity provider.

This guide is focused more on the coding than how to configure your identity provider and ServiceNow.

While ServiceNow does support username/password authentication with their APIs, OAuth is generally considered to be more secure and provides a better user experience.

I used OpenAM as an identity provider, but this should work with any identity provider which supports the same open standards.

Create An AppsScript

First create an Apps Script file in Drive. It can be any type of Apps Script: a standalone apps script, an apps script bound to a G Suite sheet or doc, or an apps script web application. All will work with the SNow API.

Create OAuth2.gs

After you have created your Apps Script, create a new script file like this and name it OAuth2.gs:

Next, as described under “Setup” in this guide, follow the alternative instructions to copy the contents from /dist/OAuth2.gs to your new OAuth2.gs file. The main reason for copying and not adding it as a library by script ID is to improve performance. Importing libraries do not run as fast. You should now have something like this:

Create ServiceNow.gs

ServiceNow.gs will implement an OAuth client using OAuth2.gs, which can then provide signed JWTs for use by URLFetch to make API calls to ServiceNow. In addition to setting up authentication to ServiceNow, this also provides some low level functions to get and create records.

In ServiceNow.gs, create the following functions:

function getSNOWService(){
return OAuth2.createService('snow')
.setAuthorizationBaseUrl('https://my-identity-provider.com/openam/oauth2/authorize?')
.setTokenUrl('https://my-identity-provider.com/openam/oauth2/access_token')
.setClientId('urn:docs.google.com:spreadsheets:<my_script_id>')
.setClientSecret('<my_client_secret>')
.setScope('openid profile cloudEmail email uid')
.setCallbackFunction('authCallback')
.setPropertyStore(PropertiesService.getUserProperties())
}
// Log the auth URL
function logAuthURL(){
Logger.log(getAuthURL());
}
// Get the auth URL to login into SNOW, or null if already logged in
function getAuthURL(){
var snowService = getSNOWService();
if(!snowService.hasAccess()){
var authorizationUrl = snowService.getAuthorizationUrl();
return authorizationUrl;
} else {
Logger.log("SNOW service already has access");
return null;
}
}

In the above example we create an OAuth service using the parameters necessary by an OpenAM identity provider. Replace the bold section (client_id and client_secret) with the client you register with your identity provider that is authorized to access your ServiceNow instance.

Generate the Callback URL

Once you have setup OAuth2.gs and ServiceNow.gs, you can generate a callback URL which might be necessary for your identity provider to register your script:

Run the function logAuthURL():

This will log a URL that is intended for a user to visit in a browser. But don’t go there yet! We just need the callback URL to register the script with our identity provider. Click View → Logs:

The URL logged will include a 2nd URL in the URL parameter redirect_uri. Copy it. It should look something like this: https%3A%2F%2Fscript.google.com%2Fmacros%2Fd%your_script_id%2Fusercallback

Register the CallBack URL with your Identity Provider.

Login

Once your identity provider has your AppsScript callback URL registered for OAuth logins, and ServiceNow is configured to trust the identity provider, you are ready to login and make authenticated API calls to ServiceNow. To login, visit the full URL returned from the function getAuthURL(). To do this we wrote the following code:

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Login')
.addItem('Display Auth URL', 'displayAuthURL')
.addToUi();
}
function displayAuthURL(){
var authUrl = getAuthURL();
if(authUrl == null){
showAlert('Auth URL:', 'You are already logged in. Noice!');
}
else{
showAlert('Auth URL:', authUrl);
}
}
// Handle callback from IdP
function authCallback(request) {
var snowService = getSNOWService();
var isAuthorized = snowService.handleCallback(request);
if (isAuthorized){
return HtmlService.createHtmlOutput('Success! You can close this tab and return.');
} else {
return HtmlService.createHtmlOutput('Denied. You can close this tab and try authenticating again.');
}
}

The function onOpen() creates a “Login” menu item and binds it to displayAuthURL() which shows an alert with the URL. From there you can simply copy the shown URL, paste it into a new tab, and follow the login process. I’m sure some of you AppsScript guru’s can make a better UI than this but at least you hopefully get the point and can login. After completing the login process, you will be redirected back to this script’s /usercallback URI which is handled automatically by authCallback() to store your id_token and access_token to the AppScript built in properties service. It also gives you an opportunity to do something nice for the user. Rumor has it that you can add some javascript with a window.close() to automatically close the newly created tab, so that they are taken back to the script which started the login process.

Make Authenticated API Calls

Now that you’re logged in you’re ready to make some API calls.

ServiceNow is heavily centered around the Tables API, which means you’ll always need to know which table name you want to work with, and what fields it has. To work with tables, we created a few lower level functions that can be implemented by higher level sections of your script. These lower level functions do basic things like generate the URL, add the id_token as a bearer to the headers, and place the payload into the request.

// Get a single SNOW table based on a sys_id and return the result
function getTable(table_name, sys_id){
var snowService = getSNOWService();
var URL = 'https://my-snow-instance.myshn.net/api/now/table/' + table_name + '/' + sys_id;
Logger.log('Requesting single table from URL: ' + URL);
var response = UrlFetchApp.fetch(URL, {
headers: {
"Authorization": 'Bearer ' + snowService.getIdToken(),
}
});
return response;
}
// Get a list of SNOW tables based on a sysparm_query and return the list of results
function getTables(table_name, sysparm_query){
var snowService = getSNOWService();
var URL = 'https://my-snow-instance.myshn.net/api/now/table/' + table_name + "?";
Logger.log('HTTP GET to : ' + URL);
Logger.log('sysparm_query: ' + sysparm_query);
var query_parameters = {
'sysparm_query': sysparm_query
}
var response = UrlFetchApp.fetch(URL + generateQueryString(query_parameters), {
headers: {
"Authorization": 'Bearer ' + snowService.getIdToken(),
}
});
return response;
}
// Create SNOW table record and return the results
function insertTable(table_name, payload){
var snowService = getSNOWService();
var URL = 'https://my-snow-instance/api/now/table/' + table_name;
Logger.log('Inserting record to table: ' + table_name);
var response = UrlFetchApp.fetch(URL, {
'method': 'post',
'headers': {
"Authorization": 'Bearer ' + snowService.getIdToken(),
},
'payload': JSON.stringify(payload),
'contentType': 'application/json',
'muteHttpExceptions' : true
});
return response;
}
// Creates a string of text from a JSON object that can be added to a URL as query parameters
function generateQueryString(data) {
const params = [];
for (var d in data)
params.push(encodeURIComponent(d) + '=' + encodeURIComponent(data[d]));
return params.join('&');
}

In the code above we have a function to get a single table, to get a list of tables based on a query parameter, a function to insert new tables, and a utility function to help format query parameters.

To execute the code above you could do something simple like this to get a single Portfolio:

function getPortfolio(){
Logger.log(getTable('pm_portfolio', 'my_portfolio_sys_id'));
}

From here I also added some mid level and high level functions. The mid level functions implement getTable() and getTables() but are specific to certain types of records like Incidents, Portfolios, Projects, etc. They help by adding things like table names, and some recursive queries where we need to get all projects based on an umbrella group (two queries). I won’t add them but wanted to give an idea on how one can progress from here.

ServiceNow’s Tables API is documented here. We also found it useful that if you don’t know how to form a sysparm_query to query for a list of tables, that you can go into the normal ServiceNow UI. For example, if you setup a filter to list out some incidents in the normal UI, you’ll see a breadcrumb trail from which you can right click on to get query parameters which work as a sysparm_query via API:

In our case, this is what it gave us: active=true^state!=6. So as long as you know how to use the filter feature to find what you’re looking for in ServiceNow, you can also form a query that is usable via API.


I hope that this guide helps you get started with AppScript integrations with ServiceNow using OAuth instead of username/password!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade