This Is amazing. Beautiful work Jeff !!!
2 Likes
YES! Glide take note…we need a monthly layout like This!!!
4 Likes
wow, thanks Jeff.
Going to have some fun with this, I think 
2 Likes
I managed to fix that by adding an IFE to deal with an initial empty state… don’t think that broke anything else, still working my way through the logic.
2 Likes
@Jeff_Hager this doesn’t look right?
Haven’t yet figured out what the impact of that is.
Edit: Looks like changing that to use WeekDay adds one day to the result.
Edit 2: hmm… another one that looks a bit dodgy…
Edit 3: Unfortunately my app is in a team folder, so wasn’t able to re-use your Glide table. Had to rebuild it from scratch. Managed to do that, and have it working in my app…
Now to figure out how to link it to my roster table…
3 Likes
So @Jeff_Hager, I’ve been studying this for most of today, fiddling and trying different things - and I keep bumping into the problem that my existing data isn’t very well structured.
Having the days of the month in columns is a real problem. After banging my head on my desk for a couple of hours, I think I’ve hit upon an idea. Would appreciate your thoughts…
As I mentioned in my OP, I do have control over the data. My existing “Roster” sheet is actually imported from another source. I wrote the import code for this, so I can do whatever I like with it. This is my current thinking:
- One row per worker, per month
- Each row has the following columns:
- Worker name
- Year
- Month
- Calendar
The final column (Calendar) will actually be the equivalent of the jl-DIVDays column in your example. It would include all the appropriate div/class definitions for that worker, for every day of that month. I would write code to generate that when I do the roster import.
With this, integrating into your Calendar layout becomes quite simple, I think…
- Add a USC to the MonthLayout table to hold a worker name from a Choice component
- Create an identical template column in each table that concatenates the worker name, year and month (eg,
Joe Blogs:2021:January) - Use those template columns to create a single relation between the two tables
- Drag the workers calendar through that relation into the MonthLayout table via a Lookup
- Use that calendar in your
tmp-Calendartemplate column, replacing the existing{ListDays}variable
A couple of potential gotchas that I can think of:
- I’ll need to take care when generating the individual calendars to ensure I get the first day of the month in the correct position. But I think I can handle that
- I’m not sure how responsive it will be as a user switches months in the calendar view - as the relation will be torn down and rebuilt with each tap. I think that should be okay, but it might be a case of suck it and see…
What do you think?
1 Like
-
Nice work on the IFE. I don’t know why I didn’t think of that. During the whole process of setting up the paging, I had discovered that for some reason, I could not make a math column work as the source of the increment. I was trying to add or subtract the calculated number of days to get to the first day of the previous or next month, but for some reason, neither increment, or set column would work for me when the source is a math column. Bugged the hell out of me and is most likely a bug. So instead, I changed the increments to increment by 1 or -1 and had to do some extra math with the “15th” method to get to the previous or next month. Because of that, there is a small bug, due to date drift, after paging through 30 some months where it will get stuck on 1 month for two taps of the next month and will skip a month when going backwards. Mostly unnoticeable, but still annoying. Being able to add months or years would make it much easier. I’ll have to see if I can rethink that one.
-
Looking at mth-WeekDay formula, I think it was honestly due to a lot of trial and error and probably changing the replacements to try different things. Changing the WeekDay replacement to use the weekday column does shift everything 1 day for January, but it also ends up putting the 1st day of the month on the same week day for all months, so the days don’t flow how they should. I changed my copy to use Date as the replacement parameter so it uses sv-FirstDay. I think it was working correctly, but just poor coding on my part.
-
Looking at mth-Day, it’s just extracting the day number so I can use it later in the DIV tags. Again, probably a lot of trial and error trying different things and an old replacement name stuck. I’ve fixed it and renamed DayPosition to Day. I also renamed mth-WeekDay to mth-Dates.
-
So to give a little context about what I was thinking to merge your data into the Month Layout…If you notice, there is the if-Highlight column. All it does right now is check if the date is today. If it is then it returns a value that fills the {highlight} replacement in the tmp-DIVDays columns. My thought is that you could add additional if statements to check the shift for that day and fill it with your shift names, such as (D, N, 1st, 2nd, etc.). That would automatically plug into the DIV tags and as long as you have the CSS set up, it would highlight with the correct color. The CSS has color coding for --current, --day, and --night, but you could change that as needed.
-
Your idea might work. I think the hardest part would be lining up the days, unless you move some the Month Layout logic over to your other tables to figure out the number of blank DIV tags at the beginning of the month. Otherwise, you could maybe do some other magic to get a joined list of only the prior month’s days that have a blank if-ThisMonthDay. The add that joined list to your {ListDays} joined list. Would you happen to have a screenshot of some sample data so I can get a better visual of you monthly tables? Then I can think on it for a while. I’m was hoping we could do some sort of simple plug and play into the Month Layout table with relations and lookups to fill the highlight column, but that might be difficult if everything is laid out in columns, when would mean that your way may be best.
2 Likes
yah, I eventually figured that one out. Have made a similar adjustment in my copy.
Got it 
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! 
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 
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 
4 Likes
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>';
}
day++;
}
worker.push(code_string);
roster.push(worker);
}
});
write_roster_to_app(roster);
}
Thanks once again for the help and inspiration! 
6 Likes
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! 
(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 )
4 Likes
Well, I guess zero based logic has some advantages here. 
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.
(Date-DAY(Date)+DayPosition)
WEEKDAY(Date)
+
1
Just create 31 numbered rows for the DayPosition column. Then just use:
(Date-DAY(Date)+DayPosition)
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