Skip to content

Instantly share code, notes, and snippets.

@niflostancu
Last active November 27, 2022 20:18
Show Gist options
  • Save niflostancu/acf5e44360a6833bfe00f09c63fe500d to your computer and use it in GitHub Desktop.
Save niflostancu/acf5e44360a6833bfe00f09c63fe500d to your computer and use it in GitHub Desktop.
Google Sheets Gradebook Tookit - contains various automation routines for gradebooks (as GApps Script).
/**
* Google Sheets Gradebook Tookit - contains various automation routines for gradebooks.
*
* For now, it only facilitates anonymization of the students using public sheets.
*
* How to use:
* - backup your document before ;)
* - create a new Apps Script and put this code in there;
* - fill in the CONFIG structure below;
* - run + authorize the script;
* - refresh the main document & TEST IT;
*
* OAuth scopes required:
* @OnlyCurrentDoc
*/
/**
* Configuration entries.
*/
const CONFIG = {
/** Prevents any actions from taking place while set. */
maintenance: false,
/** Global anonymization code generation config (@see generateUniqueCode). */
anonymizationCodes: {
type: 'numeric', length: 6,
/* type: 'wordlist', wordsSheet: '_wordlist', */
},
/** Per-sheet configuration objects. */
sheets: {
main: {
name: "Catalog",
startRow: 3,
anonymize: {
codeColumn: 4, /* note: 1-based! */
},
publicSheet: {
name: "Catalog Public",
/*
* The columns to copy from the parent sheet.
* The generated code must be the first one!
*/
copyHeaderRows: 2,
copyColumns: [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18],
protect: true,
}
}
},
};
/** On spreadsheet open event - add action menu */
function onOpen(ev) {
SpreadsheetApp.getUi().createMenu("Gradebook-X")
.addItem("Generate Anonymous Codes", "generateAnonymousCodes")
.addItem("Rebuild Public Sheets", "rebuildAllPublicSheets")
.addToUi();
}
/**
* Generates any missing anonymization codes for all configured sheets.
* Menu action.
*/
function generateAnonymousCodes() {
_maintenanceCheck();
/* First pass: fetch the current codes into per-sheet ranges + add them to a common Set() */
let sheetRanges = {};
let sheetCodes = {};
let allCodes = new Set();
Object.keys(CONFIG.sheets).forEach((key) => {
let sheetConfig = CONFIG.sheets[key];
if (!sheetConfig.anonymize) return;
let sheetObj = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetConfig.name);
sheetRanges[key] = sheetObj.getRange(sheetConfig.startRow, sheetConfig.anonymize.codeColumn,
sheetObj.getLastRow() - sheetConfig.startRow + 1, 1);
let data = sheetRanges[key].getValues();
/* convert the values from a 1xN matrix to a simple array */
data = data.map((val) => val[0]);
data.forEach((code) => { if (code) { allCodes.add(code) }; });
sheetCodes[key] = data;
});
/* Now, generate & store all the [missing] codes */
let numNewCodes = 0;
Object.keys(CONFIG.sheets).forEach((key) => {
let sheetConfig = CONFIG.sheets[key];
if (!sheetConfig.anonymize) return;
let values = sheetCodes[key].map((val) => {
if (val) return [val]; /* a value already exists (convert to 1-column array) */
let newCode = generateUniqueCode(allCodes);
allCodes.add(newCode);
numNewCodes++;
return [newCode];
});
sheetRanges[key].setValues(values);
});
SpreadsheetApp.getActive().toast("Done. New codes generated: " + numNewCodes, "👍 Finished 👍");
}
/**
* Rebuilds a public sheet (completely!).
*
* @param {Object} sheetConfig The sheet's configuration object.
*/
function rebuildPublicSheet(sheetConfig) {
_maintenanceCheck();
if (!sheetConfig.publicSheet) return;
/* 1. Initialize sheet (create if it doesn't exist + clear) */
let doc = SpreadsheetApp.getActiveSpreadsheet();
let sheetObj = doc.getSheetByName(sheetConfig.name);
let publicSheetObj = doc.getSheetByName(sheetConfig.publicSheet.name);
if (!publicSheetObj) {
publicSheetObj = doc.insertSheet();
publicSheetObj.setName(sheetConfig.publicSheet.name);
}
publicSheetObj.clear();
/* 2. Generate formulas */
let dstFormulas = [];
const maxSCol = Math.max.apply(null, sheetConfig.publicSheet.copyColumns);
const maxColChr = _columnToLetter(maxSCol);
const minColChr = _columnToLetter(sheetConfig.publicSheet.copyColumns[0]);
for (let i=0; i<sheetConfig.publicSheet.copyColumns.length; i++) {
const scol = sheetConfig.publicSheet.copyColumns[i];
const colChr = _columnToLetter(scol);
let columnFormulas = [];
const hRows = sheetConfig.publicSheet.copyHeaderRows;
for (let row=0; row < hRows; row++) {
/* header column, show cell values from their source sheet using formulas */
columnFormulas.push(`'${sheetConfig.name}'!\$${colChr}$${row+1}`);
}
if (i == 0) {
/* first column, append formula to extract all codes & sort them */
columnFormulas.push(_buildFormula('sort', [
_buildFormula("ARRAYFORMULA", [
`'${sheetConfig.name}'!$${colChr}$${hRows+1}:$${colChr}`
])
]));
} else {
/* formula to VLOOKUP source data for the current column (based on the first column) */
const vlIdx = scol - sheetConfig.publicSheet.copyColumns[0] + 1;
if (vlIdx < 2) {
throw new Error(`${sheetConfig.publicSheet.name}: column to copy ` +
`${colChr} is not allowed to be BEFORE the code column (${minColChr})`);
}
const publicColRef = `$A$${hRows+1}:$A`;
columnFormulas.push(
_buildFormula("ARRAYFORMULA", [
_buildFormula("IF", [publicColRef+'<>""',
_buildFormula("VLOOKUP", [
publicColRef, `'${sheetConfig.name}'!$${minColChr}$${hRows+1}:$${maxColChr}`,
`${vlIdx}`, 'FALSE'
]), '""'
])
])
);
}
/* merge all columns' cell formulas inside a big array formula */
dstFormulas.push(`{${columnFormulas.join("; ")}}`);
}
publicSheetObj.getRange(1, 1, 1, sheetConfig.publicSheet.copyColumns.length)
.setFormulas([dstFormulas]);
/* 3. Copy formatting */
for (let i=0; i<sheetConfig.publicSheet.copyColumns.length; i++) {
let scol = sheetConfig.publicSheet.copyColumns[i];
let srcColRange = sheetObj.getRange(1, scol, sheetObj.getMaxRows(), 1);
srcColRange.copyFormatToRange(publicSheetObj, i + 1, i + 1,
1, sheetObj.getMaxRows());
/* also copy column widths ;) */
publicSheetObj.setColumnWidth(i + 1, sheetObj.getColumnWidth(scol));
}
/* 4. Protect sheet? */
if (sheetConfig.publicSheet.protect) {
let protection = publicSheetObj.protect();
protection.setDescription("Read-Only Public Sheet");
protection.setWarningOnly(true);
}
}
/**
* Rebuilds all public sheets (menu action).
*/
function rebuildAllPublicSheets() {
_maintenanceCheck();
Object.keys(CONFIG.sheets).forEach((key) => {
rebuildPublicSheet(CONFIG.sheets[key]);
});
}
const ANONYM_CODE_GENERATORS = {
numeric: (cfg) => {
const maxDigits = Math.pow(10, cfg.length - 1);
return Math.floor(Math.random() * (maxDigits * 10 - maxDigits - 1) + maxDigits);
},
alpha: (cfg) => {
return _randomChars('abcdefghijklmnopqrstuvwxyz', cfg.length);
},
alphanum: (cfg) => {
return _randomChars('abcdefghijklmnopqrstuvwxyz0123456789', cfg.length);
},
/** Random word from a wordlist sheet. */
wordlist: (cfg) => {
let data = ANONYM_CODE_GENERATORS.wordlist._cache;
if (data === undefined) {
let sheetObj = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cfg.wordsSheet);
data = sheetObj.getDataRange().getValues();
ANONYM_CODE_GENERATORS.wordlist._cache = data;
}
return data[Math.floor(Math.random() * data.length)][0];
},
};
function _randomChars(charset, length) {
result = '';
for (let i=0; i<length; i++) {
result += charset.charAt(Math.floor(Math.random() * charset.length));
}
return result;
};
/**
* Generates a randomly unique code (checks with the provideed Set() before returning).
*/
function generateUniqueCode(usedCodeSet) {
const cfg = CONFIG.anonymizationCodes;
if (!ANONYM_CODE_GENERATORS[cfg.type])
throw new Error(`Invalid anonymization code generator: ${cfg.type}`);
let code;
let tries = 1000;
do {
code = ANONYM_CODE_GENERATORS[cfg.type](cfg);
tries--;
if (tries <= 0) {
throw new Error("Unable to generate a unique code in 1000 tries (insufficient entropy)!");
}
} while (usedCodeSet.has(code));
return code;
}
function _maintenanceCheck() {
if (CONFIG.maintenance) {
throw new Error("Maintenance mode is enabled!");
};
}
/**
* Functional for building a formula.
* @param {string} name The formula function's name.
* @param {string[]} args Formula's arguments.
*/
function _buildFormula(name, args) {
/* formula separator (Note: change if your locale using other char, e.g. ';' for Europe) */
const FSEP = ', ';
return name + "(" + args.join(FSEP) + ")";
}
function _columnToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
/**
* THE END!
*
* Fscking Google's Apps Scripts editor shows annoying toasts,
* so keep some distance from the bottom of the editor.
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment