Integrating Blackbaud FENXT with Google Sheets

At work we needed a simple way for staff to view certain GL code data without giving them unecessarily complicated access to Blackbaud Financial Edge NXT. We already use Google Fundamentals and I had a thought that I could potentially pull data through Blackbaud's SkyAPI. Turns out I can.

Setting up SkyAPI

To get started we need to set up a project. I won't go into the details of setting up a developer account but it is fairly simple to do as an administrator.

Once you're logged into blackbaud.com you will see that there is a SKY Developer tab. Click here and then click My Applications.From there Click + Add and enter your new application's details (Name etc).



Once you're done that you'll get to the main application settings page. Go ahead and record your Application ID (OAuth client_id) and your Primary application secret (OAuth client_secret). You'll need both of these for when you set up your Google Sheet.



Under the Scopes section, select Limited Data Access and only select the portions of Blackbaud that you actually need to read from. For myself, I chose Financial Edge NXT and selected Read as my only option.

Setting up Google Sheets

Before I fill in the Redirect URI section of my Blackbaud application, I need to figure out what those exactly are. Start a new Google sheet and record the URL of your newly created sheet up to the question mark (ex: https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxx/edit). Next, Click on the Extensions menu and select Apps Script. Once this has opened and you are presented the code editor you will have to record the document ID. This is found between the https://script.google.com/u/0/home/projects/ and /edit portion of the URL at the top of the page.



Now that you have these exciting pieces of information, you can head on back to Blackbaud and add your sheet URL as a Redirect URI. Add a second URI in this format for your Google Script: https://script.google.com/macros/d/xxxxxxxxxxxxxxx/usercallback where the x marks are the ID of the google script doc you recorded earlier.



From here you should almost be ready to go. The final thing to do is activate your fancy new app on in Blackbaud Core. To do this head over to the Marketplace and click Manage. From here, click on the Connect App button and paste in the Application ID which can be found in the Sky Developer application settings page. Further information can be found here: SKY API Documentation: Applications

Let's start coding!

Whew... Okay, so now we need to authorize Google to work with our new Sky API application. First we need to grab an OAuth2 Library so we can communicate with Sky API. Go ahead and head over to this URL to grab the most recent script ID for apps-script-oauth2. Here you'll find instructions on how to add this library to your project. Next, let's initiate a connection!

var OAuth2 = OAuth2.createService('blackbaud')
    .setAuthorizationBaseUrl('https://app.blackbaud.com/oauth/authorize')
    .setTokenUrl('https://oauth2.sky.blackbaud.com/token')
    .setClientId('xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxx') // Replace with your client ID provided by Blackbaud
    .setClientSecret('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') // Replace with your client secret provided by Blackbaud
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
	// Replace with the scopes required by the Blackbaud API
    .setScope('https://api.sky.blackbaud.com/generalledger/v1/transactiondistributions') 
    .setParam('access_type', 'offline')
    .setParam('approval_prompt', 'force')
	// Replace with your redirect URI provided by Blackbaud. Remember this is actually the ID of this script.
    .setParam('redirect_uri', 'https://script.google.com/macros/d/xxxxxxxxxxxxxxxx/usercallback'); 

// Function to initiate OAuth flow
function startOAuth2() {
  var authorizationUrl = OAuth2.getAuthorizationUrl();
  var template = HtmlService.createTemplate('<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>');
  template.authorizationUrl = authorizationUrl;
  var page = template.evaluate();
  //SpreadsheetApp.getUi().showModalDialog(page, 'Authorize this app');
  SpreadsheetApp.getUi().showSidebar(page);
}

// Callback function after authorization
function authCallback(request) {
  var isAuthorized = OAuth2.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Authorization denied. Please try again.');
  }
}

function fetchDataWithEndpoint(endpoint){
  if (!OAuth2.hasAccess()) {
    return 'Error: Not authorized';
  }
  
  // Make authenticated requests to the Blackbaud API using OAuth2 library
  var url = endpoint; 
  var subscriptionKey = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'; //Your Blackbaud Subscription Key
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Bb-Api-Subscription-Key': subscriptionKey,
      Authorization: 'Bearer ' + OAuth2.getAccessToken()
      
    }
  });
  
  var data = response.getContentText();
  var jsonData = JSON.parse(data);
  return jsonData;
}

