WICKD Alley of Fame for VK campaign with priorities, graphs, daily updates

Code.gs

function encode_utf8( s ){
  return unescape( encodeURIComponent( s ) );
}

function get_csv() { 
  var url = 'http://www.kgmonteith.com/alleyoffame/alley.0.js';
  var response = UrlFetchApp.fetch(url);
  Logger.log( "RESPONSE " + response.getResponseCode());
  var data = encode_utf8(response.getContentText().toString());   
  return data // as text
}

function importFromCSV() {
  var rawData = get_csv(); // gets the data, makes it nice
  var csvData = eval(rawData); // turn into an array
  Logger.log("CSV ITEMS " + csvData.length);
  // filter the array, returning only rows that contain...
  // this requires ArrayLib v13 library: MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T
  var csvWICKD = ArrayLib.filterByText(csvData, 2, ["WICKD", "Clan"] );
  Logger.log("WICKD ITEMS " + csvWICKD.length);
  // insert timestamped sheet in the 5th position and use it
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var timestamp = Utilities.formatDate(new Date(), "EST", "MMM d ");
  var sheet = ss.insertSheet(timestamp,5);
  // iterate through array and write out data
  Logger.log(sheet);
  for (var i = 0; i < csvWICKD.length; i++) {
    sheet.getRange(i+1, 1, 1, csvWICKD[i].length).setValues(new Array(csvWICKD[i]));
  }  
  // hide redundant "Clan" and "Rank" columns
  var clan = sheet.getRange("C1");
  sheet.hideColumn(clan);
  var rank = sheet.getRange("B1");
  sheet.hideColumn(rank);
  // freeze first row headers
  sheet.setFrozenRows(1);
  // set priority colors
  priorityColors(sheet);  
  // protect the sheet
  var permissions = sheet.getSheetProtection();
  permissions.setProtected(true);
  sheet.setSheetProtection(permissions);
}

function MANUALpriorityColorsACTIVE() {
  // colorizes the ACTIVE spreadsheet -- WILL FAIL IF YOU'RE NOT ON FAMELIST
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); 
  priorityColors(sheet);
}

function priorityColors( sheet ) {
  // on "Priority" sheet, find names column (A2 to A-end)
  var prioritySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Priority');
  var priorityNames = prioritySheet.getRange(2,1, prioritySheet.getLastRow()-1, 1);
  var pn = priorityNames.getValues();
  // on "sheet" we're making, find names column (D2 to D-end, not hidden C)
  var fameNames = sheet.getRange(2,4, sheet.getLastRow()-1, 1);
  var fn = fameNames.getValues();
  // outer loop iterates through fameNames
  for (i=0; i < fn.length; i++) {
    Logger.log(fn[i][0]);
    // inner loop iterates through priorityNames
    for (j=0; j < pn.length; j++) {
      // when a match is found
      if (fn[i][0] == pn[j][0]) {
        // look up priority in column G of matching row
        var priorityRow = prioritySheet.getRange(j+2, 7, 1, 1);
        var priority = priorityRow.getValue();
        // ...and notes in column H
        var notesRow = prioritySheet.getRange(j+2, 8, 1, 1);
        var notes = notesRow.getValue();
        // color 5 colums of fameNames due to hidden C
        var fameRow = sheet.getRange(i+2, 1, 1, 5);
        // transfer notes as well
        var notice = sheet.getRange(i+2, 6, 1, 1);
        switch (priority) {
          case 0:
            fameRow.setBackground("#8b0000");
            break;
          case 1:
            fameRow.setBackground("#FF0000");
            break;
          case 2:
            fameRow.setBackground("#FF7F00");
            notice.setValue(notes);
            break;
          case 3:
            fameRow.setBackground("#FFFF00");
            notice.setValue(notes);
            break;
          case 4:
            fameRow.setBackground("#FFFF00");
            notice.setValue(notes);
            break;
          case 5:
            fameRow.setBackground("#00FF00");
            notice.setValue(notes);
            break;
          default:
            fameRow.setBackground("#000000");      
        }    
      }
    }
  }
}

