Integrating Blackbaud with Google Workspace for Seamless Data Flow

Popular among non-profits, Blackbaud can sometimes become a frustrating data silo

Before you review this script,

You will need an advanced list in Blackbaud and you will need the ID number for the list. Further, you will need to have setup the API access and user. I will cover this in a paid post in the future.

Script Setup

I am using Google Apps Script(JavaScript). You can do this with Python or Node.

If you are new to Apps Script, you need to know how to open a script on a Google Sheet. Here are some posts about Apps Script. You can easily look this up online too. It is well documented.

To make this easier, I am showing some of the parameters you need in plain text. You should store these in script properties that are not visible.

Step 1: Create a spreadsheet, and name the tab.

In this case I am pulling a list of missing grades, so my tab name is “MissingGrades”.

Step 2: Open a new script on the spreadsheet, give it a name, and paste the code in.

I have intentionally hardcoded some values in, so you can see when things are referenced. Ideally, you want to set sheet info in an immutable variable and use it.

function clearDataFromSheet(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (sheet !== null) {
    sheet.clear();
  } else {
    Logger.log("Sheet '" + sheetName + "' not found.");
  }
}

function fetchMissingGradesFromBlackbaud() {
  // Clear the sheet insert target sheet name
  clearDataFromSheet("MissingGrades");


//The password and username are actualy numeric and you get them when you setup the BB api.

  const username = myUname();//Store in a script property 

// You can paste your info in here for testing

  const password = myPass();//Store in a script property 

// You can paste your info in here for testing

  var thesheet = "MissingGrades";

  // Add your Blackbaud base domain here and the advanced list ID
  var school_name = "name in your myschool app";

//Example of school_name, NAME.myschoolapp.com/app#login

  var list_id = "166883";

  // Log in to the portal
  var login_url = "https://" + school_name + ".myschoolapp.com/api/authentication/login";
  var login_data = {
    "username": username,
    "password": password
  };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(login_data)
  };

  var response = UrlFetchApp.fetch(login_url, options);

  if (response.getResponseCode() === 200) {
    var responseJson = JSON.parse(response.getContentText());

    // Get the authentication token
    var token = responseJson.Token;

    // Fetch the specified list
    var list_url = "https://" + school_name + ".myschoolapp.com/api/list/" + list_id;
    var params = {
      "t": token,
      "format": "json"
    };

    var listOptions = {
      "method": "get"
    };

    list_url += '?' + Object.keys(params).map(function(key) {
      return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]);
    }).join('&');

    var listResponse = UrlFetchApp.fetch(list_url, listOptions);
    var listData = JSON.parse(listResponse.getContentText());

    // Target the "StudentData" sheet for writing this data
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MissingGrades");
    if (sheet === null) {
      // Create the sheet if it doesn't exist
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("MissingGrades");
    }

    // If the listData is not empty, append headers
    if (listData.length > 0) {
      var headers = Object.keys(listData[0]);
      var dataToWrite = [headers]; // Initialize the data array with headers
      for (var i = 0; i < listData.length; i++) {
        var row = [];
        for (var key in listData[i]) {
          row.push(listData[i][key]);
        }
        dataToWrite.push(row); // Add each row to the data array
      }
      // Push the entire data array to the sheet in one operation
      sheet.getRange(1, 1, dataToWrite.length, headers.length).setValues(dataToWrite);
    }
  } else {
    Logger.log("Error: Could not log in. Check your credentials and try again.");
  }


  // Get the range for the entire sheet
  var entireSheetRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());

  // Set vertical and horizontal alignment for the entire sheet
  entireSheetRange.setVerticalAlignment('top');
  entireSheetRange.setHorizontalAlignment('left');

}

Step 3: Run, fetchMissingGradesFromBlackbaud()

When this script runs, it will auto populate the headers defined in the Blackbaud Advanced List.

Step 4: If you are happy, setup a trigger to run this daily, weekly, etc.

What’s Next?

I like to connect this to BI tools like Looker Studio or Power BI. You can keep the sheet away from editors.

If you want to share the sheet, I would recommend adding another function that copy’s the sheet to another one so the orginal is never accessed.