If you've done everything correctly, after you've saved your script, if you run the startOAuth2() function, you will get an authorization link that pops up on the side of your Google Sheet. If you now click this you will be prompted to Authorize your account with BlackBaud and Google. Go ahead and do this. Your sheet is now linked to Blackbaud and you can start grabbing data!

//Function to run all GLs I select
function fetchAllGL()
{
  fetchGL("10-xxxx-05");
  fetchGL("10-xxxx-06");
  fetchGL("10-xxxx-05");
}

// Function to fetch GL info
function fetchGL(gl) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(gl);

 //get Fiscal Start Date
 //Where FrontPage is my main viewing page and cell B2 on this page is where I define my Fiscal Start date.
 var fiscalYearStart = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FrontPage').getRange("B2").getValues();

$apiEndpoint='https://api.sky.blackbaud.com/generalledger/v1/transactiondistributions?limit=5000&from_date='+fiscalYearStart+'&account_number='+gl

jsonData = fetchDataWithEndpoint($apiEndpoint)

//Clear Sheet
sheet.clear(); // Clear existing content

// Extract the array of values from the "value" key
  var values = jsonData.value;
  
  // Define the headers for the array
  var headers = ['Date Added','Post Date', 'Added By', 'Reference', 'Transaction Amount', 'Account Number'];
  
  // Create the array to store the data
  var dataArray = [];
  dataArray.push(headers); // Add headers as the first row
  
  // Iterate through the array of values and extract the data
  values.forEach(function(item) {
    var row = [];
    row.push(item.date_added);
    row.push(item.post_date);
    row.push(item.added_by);
    row.push(item.reference);
    row.push(item.transaction_amount);
    row.push(item.account_number);
    dataArray.push(row);
  });
  
  // Get the dimensions of the data array
  var numRows = dataArray.length;
  var numCols = dataArray[0].length;
  
  // Set the range to populate the data
  var range = sheet.getRange(1, 1, numRows, numCols);
  
  // Populate the data into the range
  range.setValues(dataArray);

  //Remove Duplicates
  removeDuplicateRows(gl);
}


function removeDuplicateRows(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  
  var uniqueRows = [];
  var hash = {};
  
  for (var i = 0; i < values.length; i++) {
    var row = values[i].join('');
    if (!hash[row]) {
      uniqueRows.push(values[i]);
      hash[row] = true;
    }
  }
  
  // Clear existing data in the sheet
  sheet.clear();
  
  // Populate the unique rows back into the sheet
  var numRows = uniqueRows.length;
  var numCols = uniqueRows[0].length;
  var range = sheet.getRange(1, 1, numRows, numCols);
  range.setValues(uniqueRows);
}

Okay, so if you run fetchAllGL() this function will dump all of the 'Date Added','Post Date', 'Added By', 'Reference', 'Transaction Amount' and 'Account Number' amounts in separate columns in a sheet named after the GLs named in the fetchAllGL() function (such as 10-xxxx-05 in my example).



In this simple script you will already need to have created the GL sheets accordingly. The other variable you will need to define is the fiscal start date which I have put on a handy 'frontpage' in cell B2. If you wish to pull other fields from the GL, you can do this by referring to the SKY API documentation and modifiying the row.push() items in the fetchGL section.

From this point you should have all the data you need. In my projects, I tend to hide the GL sheets as raw data input and then pull the fields I need into a nicer view on another page I create for my users.

Other ways to improve this...

The code above forms the basics of how to connect Google Sheets to other APIs within Blackbaud's ecosystem. I've also created one to track our current enrollment by pulling enrollment data from the Education side of Blackbaud.

To keep the data pull easy for your end users, you can also set a trigger in Google App Scripts to run the fetchAllGL() function on a timed basis. I set mine to run nightly so that the data is fresh daily for my administrators. You can also put a button on your sheet and call the function this way by linking the function, but this is another step for your end user.



For its base level API access, Blackbaud limits calls to 20,000 hits a day. Considering each GL call counts as a hit, it's wise to keep your triggers to a longer interval, especially if you expect to be polling a lot of APIs in your scripts.

Happy Coding!

Links:

Blackbaud SKY API: Home
apps-script-oauth2: An OAuth Library for Google Apps Script

Last Updated: December 17, 2024