function buildChartDataTable() {
  // pull in names from first column of sheet "Chart"
  var chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Chart Data');
  var chartNames = chartSheet.getRange(2,1, chartSheet.getLastRow()-1, 1);
  var cn = chartNames.getValues();
  // start inserting data into second column of chartSheet
  var chartColumn = 2
  // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  // outer loop cycles from oldest/last sheet down to index 5, which (should) select only fameSheets:
  // 0 - priority
  // 1 - chart data
  // 2 - chart data transposed
  // 3 - line chart
  // 4 - bar chart
  // 5 to end - fameSheets, interspersed hidden sheets?
  // this can be easily broken by hidden, moved and added/removed sheets changing the index
  // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  // TRY: select only Protected && Not-Hidden sheets
  // Otherwise, name-based filtering? Sheet contents analysis/identification?
  // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (i=allSheets.length-1; i > 4; i--) {
    // pull in names from fame sheet
    var fameSheet = allSheets[i];
    var fameNames = fameSheet.getRange(2,4, fameSheet.getLastRow()-1, 1);
    var fn = fameNames.getValues();
    // ignore hidden sheets
    if (!fameSheet.isSheetHidden()) {
      // put fameSheet name (date of the fame data) into data table column header
      var chartHeader = chartSheet.getRange(1, chartColumn, 1, 1);
      chartHeader.setValue(fameSheet.getName());
      // middle loop iterates through chartNames
      for (j=0; j < cn.length; j++) {
        // inner loop iterates through fameNames       
        for (k=0; k < fn.length; k++) {
          // when a match is found...
          if (fn[k][0] == cn[j][0]) {
            // read fame of fn from fameSheet
            var fameRow = fameSheet.getRange(k+2, 5, 1, 1);
            var fame = fameRow.getValue();
            // insert data into chart data table
            var chartRow = chartSheet.getRange(j+2, chartColumn, 1, 1);
            chartRow.setValue(fame);
          } // match
        } // fn++
      } // cn++
       // move to the next column of chartSheet before advancing fameSheet
       chartColumn++;
    } // if hidden
  } // fameSheets
  // sort data table descending by latest ranking
  Logger.log(chartColumn);
  var chartDataRange = chartSheet.getRange(2, 1, chartSheet.getLastRow()-1, chartColumn-1);
  chartDataRange.sort({column: chartColumn-1, ascending: false});
  // turns out the data table is the wrong way around
  // ehh, just transpose the data into a new sheet instead of re-working the function
  var chartData = chartSheet.getRange(1, 1, chartSheet.getLastRow()-1, chartColumn-1).getValues();
  // this requires ArrayLib v13 library: MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T
  var chartDataTransposed = ArrayLib.transpose(chartData);
  var chartSheetTransposed = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Chart Data Transposed");
  chartSheetTransposed.getRange(1, 1, chartDataTransposed.length, chartDataTransposed[0].length).setValues(chartDataTransposed);
  SpreadsheetApp.flush();  
} // function

function buildLineChart() {
  var chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Chart Data Transposed');
  var chartSheetRange = chartSheet.getRange(1, 1, chartSheet.getLastRow(), chartSheet.getLastColumn());
  var lineChart = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Line Chart');
  /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  var oldChart = chartSheet.getCharts();
  chartSheet.removeChart(oldChart[0]);
  .removeChart(chart);
  but you have to check that it exists, or re-work the chart definition below to put the options in-line with build();
  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  */
  var chart = lineChart.newChart()
  .setChartType(Charts.ChartType.LINE)
  .addRange(chartSheetRange)
  .setOption('width', 1000)
  .setOption('height', 700)
  .setOption('vAxis.viewWindowMode', 'maximized')
  .setOption('chartArea.left', 60)
  .setOption('chartArea.top', 10)
  .setOption('chartArea.width', 800)
  .setOption('chartArea.height', 660)
  .setPosition(2,1,0,0)
  .build();
  lineChart.insertChart(chart);
  Logger.log(chart.getOptions());
}