Generating & displaying a monthly shift roster

[Darren_Murphy]

Here we go…

Some of the styling is still a bit wonky, but that’s easy fixed.

• I only have the roster for a single month right now, so I actually created two tmp-Calendar columns, then used and ITE to check the status of the relation to the roster sheet. If it’s empty, show a blank calendar, otherwise show the selected workers calendar.
• This is what my new roster sheet looks like…

And in case you're interested, here is the code I wrote to generate the roster export...

function export_roster_to_app() {
  var today = get_now(); // new Date().toISOString().slice(0,10);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getRangeByName('AppRosterExport').getValues();
  var month_names = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
  var header = data.shift();
  var date = new Date(header[3]);
  var month = month_names[date.getMonth()];
  var year = date.getFullYear();

  var blank_days = date.getDay();
  data.shift(); data.shift(); // Discard two blank rows

  var class_names = get_class_names();
  var class_map = get_class_map();
  var roster = [];
  var div_start = '<div class="calendar__number ';
  var blank_div = '<div></div>';

  data.forEach(function (row) {
    var worker = [];
    var nric = row.shift();
    var name = row.shift();
    row.shift(); // blank column
    if (nric != '') {
      worker.push(nric, name, year, month, today);
      var day = 1;
      var code_string = '';
      code_string += blank_div.repeat(blank_days);
      while (row.length > 0) {
        var code = row.shift();
        if (code) {
          var display = class_map[code].display;
          var value = display == 'code' ? code : day;
          var code_class = class_map[code].class;
          code_string += div_start + class_names[code_class] + '">' + value + '</div>';
        }
        else {
          code_string += div_start + '">' + day + '</div>';
        }
        day++;
      }
      worker.push(code_string);
      roster.push(worker);
    }
  });
  
  write_roster_to_app(roster);
}

Thanks once again for the help and inspiration!