Google Sheets Gradebook Tookit - contains various automation routines for gradebooks (as GApps Script).

gdocs_gradebook_helper.js

/**
 * 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.
 */