Connecting BB
K-12 to Google
Account
BBDevDays
Bob Raymond – Independent Developer
robert.raymond@gmail.com
Why connect BB to Google Workspace?
Automate workflows, improve user access
Each hour, HOS interrupted bookkeeper to ask for an
enrollment report to be run showing the status of all
enrollment contracts
Enrollment season takes 6-9 months…lots of stragglers
HOS, Admission Director, and Principals not particularly
interested in logging into BB K-12 account and running
reports
Wanted to distribute information securely within the
school
Be able to quickly view enrollment contract status by
student and in sum – use a pivot table
Connecting data from Enrollment
Management to a Google Sheet
Advanced List
in BB
Enrollment
Management
SKY API
(Lists API)
Google Apps
Script
Hourly trigger
Google Sheet
with Pivot
Table
Why Google Workspace?
Schools already use Google Workspace – it’s part of their Google
Classroom account
Google Apps Script is a “rapid application development platform
that makes it fast and easy to create business applications that
integrate with Google Workspace.” www.google.com/script/start/
Javascript connected to Google Sheets
Google Apps Script/Workspace can:
Fetch required SKY API Endpoints
Store SKY API codes and keys securely within a Google Apps
Script
Email notifications on update
Security follows Google account (2FA, etc.)
+ there is no MS Power Platform for K-12
An Advanced List….
Reporting > Manage Lists > Advanced List
List Templates > Candidate/Student Contracts
Modify data fields and filters as needed
Save this list – the id is in the link address when you
hover:__pdL(‘888888','Advanced List: Enrollment Contracts 23-24',
'1', '~slid=139999~ml=False~sln=Enrollment Contracts 23-24', '',
'False', '0', '', 'default.aspx’) to use in your Google Script
Bob Raymond - [email protected]
5Bob Raymond - [email protected]
How to connect
Enrollment Management and Google
Workspace
1.
Library Script: Google Apps Script for SKY API data retrieval
This script becomes a library to be used by other Google Apps Scripts
Include Google Oauth2 library (github.com/googleworkspace/apps-script-oauth2)
This Script will stores/refresh keys required by SKY API securely
Authorize and generate call-back URL
Include call to List API
(developer.sky.blackbaud.com/docs/services/school/operations/V1ListsAdvancedByList
_idGet)
2.
Developer App: Add a new application to your SKY Developer account
Callback URL is generated from the Google Apps Script #3 above
Need admin privileges for the environment
3.
Sheet & Script: Create Google sheet with an attached script
Create a new script, with the script from #1 as a library
This script will pull the data from the SKY API and list it in a Google sheet
Format the pivot table using the data from that sheet
Create a trigger so this script auto updates
Sample code at github.com/bray-field/sky-api
7
Processing Available in
Google Apps Script
Lists can be filtered for different types of contracts
School uses multiple contracts per student – different sheets for
different contract types
Use different sheets (or different tabs) to monitor different contracts
Data can be modified within Google to make presentation easier
Example: Submitted/Processed status combined
Changes in enrollment can trigger email update to users notifying
them of any change and link to the sheet
Contract creation when student is accepted
When parent looks at Contract (but doesn’t sign it)
When a Contract processed
8Bob Raymond - [email protected]
Sample use cases
Any Advanced List can be accessed using the SKY API
Google Apps Script can be used to add functionality
Automated email notification based on filters
Data manipulation
Implemented for client schools:
Automated requirements analysis of transcripts
Problem: Registrar need to review student transcripts to ensure number of credits by department. School
requirements were more complicated than BB K-12 allows (hundreds of courses, changed regularly)
Solution: Advanced list download and analysis/grouping of data in Google Sheet
Official notes tracking
Problem: Summary and alerts when Official Notes were sent for a particular reason
Solution: Advanced list download, create Pivot Table by day and student
Attendance tracking & reminders
Problem: Teachers needed reminder to take attendance.
Solution: Advanced list showing absent students in spreadsheet, which teachers did not take attendance as of
9AM, email reminders to those teachers and second update at 11AM
Analyze Academic Schedule for next school year
Problem: School wanted to use BB Scheduler, but had complicated schedule with many special changes
Solution: Advanced list to extract course requests and fulfillment status to identify missing classes
There are more examples! Anything that can be captured in Advanced List can be exported
9
Use Cases
for other BB
Products
Linking the SKY API to Google
Workspace allows endpoint access to
RE NXT, FE NXT, and Payments API
Implemented Google Apps Scripts for
many purposes:
Automated uploading of RE Gifts from
Google Spreadsheet
Automated Payments API using BB
Checkout from Google Apps Script
Forms
Automated Journal Entry uploads
from Google Sheets
A lot more …
10Bob Raymond - [email protected]
Contact/Follow Up
Email
robert.raymond@gmail.com
Independent Consultant
available to work with
schools
Github – Sample Scripts
github.com/bray-field/sky-api
