Generating & displaying a monthly shift roster

yah, I eventually figured that one out. Have made a similar adjustment in my copy.

Got it :+1: Have made similar adjustments in my copy.

Yeah, I picked up on that. Problem is I have over 30 distinct codes to deal with, and now an additional requirement where my customer has decided that for some codes he wants the actual code displayed in place of the day number. Ugh! :crazy_face:

Yup, I’ve already built out the CSS I’ll need for all codes.

I reckon it will, and I’ve already made a start on the code. As I’ll be generating the {ListDays} array programatically, I’ll just insert the appropriate number of blank divs as I do that. The initial thought of generating this programatically was a bit daunting, but now that I’ve made a start, I can see that it’ll actually be pretty straightforward. For example, I started by defining a couple of mapping tables…

function get_class_names() {
  var class_names = {
    day: 'calendar__number--day',
    night: 'calendar__number--night',
    first: 'calendar__number--first',
    second: 'calendar__number--second',
    third: 'calendar__number--third',
    twoa: 'calendar__number--twoa',
    twob: 'calendar__number--twob',
    ns: 'calendar__number--ns',
    off: 'calendar__number--off',
    training: 'calendar__number--training',
    standby: 'calendar__number--standby',
    leave: 'calendar__number--leave',
    missing: 'calendar__number--missing'
  return class_names;

function get_class_map() {
  var class_map = {
    '1ST': { class: 'day', display: 'day' },
    '2A': { class: 'twoa', display: 'day' },
    '2ND': { class: 'second', display: 'day' },
    'ATI D': { class: 'day', display: 'code' },
    'D': { class: 'day', display: 'day' },
    'ITE': { class: 'day', display: 'code' },
    'OJT': { class: 'training', display: 'code' },
    'OJT D': { class: 'training', display: 'code' },
    'SRT': { class: 'training', display: 'code' },
    '2B': { class: 'twob', display: 'day' },
    '3RD': { class: 'third', display: 'day' },
    'ATI N': { class: 'night', display: 'code' },
    'N': { class: 'night', display: 'day' },
    'OJT N': { class: 'night', display: 'code' },
    'ABS': { class: 'missing', display: 'code' },
    'AL': { class: 'leave', display: 'code' },
    'CO': { class: 'training', display: 'code' },
    'MC': { class: 'leave', display: 'code' },
    'MIA': { class: 'missing', display: 'code' },
    'NS': { class: 'ns', display: 'day' },
    'OFF': { class: 'off', display: 'day' },
    'RS': { class: 'missing', display: 'code' },
    'SB': { class: 'standby', display: 'day' },
    'SUS': { class: 'missing', display: 'code' },
    'UL': { class: 'leave', display: 'code' },
    'DAFM': { class: 'day', display: 'code' },
    'NAFM': { class: 'night', display: 'code' },
    'DPM': { class: 'day', display: 'code' },
    'NPM': { class: 'night', display: 'code' },
    'DFM': { class: 'day', display: 'code' },
    'NFM': { class: 'night', display: 'code' },
  return class_map;

With those, all I need to do is iterate through the roster, and create an array of arrays, plugging in the appropriate values as I go :grin:

Here is a snapshot of what the source data looks like (this lives in a separate Google Worksheet, and is managed on a day to day basis by the customer):

And here is what my current export looks like when it arrives in my app Google Sheet:

But, assuming that my idea works out (I should know in a few hours), then that will change and all those day of month columns will be gone.

I’ll report back a bit later tonight :slightly_smiling_face:


Ahh, I see. So the script will replace the need for separate day columns with a single combined div tag string? Looks daunting, but I’m excited to see if it works out for you. Good luck!

1 Like

That’s the plan!

1 Like

Great!..Jeff you have covered so much in one little app…Magnificent ! I don’t need my google sheet formula now! I know what I am doing now…I only needed the last three columns you created in the editor,…Brilliant!

Thank you!!!

1 Like

One massive sheet! amazing!

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>';

Thanks once again for the help and inspiration! :+1: :slightly_smiling_face:


Beautiful! That’s turning out awesome!

At some point, I’ll try to see if I can come up with a better method for calculating the next and previous months. The issue at the 30 or so month mark from today bugs the hell out of me.

For code blocks, it should work to write 4 backticks ```` with your code below that, followed by 4 more backticks.

Incidentally, getting the first day of the month positioned correctly turned out to be remarkably simple.
I already had the date of the first of the month from the source sheet, so all I had to do was convert that to a date object, and call getDay() on it. getDay() returns an integer representing the day of the week. And because it conveniently starts at Sunday = 0, whatever number it returned was precisely how many empty divs I needed at the start of the roster string.

So, as I started building the roster for each worker, it was just a matter of:

var code_string = '';
code_string += blank_div.repeat(blank_days);

oooh, nicely formatted code block with syntax highlighting. That’s one more I owe you! :rofl: :rofl:

(I’m going to have to go back and reformat every code snippet I’ve ever posted now. That’ll keep me busy for a while :rofl:)


Well, I guess zero based logic has some advantages here. :wink: I just hate when some languages use zero based and some are one based…or they mix both.

1 Like

@Jeff_Hager I don’t want the weekdays, I just want the calendar days (you know for temp store CSS means rejection): The idea is just display 31 boxes. I can get rid of one sheet tab altogether.

I know you have 37 day positions to get the weekdays display right, but I just want max 31 days.

What would need to change in this formula, pls, when you have time.



Just create 31 numbered rows for the DayPosition column. Then just use:


1 Like

Thank you!

I deleted the weekdays column and got it to work…but sure was gonna come back to haunt me.

I will apply this.

1 Like

OMG! @Jeff_Hager Just finished combing through the List Layout. This is gold mine…I don’t how to thank you…but on a different level…I know what I am doing with this stuff…credit to @Darren_Murphy for jostling you into it. Next level stuff,now need to update my staff scheduler…I might just copy/paste everything…!

1 Like

Thank you! I think that was my attempt at a staff scheduler a while back, which ended up becoming a calendar with half a$$ed multi day capabilities. Now that it’s resurfaced, I might have to throw some new ideas at it. I’d like to merge it somehow with the month layout.


Lovely stuff and :joy: @ a$$ed. I’m so exited I don’t know where to start with this. I’ve few concepts I can bring to life now.

I’m going to merge it with the 31 day calendar. I know that I can do but weekday one I could too. Let me get the 31 out of the way. I’ll show you the result soon. Then I’m gonna work on the weekday layout. It’s the CSS. I have to avoid it. I was so glued to Roberts css at one point I’d forgotten about the template store rules. Then reality knocked. But just for the fun of it I’m going to try.


I’m really pleased with the way this has turned out. I still have a bit of work to make it look a bit more presentable, but I think it’s a really solid solution that should be relatively low maintenance.

Kudos again to Jeff. As we say back home in Oz, the guy is a fair dinkum legend!



1 Like

Well, I can definitely say that’s the first time I’ve been called that. :slight_smile: :smile:


@Jeff_Hager - I think you’ll like this…

Was going backwards and forwards with the customer today - kept changing their minds about shift code colours. This started doing my head in, so eventually I did this:

  • Gave the customer a sheet that looks like this:

  • And told him to go to town on it :grin:
  • While he was busy doing that, I wrote this little code snippet…
function build_css_classes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Shift Code Map');
  var range = ss.getRangeByName('ShiftCodes');
  var classes = [];
  var row = 1;
  var last_row = range.getLastRow();
  while (row < last_row) {
    var text_color = range.getCell(row,4).getFontColor();
    var bg_color = range.getCell(row,4).getBackground();
    var class_name = range.getCell(row,5).getValue();
    var this_class = '.' + class_name + ' { ' +
      'border-radius:25px; ' +
      'background-color: ' + bg_color + '; ' +
      'color: ' + text_color + ' !important; ' +

      'font-weight: 700; ' +
      'cursor: pointer; }';

… you can probably see where this is going :wink:

Here’s a couple of screen grabs of the current iteration:


Yeah, there you go! Makes your life easier.