Seth Battis battis

DRAFT: Exporting LMS data from Blackbaud to Canvas

Basic Workflow

Capture LMS data snapshot(s) using msar snapshot
Archive linked files from snapshot using msar archive
Create historic faculty using SIS Users Import
1. Use a spreadsheet to generate appropriate sis_user_id values (e.g. usr-123-12345678)
2. Create a non-conflicting login email (e.g. usr-123-12345678@example.myschoolapp.com) and assign it to both the login_id (for Canvas) and email (for Canvas Studio)
Designate an admin account as the import user (ideally not a person, but a generic IT or Academics account) and assign that account the sis_user_id of @msar/canvas-import (this can be changed after the import is complete)

Publish a Blackbaud SKY API app

Create an account on the SKY API developer portal if you don't already have one. This will need to be provisioned through your school.
Create a new application under your account in My applications
While you don't have to have your app deployed yet, you will need to know where it will be deployed, as you need a few URLs:

The URL to the app itself
A redirect URI for OAuth 2.0 authentication (use a PHP or TypeScript library for this!)

Make a note of the Application ID (OAuth client_id) and Primary application secret (OAuth client_secret) (Blackbaud recommends rotating your secrets every 90 days, and provides a primary and secondary to facilitate doing this without a break in service).
Make a note of your subscription key under [My subscriptions](h

Publish a Google Workspace Add-on

Based on Google documentation

I have attached my draft version of a setup script for these projects (including my frequent need to connect to the Blackbaud SKY API), but not being able to script a number of key publishing steps makes it not really worthwhile. This assumes that you already have node installed on your computer to build the project.

In the browser

Create a new Google Cloud project in the console.
Under APIs & Services, configure OAuth Consent. I usually publish with Internal users (no need for Google review). Add the OAuth Scopes listed in the projects appscripts.json file to the OAuth Consent.
Enable the Google Workspace Markplace SDK API.

Usage: mkdummy [-f files] [-n bytes] [-x bytes] [-s bytes] [-d depth] [-r [0..99)] [path]
  -d: [D]epth of recursive folder nesting
  -f: maximum number of [F]iles
  -n: mi[N]imum file size (in bytes)
  -r: probabilty of [R]ecursive folder nesting [0..100), default 10
  -s: approximate amount of disk[S]pace to consume (in bytes)
  -x: ma[X]imum file size (in bytes)

	import { parse } from 'csv-parse/sync';
	import { convert } from 'html-to-text';
	import { stringify } from 'csv-stringify/sync';
	import fs from 'node:fs';

	const comments = parse(
	fs.readFileSync('/Users/sbattis/Downloads/comments.csv'),
	{
	columns: true,
	}

	/*
	* Parse the list of learning profiles under
	* Academics > Grades > Grades Management > Manage Learning Profiles
	*
	* The output is essentially a TSV.
	*
	* There is a known bug in Blackbaud were past students' (not alums) names are
	* not shown (Case 019825709). The names have to be deduced by actually viewing
	* the learning profile. All of these names are at the start of the list.
	*/

	/*
	* Seth Battis
	* Google Apps Script
	* No external dependencies
	*/

	/**
	* @param fileId: string - Google Sheets document ID
	* @param tileIndex: number - index of "combined revisions" shown in versions UI
	*/

	/*
	* Seth Battis
	* Google Apps Script
	* No external dependencies
	*/
	function fileHistory(fileId) {
	const response = JSON.parse(UrlFetchApp.fetch(`https://www.googleapis.com/drive/v3/files/${fileId}/revisions`, {
	headers: {
	Authorization: `Bearer ${ScriptApp.getOAuthToken()}`
	}

	import g from '@battis/gas-lighter';

	const colors = {
	'Pale Blue': '1',
	'Pale Green': '2',
	Mauve: '3',
	'Pale Red': '4',
	Yellow: '5',
	Orange: '6',
	Cyan: '7',

	<?php

	use Google\Cloud\SecretManager\V1\SecretManagerServiceClient;

	class Secrets {
	private static ?SecretManagerServiceClient $client = null;

	private function __construct()
	{}