Color-code plan edits?

[battis]

kleggat suggests color-coding course plan edits based on the party doing it (college counseling, academics office, advisor, student)

This could probably be accomplished by running a scheduled task that updates edited course plan formatting periodically.

[battis]

Took a look at this, and the process of getting a Google Sheet's revision and processing it is ungodly onerous. The workflow, as described on StackOverflow, is to query the Google Drive API for the revisions list, then the Google Sheets API to download an Excel file of a particular revision, then re-upload that excel file to Google Sheets to analyze the changes manually.

I haven't yet come up with a better option on my own. And the revision metadata (weirdly) doesn't seem to include user attribution. WTF.

[battis]

Sidebar, this relates to #60 if it ends up getting scheduled.

[battis]

Looks like I can get user-attributed revisions after all

Just need to parse some HTML

https://gist.github.com/battis/2f24081dd024cd743a752dd21e84722b

[battis]

Proof of concept for live tracking of edits

function onOpen(e) {
  const users = SpreadsheetApp.getActiveSheet().getRange('A:A');
  const data = users.getValues();
  let found = false;
  let color;
  for (let row = 0; row < data.length && !found; row++) {
    if (data[row][0] == e.user.getEmail()) {
      color = users.offset(row, 0, 1, 1).getBackground();
      found = true
    } else if (data[row][0] == '') {
      color = rainbow(data.length, row);
      users.offset(row,0,1,1).setValue(e.user.getEmail()).setBackground(color);
      found = true;
    }
  }
  if (color) {
    PropertiesService.getUserProperties().setProperty('color', color);
  }
}

function onEdit(e) {
  let color = PropertiesService.getUserProperties().getProperty('color');
  e.range.setBackground(color);
 }

/**
 * @param numOfSteps: Total number steps to get color, means total colors
 * @param step: The step number, means the order of the color
 * @see https://stackoverflow.com/a/7419630/294171
 */
function rainbow(numOfSteps, step) {
    // This function generates vibrant, "evenly spaced" colours (i.e. no clustering). This is ideal for creating easily distinguishable vibrant markers in Google Maps and other apps.
    // Adam Cole, 2011-Sept-14
    // HSV to RBG adapted from: http://mjijackson.com/2008/02/rgb-to-hsl-and-rgb-to-hsv-color-model-conversion-algorithms-in-javascript
    var r, g, b;
    var h = step / numOfSteps;
    var i = ~~(h * 6);
    var f = h * 6 - i;
    var q = 1 - f;
    switch(i % 6){
        case 0: r = 1; g = f; b = 0; break;
        case 1: r = q; g = 1; b = 0; break;
        case 2: r = 0; g = 1; b = f; break;
        case 3: r = 0; g = q; b = 1; break;
        case 4: r = f; g = 0; b = 1; break;
        case 5: r = 1; g = 0; b = q; break;
    }
    var c = "#" + ("00" + (~ ~(r * 255)).toString(16)).slice(-2) + ("00" + (~ ~(g * 255)).toString(16)).slice(-2) + ("00" + (~ ~(b * 255)).toString(16)).slice(-2);
    return (c);
}