b6TuZ3b6

1// Compiled using ts2gas 3.4.4 (TypeScript 3.5.3)
2var exports = exports || {};
3var module = module || { exports: exports };
4/*
5
6GLOBAL SETTINGS
7
8*/
9var __extends = (this && this.__extends) || (function () {
10    var extendStatics = function (d, b) {
11        extendStatics = Object.setPrototypeOf ||
12            ({ __proto__: [] } instanceof Array && function (d, b) { d.__proto__ = b; }) ||
13            function (d, b) { for (var p in b) if (b.hasOwnProperty(p)) d[p] = b[p]; };
14        return extendStatics(d, b);
15    };
16    return function (d, b) {
17        extendStatics(d, b);
18        function __() { this.constructor = d; }
19        d.prototype = b === null ? Object.create(b) : (__.prototype = b.prototype, new __());
20    };
21})();
22var __assign = (this && this.__assign) || function () {
23    __assign = Object.assign || function(t) {
24        for (var s, i = 1, n = arguments.length; i < n; i++) {
25            s = arguments[i];
26            for (var p in s) if (Object.prototype.hasOwnProperty.call(s, p))
27                t[p] = s[p];
28        }
29        return t;
30    };
31    return __assign.apply(this, arguments);
32};
33var ARC_APP_DEBUG_MODE = true;
34var AskStatus;
35(function (AskStatus) {
36    AskStatus["LOADING"] = "LOADING";
37    AskStatus["LOADED"] = "LOADED";
38    AskStatus["ERROR"] = "ERROR";
39})(AskStatus || (AskStatus = {}));
40/*
41
42UTILITIES
43
44*/
45function roundDownToDay(utcTime) {
46    var timeInThisTimezone = utcTime - 4 * 3600 * 1000;
47    return (Math.floor(timeInThisTimezone / 1000 / 86400) * 86400 * 1000 +
48        4 * 3600 * 1000);
49}
50function onlyKeepUnique(arr) {
51    var x = {};
52    for (var i = 0; i < arr.length; ++i) {
53        x[JSON.stringify(arr[i])] = arr[i];
54    }
55    var result = [];
56    for (var _i = 0, _a = Object.getOwnPropertyNames(x); _i < _a.length; _i++) {
57        var key = _a[_i];
58        result.push(x[key]);
59    }
60    return result;
61}
62// polyfill of the typical Object.values()
63function Object_values(o) {
64    var result = [];
65    for (var _i = 0, _a = Object.getOwnPropertyNames(o); _i < _a.length; _i++) {
66        var i = _a[_i];
67        result.push(o[i]);
68    }
69    return result;
70}
71function recordCollectionToArray(r) {
72    var x = [];
73    for (var _i = 0, _a = Object.getOwnPropertyNames(r); _i < _a.length; _i++) {
74        var i = _a[_i];
75        x.push(r[i]);
76    }
77    return x;
78}
79// This function converts mod numbers (ie. 11) into A-B-day strings (ie. 1B).
80function stringifyMod(mod) {
81    if (1 <= mod && mod <= 10) {
82        return String(mod) + 'A';
83    }
84    else if (11 <= mod && mod <= 20) {
85        return String(mod - 10) + 'B';
86    }
87    throw new Error("mod " + mod + " isn't serializable");
88}
89function stringifyError(error) {
90    if (error instanceof Error) {
91        return JSON.stringify(error, Object.getOwnPropertyNames(error));
92    }
93    try {
94        return JSON.stringify(error);
95    }
96    catch (unusedError) {
97        return String(error);
98    }
99}
100var Field = /** @class */ (function () {
101    function Field(name) {
102        if (name === void 0) { name = null; }
103        this.name = name;
104    }
105    return Field;
106}());
107var BooleanField = /** @class */ (function (_super) {
108    __extends(BooleanField, _super);
109    function BooleanField(name) {
110        if (name === void 0) { name = null; }
111        return _super.call(this, name) || this;
112    }
113    BooleanField.prototype.parse = function (x) {
114        return x === 'true' ? 'true' : 'false';
115    };
116    BooleanField.prototype.serialize = function (x) {
117        return x ? 'true' : 'false';
118    };
119    return BooleanField;
120}(Field));
121var NumberField = /** @class */ (function (_super) {
122    __extends(NumberField, _super);
123    function NumberField(name) {
124        if (name === void 0) { name = null; }
125        return _super.call(this, name) || this;
126    }
127    NumberField.prototype.parse = function (x) {
128        return Number(x);
129    };
130    NumberField.prototype.serialize = function (x) {
131        return Number(x);
132    };
133    return NumberField;
134}(Field));
135var StringField = /** @class */ (function (_super) {
136    __extends(StringField, _super);
137    function StringField(name) {
138        if (name === void 0) { name = null; }
139        return _super.call(this, name) || this;
140    }
141    StringField.prototype.parse = function (x) {
142        return String(x);
143    };
144    StringField.prototype.serialize = function (x) {
145        return String(x);
146    };
147    return StringField;
148}(Field));
149// Dates are treated as numbers.
150var DateField = /** @class */ (function (_super) {
151    __extends(DateField, _super);
152    function DateField(name) {
153        if (name === void 0) { name = null; }
154        return _super.call(this, name) || this;
155    }
156    DateField.prototype.parse = function (x) {
157        if (x === '' || x === -1) {
158            return -1;
159        }
160        else {
161            return Number(x);
162        }
163    };
164    DateField.prototype.serialize = function (x) {
165        if (x === -1 || x === '') {
166            return '';
167        }
168        else {
169            return new Date(x);
170        }
171    };
172    return DateField;
173}(Field));
174var JsonField = /** @class */ (function (_super) {
175    __extends(JsonField, _super);
176    function JsonField(name) {
177        if (name === void 0) { name = null; }
178        return _super.call(this, name) || this;
179    }
180    JsonField.prototype.parse = function (x) {
181        return JSON.parse(x);
182    };
183    JsonField.prototype.serialize = function (x) {
184        return JSON.stringify(x);
185    };
186    return JsonField;
187}(Field));
188/*
189
190TABLE CLASS
191
192*/
193var Table = /** @class */ (function () {
194    function Table(name) {
195        this.idCounter = new Date().getTime();
196        this.name = name;
197        this.tableInfo = Table.tableInfoAll[name];
198        // Forms have important limitations in the app. We want to protect form data at all costs.
199        this.isForm = !!this.tableInfo.isForm;
200        if (this.tableInfo === undefined) {
201            throw new Error("table " + name + " not found in tableInfoAll");
202        }
203        this.rebuildSheetIfNeeded();
204    }
205    Table.makeBasicStudentConfig = function () {
206        return [
207            new StringField('friendlyFullName'),
208            new StringField('friendlyName'),
209            new StringField('firstName'),
210            new StringField('lastName'),
211            new NumberField('grade'),
212            new NumberField('studentId'),
213            new StringField('email'),
214            new StringField('phone'),
215            new StringField('contactPref'),
216            new StringField('homeroom'),
217            new StringField('homeroomTeacher'),
218            new StringField('attendanceAnnotation')
219        ];
220    };
221    Table.prototype.rebuildSheetIfNeeded = function () {
222        this.sheet = SpreadsheetApp.getActive().getSheetByName(this.tableInfo.sheetName);
223        if (this.sheet === null) {
224            if (this.isForm) {
225                throw new Error("table " + this.name + " not found, and it's supposed to be a form");
226            }
227            else {
228                this.sheet = SpreadsheetApp.getActive().insertSheet(this.tableInfo.sheetName);
229                this.rebuildSheetHeadersIfNeeded();
230            }
231        }
232        else {
233            this.sheetLastColumn = this.sheet.getLastColumn();
234        }
235    };
236    Table.prototype.rebuildSheetHeadersIfNeeded = function () {
237        var col = this.sheet.getLastColumn();
238        if (!this.isForm) {
239            this.sheet.getRange(1, 1, 1, col === 0 ? 1 : col).clearContent();
240            this.sheet
241                .getRange(1, 1, 1, this.tableInfo.fields.length)
242                .setValues([this.tableInfo.fields.map(function (field) { return field.name; })]);
243        }
244        this.sheetLastColumn = col;
245    };
246    Table.prototype.resetEntireSheet = function () {
247        if (!this.isForm) {
248            this.sheet.getDataRange().clearContent();
249        }
250        this.rebuildSheetHeadersIfNeeded();
251    };
252    // This is useful for debug. It rewrites each cell with the content that the app *thinks* is inside the cell.
253    Table.prototype.rewriteEntireSheet = function () {
254        if (!this.isForm) {
255            this.updateAllRecords(Object_values(this.retrieveAllRecords()));
256        }
257        this.rebuildSheetHeadersIfNeeded();
258    };
259    Table.prototype.retrieveAllRecords = function () {
260        if (ARC_APP_DEBUG_MODE) {
261            this.rebuildSheetHeadersIfNeeded();
262        }
263        if (this.sheetLastColumn !== this.tableInfo.fields.length) {
264            throw new Error("something's wrong with the columns of table " + this.name + " (" + this.tableInfo.fields.length + ")");
265        }
266        var raw = this.sheet.getDataRange().getValues();
267        var res = {};
268        for (var i = 1; i < raw.length; ++i) {
269            var rec = this.parseRecord(raw[i]);
270            res[String(rec.id)] = rec;
271        }
272        return res;
273    };
274    Table.prototype.parseRecord = function (raw) {
275        var rec = {};
276        for (var i = 0; i < this.tableInfo.fields.length; ++i) {
277            var field = this.tableInfo.fields[i];
278            // this accounts for blanks in the last field
279            rec[field.name] = field.parse(raw[i] === undefined ? '' : raw[i]);
280        }
281        if (this.isForm) {
282            // forms don't have an id field, so copy the date into the id
283            rec.id = rec.date;
284        }
285        return rec;
286    };
287    // Turn a JS object into an array of raw cell data, using each field object's built-in serializer.
288    Table.prototype.serializeRecord = function (record) {
289        return this.tableInfo.fields.map(function (field) {
290            return field.serialize(record[field.name]);
291        });
292    };
293    Table.prototype.createRecord = function (record) {
294        if (record.date === -1) {
295            record.date = Date.now();
296        }
297        // REMEMBER: forms don't have id fields!
298        if (!this.isForm && record.id === -1) {
299            record.id = this.createNewId();
300        }
301        this.sheet.appendRow(this.serializeRecord(record));
302        return record;
303    };
304    Table.prototype.getRowById = function (id) {
305        if (this.isForm) {
306            throw new Error('getRowById not supported for forms');
307        }
308        // because the first row is headers, we ignore it and start from the second row
309        var mat = this.sheet
310            .getRange(2, 1, this.sheet.getLastRow() - 1)
311            .getValues();
312        var rowNum = -1;
313        for (var i = 0; i < mat.length; ++i) {
314            var cell = mat[i][0];
315            if (typeof cell !== 'number') {
316                throw new Error("id at location " + String(i) + " is not a number in table " + String(this.name));
317            }
318            if (cell === id) {
319                if (rowNum !== -1) {
320                    throw new Error("duplicate ID " + String(id) + " in table " + String(this.name));
321                }
322                rowNum = i + 2; // i = 0 <=> second row (rows are 1-indexed)
323            }
324        }
325        if (rowNum == -1) {
326            throw new Error("ID " + String(id) + " not found in table " + String(this.name));
327        }
328        return rowNum;
329    };
330    Table.prototype.updateRecord = function (editedRecord, rowNum) {
331        if (rowNum === undefined) {
332            rowNum = this.getRowById(editedRecord.id);
333        }
334        this.sheet
335            .getRange(rowNum, 1, 1, this.sheetLastColumn)
336            .setValues([this.serializeRecord(editedRecord)]);
337    };
338    Table.prototype.updateAllRecords = function (editedRecords) {
339        if (this.sheet.getLastRow() === 1) {
340            return; // the sheet is empty, and trying to select it will result in an error
341        }
342        // because the first row is headers, we ignore it and start from the second row
343        var mat = this.sheet
344            .getRange(2, 1, this.sheet.getLastRow() - 1)
345            .getValues();
346        var idRowMap = {};
347        for (var i = 0; i < mat.length; ++i) {
348            idRowMap[String(mat[i][0])] = i + 2; // i = 0 <=> second row (rows are 1-indexed)
349        }
350        for (var _i = 0, editedRecords_1 = editedRecords; _i < editedRecords_1.length; _i++) {
351            var r = editedRecords_1[_i];
352            this.updateRecord(r, idRowMap[String(r.id)]);
353        }
354    };
355    Table.prototype.deleteRecord = function (id) {
356        if (this.isForm) {
357            throw new Error('cannot delete records from a form');
358        }
359        this.sheet.deleteRow(this.getRowById(id));
360    };
361    Table.prototype.createNewId = function () {
362        if (this.isForm) {
363            // There's no point! Forms don't have IDs, because the Date field is treated like an ID!
364            throw new Error('ID generation not supported for forms');
365        }
366        var time = new Date().getTime();
367        if (time <= this.idCounter) {
368            ++this.idCounter;
369        }
370        else {
371            this.idCounter = time;
372        }
373        return this.idCounter;
374    };
375    // This is auto-called by the website (client) which is also known as the frontend.
376    Table.prototype.processClientAsk = function (args) {
377        if (this.isForm) {
378            throw new Error('cannot access form tables from the client API');
379        }
380        if (args[0] === 'retrieveAll') {
381            return this.retrieveAllRecords();
382        }
383        if (args[0] === 'update') {
384            this.updateRecord(args[1]);
385            onClientNotification(['update', this.name, args[1]]);
386            return null;
387        }
388        if (args[0] === 'create') {
389            var newRecord = this.createRecord(args[1]);
390            onClientNotification(['create', this.name, newRecord]);
391            return newRecord;
392        }
393        if (args[0] === 'delete') {
394            this.deleteRecord(args[1]);
395            onClientNotification(['delete', this.name, args[1]]);
396            return null;
397        }
398        if (args[0] === 'debugheaders') {
399            this.rebuildSheetHeadersIfNeeded();
400            return null;
401        }
402        if (args[0] === 'debugreseteverything') {
403            this.resetEntireSheet();
404            return null;
405        }
406        throw new Error('args not matched');
407    };
408    Table.tableInfoAll = {
409        tutors: {
410            sheetName: '$tutors',
411            fields: [
412                new NumberField('id'),
413                new DateField('date')
414            ].concat(Table.makeBasicStudentConfig(), [
415                new JsonField('mods'),
416                new JsonField('modsPref'),
417                new StringField('subjectList'),
418                new JsonField('attendance'),
419                new JsonField('dropInMods'),
420                new StringField('afterSchoolAvailability'),
421                new NumberField('additionalHours')
422            ])
423        },
424        learners: {
425            sheetName: '$learners',
426            fields: [
427                new NumberField('id'),
428                new DateField('date')
429            ].concat(Table.makeBasicStudentConfig(), [
430                new JsonField('attendance')
431            ])
432        },
433        requests: {
434            sheetName: '$requests',
435            fields: [
436                new NumberField('id'),
437                new DateField('date'),
438                new NumberField('learner'),
439                new JsonField('mods'),
440                new StringField('subject'),
441                new StringField('specialRoom'),
442                new NumberField('step'),
443                new NumberField('chosenBooking')
444            ]
445        },
446        requestSubmissions: {
447            sheetName: '$request-submissions',
448            fields: [
449                new NumberField('id'),
450                new DateField('date')
451            ].concat(Table.makeBasicStudentConfig(), [
452                new JsonField('mods'),
453                new StringField('subject'),
454                new NumberField('studentId'),
455                new StringField('specialRoom'),
456                new StringField('status')
457            ])
458        },
459        bookings: {
460            sheetName: '$bookings',
461            fields: [
462                new NumberField('id'),
463                new DateField('date'),
464                new NumberField('request'),
465                new NumberField('tutor'),
466                new NumberField('mod'),
467                new StringField('status')
468            ]
469        },
470        matchings: {
471            sheetName: '$matchings',
472            fields: [
473                new NumberField('id'),
474                new DateField('date'),
475                new NumberField('learner'),
476                new NumberField('tutor'),
477                new StringField('subject'),
478                new NumberField('mod'),
479                new StringField('specialRoom')
480            ]
481        },
482        requestForm: {
483            sheetName: '$request-form',
484            // this means that the ID field is automatically generated from the date field
485            isForm: true,
486            fields: [
487                // THE ORDER OF THE FIELDS MATTERS! They must match the order of the form's questions.
488                new DateField('date'),
489                new StringField('firstName'),
490                new StringField('lastName'),
491                new StringField('friendlyFullName'), // EDIT!!!!!!!
492                new NumberField('studentId'),
493                new NumberField('grade'),
494                new StringField('subject'),
495                new StringField('modDataA1To5'),
496                new StringField('modDataB1To5'),
497                new StringField('modDataA6To10'),
498                new StringField('modDataB6To10'),
499              new StringField('homeroom'),
500              new StringField('homeroomTeacher'),
501                new StringField('email'),
502                new StringField('phone'),
503                new StringField('contactPref'),
504                new StringField('iceCreamQuestion')
505            ]
506        },
507        specialRequestForm: {
508            sheetName: '$special-request-form',
509            isForm: true,
510            fields: [
511                new DateField('date'),
512                new StringField('firstName'),
513                new StringField('lastName'),
514                new StringField('friendlyName'),
515                new StringField('friendlyFullName'),
516                new NumberField('studentId'),
517                new NumberField('grade'),
518                new StringField('subject'),
519                new StringField('abDay'),
520                new NumberField('mod1To10'),
521                new StringField('specialRoom'),
522                new StringField('iceCreamQuestion')
523                // contact info is intentionally omitted
524            ]
525        },
526        attendanceForm: {
527            sheetName: '$attendance-form',
528            isForm: true,
529            fields: [
530                new DateField('date'),
531                new DateField('dateOfAttendance'),
532                new StringField('abDay'),
533                new NumberField('mod1To10'),
534                new NumberField('studentId'),
535                new StringField('presence')
536            ]
537        },
538        tutorRegistrationForm: {
539            sheetName: '$tutor-registration-form',
540            isForm: true,
541            fields: [
542                new DateField('date'),
543                new StringField('firstName'),
544                new StringField('lastName'),
545                new StringField('friendlyName'),
546                new StringField('friendlyFullName'),
547                new NumberField('studentId'),
548                new StringField('grade'),
549                new StringField('email'),
550                new StringField('phone'),
551                new StringField('contactPref'),
552                new StringField('homeroom'),
553                new StringField('homeroomTeacher'),
554                new StringField('afterSchoolAvailability'),
555                new StringField('modDataA1To5'),
556                new StringField('modDataB1To5'),
557                new StringField('modDataA6To10'),
558                new StringField('modDataB6To10'),
559                new StringField('modDataPrefA1To5'),
560                new StringField('modDataPrefB1To5'),
561                new StringField('modDataPrefA6To10'),
562                new StringField('modDataPrefB6To10'),
563                new StringField('subjects0'),
564                new StringField('subjects1'),
565                new StringField('subjects2'),
566                new StringField('subjects3'),
567                new StringField('subjects4'),
568                new StringField('subjects5'),
569                new StringField('subjects6'),
570                new StringField('subjects7'),
571                new StringField('iceCreamQuestion'),
572                new NumberField('numberGuessQuestion')
573            ]
574        },
575        attendanceLog: {
576            // this table is merged into the JSON of tutor.fields.attendance
577            // the table will get quite large, so we will hand-archive it from time to time
578            // ASSUMPTION: (thus...) the table DOESN'T contain all of the attendance data. Some of it
579            // will be archived somewhere else. The JSON will be merged with the attendance log.
580            sheetName: '$attendance-log',
581            fields: [
582                new NumberField('id'),
583                new DateField('date'),
584                new DateField('dateOfAttendance'),
585                new StringField('validity'),
586                new NumberField('mod'),
587                // one of these ID fields will be left as -1 (blank).
588                new NumberField('tutor'),
589                new NumberField('learner'),
590                new NumberField('minutesForTutor'),
591                new NumberField('minutesForLearner'),
592                new StringField('presenceForTutor'),
593                new StringField('presenceForLearner')
594            ]
595        },
596        attendanceDays: {
597            sheetName: '$attendance-days',
598            fields: [
599                new NumberField('id'),
600                new DateField('date'),
601                new DateField('dateOfAttendance'),
602                new StringField('abDay'),
603                // we add a functionality to reset a day's attendance absences
604                new StringField('status') // upcoming, finished, finalized, unreset, reset
605            ]
606        },
607        operationLog: {
608            sheetName: '$operation-log',
609            fields: [
610                new NumberField('id'),
611                new DateField('date'),
612                new JsonField('args')
613            ]
614        }
615    };
616    return Table;
617}());
618// The point of this whole thing is so we don't read 10+ tables each time the script is run.
619// Tables are only loaded once you write the magic words: tableMap.NAMEOFTABLE().
620var tableMap = __assign({}, tableMapBuild('tutors'), tableMapBuild('learners'), tableMapBuild('requests'), tableMapBuild('requestSubmissions'), tableMapBuild('bookings'), tableMapBuild('matchings'), tableMapBuild('requestForm'), tableMapBuild('specialRequestForm'), tableMapBuild('attendanceForm'), tableMapBuild('attendanceLog'), tableMapBuild('operationLog'), tableMapBuild('attendanceDays'), tableMapBuild('tutorRegistrationForm'));
621// This fancy code makes it so the table isn't loaded twice if you call tableMap.NAMEOFTABLE() twice.
622function tableMapBuild(name) {
623    var _a;
624    return _a = {},
625        _a[name] = (function () {
626            var table = null;
627            return function () {
628                if (table === null) {
629                    return new Table(name);
630                }
631                else {
632                    return table;
633                }
634            };
635        })(),
636        _a;
637}
638/*
639
640IMPORTANT EVENT HANDLERS
641(CODE THAT DOES ALL THE USER ACTIONS NECESSARY IN THE BACKEND)
642(ALSO CODE THAT HANDLES SERVER-CLIENT INTERACTIONS)
643
644*/
645function doGet() {
646    // TODO: fix this
647    tableMap.operationLog().resetEntireSheet();
648    return HtmlService.createHtmlOutputFromFile('index');
649}
650function processClientAsk(args) {
651    var resourceName = args[0];
652    if (resourceName === undefined) {
653        throw new Error('no args, or must specify resource name');
654    }
655    if (resourceName === 'command') {
656        if (args[1] === 'syncDataFromForms') {
657            return onSyncForms();
658        }
659        if (args[1] === 'recalculateAttendance') {
660            return onRecalculateAttendance();
661        }
662        if (args[1] === 'generateSchedule') {
663            return onGenerateSchedule();
664        }
665        throw new Error('unknown command');
666    }
667    if (tableMap[resourceName] === undefined) {
668        throw new Error("resource " + String(resourceName) + " not found");
669    }
670    var resource = tableMap[resourceName]();
671    return resource.processClientAsk(args.slice(1));
672}
673// this is the MAIN ENTRYPOINT that the client uses to ask the server for data.
674function onClientAsk(args) {
675    var returnValue = {
676        error: true,
677        val: null,
678        message: 'Mysterious error'
679    };
680    try {
681        returnValue = {
682            error: false,
683            val: processClientAsk(args),
684            message: null
685        };
686    }
687    catch (err) {
688        returnValue = {
689            error: true,
690            val: null,
691            message: stringifyError(err)
692        };
693    }
694    // If you send a too-big object, Google Apps Script doesn't let you do it, and null is returned. But if you stringify it, you're fine.
695    return JSON.stringify(returnValue);
696}
697// This, and anything related to it, is 100% TODO.
698function onClientNotification(args) {
699    // we record the logs
700    // TODO: have the client read them every 20 seconds so they know the things that other clients have done
701    // in the case that multiple clients are open at once
702    tableMap.operationLog().createRecord({
703        id: -1,
704        date: -1,
705        args: args
706    });
707}
708function debugClientApiTest() {
709    try {
710        var ui = SpreadsheetApp.getUi();
711        var response = ui.prompt('Enter args as JSON array');
712        ui.alert(JSON.stringify(onClientAsk(JSON.parse(response.getResponseText()))));
713    }
714    catch (err) {
715        Logger.log(stringifyError(err));
716        throw err;
717    }
718}
719// This is a useful debug. It rewrites all the sheet headers to what the app thinks the sheet headers "should" be.
720function debugHeaders() {
721    try {
722        for (var _i = 0, _a = Object.getOwnPropertyNames(tableMap); _i < _a.length; _i++) {
723            var name = _a[_i];
724            tableMap[name]().rebuildSheetHeadersIfNeeded();
725        }
726    }
727    catch (err) {
728        Logger.log(stringifyError(err));
729        throw err;
730    }
731}
732// Resets every table with length < 5. ONLY FOR DEMO PURPOSES. DO NOT RUN IN PRODUCTION.
733function debugResetAllSmallTables() {
734    try {
735        var ui = SpreadsheetApp.getUi();
736        var response = ui.prompt('Leave the box below blank to cancel debug operation.');
737        if (response.getResponseText() === 'DEBUG_SMALL_RESET') {
738            for (var _i = 0, _a = Object.getOwnPropertyNames(tableMap); _i < _a.length; _i++) {
739                var name = _a[_i];
740                var table = tableMap[name]();
741                if (Object.getOwnPropertyNames(table.retrieveAllRecords()).length < 5) {
742                    table.resetEntireSheet();
743                }
744            }
745        }
746    }
747    catch (err) {
748        Logger.log(stringifyError(err));
749        throw err;
750    }
751}
752// Completely wipes every single table in the database (except forms).
753function debugResetEverything() {
754    try {
755        var ui = SpreadsheetApp.getUi();
756        var response = ui.prompt('Leave the box below blank to cancel debug operation.');
757        if (response.getResponseText() === 'DEBUG_RESET') {
758            for (var _i = 0, _a = Object.getOwnPropertyNames(tableMap); _i < _a.length; _i++) {
759                var name = _a[_i];
760                tableMap[name]().resetEntireSheet();
761            }
762        }
763    }
764    catch (err) {
765        Logger.log(stringifyError(err));
766        throw err;
767    }
768}
769function debugRewriteEverything() {
770    try {
771        var ui = SpreadsheetApp.getUi();
772        var response = ui.prompt('Leave the box below blank to cancel debug operation.');
773        if (response.getResponseText() === 'DEBUG_REWRITE') {
774            for (var _i = 0, _a = Object.getOwnPropertyNames(tableMap); _i < _a.length; _i++) {
775                var name = _a[_i];
776                tableMap[name]().rewriteEntireSheet();
777            }
778        }
779    }
780    catch (err) {
781        Logger.log(stringifyError(err));
782        throw err;
783    }
784}
785// This is a utility designed for onSyncForms().
786// Syncs between the formTable and the actualTable that we want to associate with it.
787// Basically, we use formRecordToActualRecord() to convert form records to actual records.
788// Then the actual records go in the actualTable.
789// But we only do this for form records that have dates that don't exist as IDs in actualTable.
790// (Remember that a form date === a record ID.)
791// There is NO DELETING RECORDS! No matter what!
792function doFormSync(formTable, actualTable, formRecordToActualRecord) {
793    var actualRecords = actualTable.retrieveAllRecords();
794    var formRecords = formTable.retrieveAllRecords();
795    var numOfThingsSynced = 0;
796    // create an index of actualdata >> date.
797    // Then iterate over all formdata and find the ones that are missing from the index.
798    var index = {};
799    for (var _i = 0, _a = Object.getOwnPropertyNames(actualRecords); _i < _a.length; _i++) {
800        var idKey = _a[_i];
801        var record = actualRecords[idKey];
802        var dateIndexKey = String(record.date);
803        index[dateIndexKey] = record;
804    }
805    for (var _b = 0, _c = Object.getOwnPropertyNames(formRecords); _b < _c.length; _b++) {
806        var idKey = _c[_b];
807        var record = formRecords[idKey];
808        var dateIndexKey = String(record.date);
809        if (index[dateIndexKey] === undefined) {
810            actualTable.createRecord(formRecordToActualRecord(record));
811            ++numOfThingsSynced;
812        }
813    }
814    return numOfThingsSynced;
815}
816var MINUTES_PER_MOD = 38;
817function uiSyncForms() {
818    try {
819        var result = onSyncForms();
820        SpreadsheetApp.getUi().alert("Finished sync! " + result + " new form submits found.");
821    }
822    catch (err) {
823        Logger.log(stringifyError(err));
824        throw err;
825    }
826}
827// The main "sync forms" function that's crammed with form data formatting.
828function onSyncForms() {
829    // tables
830    var tutors = tableMap.tutors().retrieveAllRecords();
831    var matchings = tableMap.matchings().retrieveAllRecords();
832    // parsing contact preferences
833    function parseContactPref(s) {
834        if (s === 'Phone')
835            return 'phone';
836        if (s === 'Email')
837            return 'email';
838        return 'either';
839    }
840    // parsing grade
841    function parseGrade(g) {
842        if (g === 'Freshman')
843            return 9;
844        if (g === 'Sophomore')
845            return 10;
846        if (g === 'Junior')
847            return 11;
848        if (g === 'Senior')
849            return 12;
850        return 0;
851    }
852    function parseModInfo(abDay, mod1To10) {
853        if (abDay.toLowerCase().charAt(0) === 'a') {
854            return mod1To10;
855        }
856        if (abDay.toLowerCase().charAt(0) === 'b') {
857            return mod1To10 + 10;
858        }
859        throw new Error(String(abDay) + " does not start with A or B");
860    }
861    function parseModData(modData) {
862        function doParse(d) {
863            return d
864                .split(',')
865                .map(function (x) { return x.trim(); })
866                .filter(function (x) { return x !== '' && x !== 'None'; })
867                .map(function (x) { return parseInt(x); });
868        }
869        var mA15 = doParse(modData[0]);
870        var mB15 = doParse(modData[1]).map(function (x) { return x + 10; });
871        var mA60 = doParse(modData[2]);
872        var mB60 = doParse(modData[3]).map(function (x) { return x + 10; });
873        return mA15
874            .concat(mA60)
875            .concat(mB15)
876            .concat(mB60);
877    }
878    function parseStudentConfig(r) {
879        return {
880            firstName: r.firstName,
881            lastName: r.lastName,
882          friendlyName: (r.friendlyName ? r.friendlyName : r.firstName), // EDIT!!!!!!!
883          friendlyFullName: (r.friendlyFullName ? r.friendlyFullName : r.firstName + ' ' + r.lastName), // EDIT!!!!!!!!!
884            grade: parseGrade(r.grade),
885            studentId: r.studentId,
886            email: r.email,
887            phone: r.phone,
888            contactPref: parseContactPref(r.contactPref),
889            homeroom: r.homeroom,
890            homeroomTeacher: r.homeroomTeacher,
891            attendanceAnnotation: ''
892        };
893    }
894    function processRequestFormRecord(r) {
895      // EDIT!!! chosenBooking
896        return __assign({ id: -1, date: r.date, subject: r.subject, mods: parseModData([
897                r.modDataA1To5,
898                r.modDataB1To5,
899                r.modDataA6To10,
900                r.modDataB6To10
901        ]), specialRoom: '' }, parseStudentConfig(r), { status: 'unchecked', homeroom: r.homeroom, homeroomTeacher: r.homeroomTeacher, chosenBooking: -1 });
902    }
903    function processTutorRegistrationFormRecord(r) {
904        function parseSubjectList(d) {
905            return d
906                .join(',')
907                .split(',') // remember that within each string there are commas
908                .map(function (x) { return x.trim(); })
909                .filter(function (x) { return x !== '' && x !== 'None'; })
910                .map(function (x) { return String(x); })
911                .join(', ');
912        }
913        return __assign({ id: -1, date: r.date }, parseStudentConfig(r), { mods: parseModData([
914                r.modDataA1To5,
915                r.modDataB1To5,
916                r.modDataA6To10,
917                r.modDataB6To10
918            ]), modsPref: parseModData([
919                r.modDataPrefA1To5,
920                r.modDataPrefB1To5,
921                r.modDataPrefA6To10,
922                r.modDataPrefB6To10
923            ]), subjectList: parseSubjectList([
924                r.subjects0,
925                r.subjects1,
926                r.subjects2,
927                r.subjects3,
928                r.subjects4,
929                r.subjects5,
930                r.subjects6,
931                r.subjects7
932            ]), attendance: {}, dropInMods: [], afterSchoolAvailability: r.afterSchoolAvailability, attendanceAnnotation: '', additionalHours: 0 });
933    }
934    function processSpecialRequestFormRecord(r) {
935        return __assign({ id: -1, date: r.date, subject: r.subject, specialRoom: r.specialRoom, mods: [parseModInfo(r.abDay, r.mod1To10)] }, parseStudentConfig(r), { homeroom: '[writing pass is unnecessary]', homeroomTeacher: '[writing pass is unnecessary]', attendanceAnnotation: '', status: 'unchecked' });
936    }
937    function processAttendanceFormRecord(r) {
938        var mod = parseModInfo(r.abDay, r.mod1To10);
939        var tutor = -1;
940        var learner = -1;
941        var validity = '';
942        var minutesForTutor = -1;
943        var minutesForLearner = -1;
944        var presenceForTutor = '';
945        var presenceForLearner = '';
946        // give the tutor their time
947        minutesForTutor = MINUTES_PER_MOD;
948        presenceForTutor = 'P';
949        // figure out who the tutor is, by student ID
950        var xTutors = recordCollectionToArray(tutors).filter(function (x) { return x.studentId === r.studentId; });
951        if (xTutors.length === 0) {
952            validity = 'tutor student ID does not exist';
953        }
954        else if (xTutors.length === 1) {
955            tutor = xTutors[0].id;
956        }
957        else {
958            throw new Error("duplicate tutor student id " + String(r.studentId));
959        }
960        // does the tutor have a learner?
961        var xMatchings = recordCollectionToArray(matchings).filter(function (x) { return x.tutor === tutor && x.mod === mod; });
962        if (xMatchings.length === 0) {
963            learner = -1;
964        }
965        else if (xMatchings.length === 1) {
966            learner = xMatchings[0].learner;
967        }
968        else {
969            throw new Error("the tutor " + xMatchings[0].friendlyFullName + " is matched twice on the same mod");
970        }
971        // ATTENDANCE LOGIC
972        if (validity === '') {
973            if (r.presence === 'Yes') {
974                minutesForLearner = MINUTES_PER_MOD;
975                presenceForLearner = 'P';
976            }
977            else if (r.presence === 'No') {
978                minutesForLearner = 0;
979                presenceForLearner = 'A';
980            }
981            else if (r.presence === "I don't have a learner assigned") {
982                if (learner === -1) {
983                    minutesForLearner = -1;
984                }
985                else {
986                    // so there really is a learner...
987                    validity = "tutor said they don't have a learner assigned, but they do!";
988                }
989            }
990            else if (r.presence === "No, but the learner doesn't need any tutoring today") {
991                if (learner === -1) {
992                    validity = "tutor said they have a learner assigned, but they don't!";
993                }
994                else {
995                    minutesForLearner = 1; // TODO: this is a hacky solution; fix it
996                    presenceForLearner = 'E';
997                }
998            }
999            else {
1000                throw new Error("invalid presence (" + String(r.presence) + ")");
1001            }
1002        }
1003        return {
1004            id: r.id,
1005            date: r.date,
1006            dateOfAttendance: roundDownToDay(r.dateOfAttendance === -1 ? r.date : r.dateOfAttendance),
1007            validity: validity,
1008            mod: mod,
1009            tutor: tutor,
1010            learner: learner,
1011            minutesForTutor: minutesForTutor,
1012            minutesForLearner: minutesForLearner,
1013            presenceForTutor: presenceForTutor,
1014            presenceForLearner: presenceForLearner
1015        };
1016    }
1017    var numOfThingsSynced = 0;
1018    numOfThingsSynced += doFormSync(tableMap.requestForm(), tableMap.requestSubmissions(), processRequestFormRecord);
1019    // EDIT!!!!!!! numOfThingsSynced += doFormSync(tableMap.specialRequestForm(), tableMap.requestSubmissions(), processSpecialRequestFormRecord);
1020    numOfThingsSynced += doFormSync(tableMap.attendanceForm(), tableMap.attendanceLog(), processAttendanceFormRecord);
1021    numOfThingsSynced += doFormSync(tableMap.tutorRegistrationForm(), tableMap.tutors(), processTutorRegistrationFormRecord);
1022    return numOfThingsSynced;
1023}
1024function uiRecalculateAttendance() {
1025    try {
1026        var numAttendancesChanged = onRecalculateAttendance();
1027        SpreadsheetApp.getUi().alert("Finished attendance update. " + numAttendancesChanged + " attendances were changed.");
1028    }
1029    catch (err) {
1030        Logger.log(stringifyError(err));
1031        throw err;
1032    }
1033}
1034// This recalculates the attendance.
1035function onRecalculateAttendance() {
1036    var numAttendancesChanged = 0;
1037    function calculateIsBDay(x) {
1038        if (x.toLowerCase().charAt(0) === 'a') {
1039            return false;
1040        }
1041        else if (x.toLowerCase().charAt(0) === 'b') {
1042            return true;
1043        }
1044        else {
1045            throw new Error('unrecognized attendance day letter');
1046        }
1047    }
1048    function whenTutorFormNotFilledOutLogic(tutorId, learnerId, mod, day) {
1049        var tutor = tutors[tutorId];
1050      var learner = learnerId === -1 ? null : learners[learnerId];
1051      // EDIT!!!!!!!
1052      var date = day.dateOfAttendance;
1053        // mark tutor as (un-?)absent at a specific date and mod
1054        if (day.status === 'doreset') {
1055            if (tutor.attendance[date] !== undefined) {
1056                // filter out some absences (excused absences are actually counted as a 1-minute presence)
1057                tutor.attendance[date] = tutor.attendance[date].filter(function (x) {
1058                    if (x.mod === mod && x.minutes === 0) {
1059                        ++numAttendancesChanged;
1060                        return false;
1061                    }
1062                    else {
1063                        return true;
1064                    }
1065                });
1066            }
1067        }
1068        if (day.status === 'doit') {
1069            var alreadyExists = false; // if a presence or absence exists, don't add an absence
1070            if (tutor.attendance[date] === undefined) {
1071                tutor.attendance[date] = [];
1072            }
1073            if (learner !== null && learner.attendance[date] === undefined) {
1074                learner.attendance[date] = [];
1075            }   // EDIT!!!!!!!!
1076            else {
1077                for (var _i = 0, _a = tutor.attendance[date]; _i < _a.length; _i++) {
1078                    var x = _a[_i];
1079                    if (x.mod === mod) {
1080                        alreadyExists = true;
1081                    }
1082                }
1083            }
1084            if (!alreadyExists) {
1085                // add an absence for the tutor
1086                tutor.attendance[date].push({
1087                    date: date,
1088                    mod: mod,
1089                    minutes: 0
1090                });
1091                // add an excused absence for the learner, if exists
1092                if (learnerId !== -1) {
1093                    learners[learnerId].attendance[date].push({
1094                        date: date,
1095                        mod: mod,
1096                        minutes: 1
1097                    });
1098                }
1099                numAttendancesChanged += 2;
1100            }
1101        }
1102    }
1103    function applyAttendanceForStudent(attendance, entry, minutes) {
1104        if (attendance[entry.dateOfAttendance] === undefined) {
1105            attendance[entry.dateOfAttendance] = [];
1106        }
1107        var isNew = true;
1108        for (var _i = 0, _a = attendance[entry.dateOfAttendance]; _i < _a.length; _i++) {
1109            var i = _a[_i];
1110            if (entry.mod === i.mod) {
1111                isNew = false;
1112            }
1113        }
1114        if (isNew) {
1115            attendance[entry.dateOfAttendance].push({
1116                date: entry.dateOfAttendance,
1117                mod: entry.mod,
1118                minutes: minutes
1119            });
1120            ++numAttendancesChanged;
1121        }
1122    }
1123    // read tables
1124    var tutors = tableMap.tutors().retrieveAllRecords();
1125    var learners = tableMap.learners().retrieveAllRecords();
1126    var attendanceLog = tableMap.attendanceLog().retrieveAllRecords();
1127    var attendanceDays = tableMap.attendanceDays().retrieveAllRecords();
1128    var matchings = tableMap.matchings().retrieveAllRecords();
1129    var tutorsArray = Object_values(tutors);
1130    var learnersArray = Object_values(learners);
1131    var matchingsArray = Object_values(matchings);
1132    var attendanceLogArray = Object_values(attendanceLog);
1133    // PROCESS EACH ATTENDANCE LOG ENTRY
1134    for (var _i = 0, attendanceLogArray_1 = attendanceLogArray; _i < attendanceLogArray_1.length; _i++) {
1135        var entry = attendanceLogArray_1[_i];
1136        if (entry.tutor !== -1) {
1137            applyAttendanceForStudent(tutors[entry.tutor].attendance, entry, entry.minutesForTutor);
1138        }
1139        if (entry.learner !== -1) {
1140            applyAttendanceForStudent(learners[entry.learner].attendance, entry, entry.minutesForLearner);
1141        }
1142    }
1143    var tutorAttendanceFormIndex = {};
1144    for (var _a = 0, tutorsArray_1 = tutorsArray; _a < tutorsArray_1.length; _a++) {
1145        var tutor = tutorsArray_1[_a];
1146        tutorAttendanceFormIndex[tutor.id] = {
1147            id: tutor.id,
1148            mod: {}
1149        };
1150        // handle drop-ins
1151        for (var _b = 0, _c = tutor.dropInMods; _b < _c.length; _b++) {
1152            var mod = _c[_b];
1153            tutorAttendanceFormIndex[tutor.id].mod[mod] = {
1154                wasFormSubmitted: false,
1155                learnerId: -1
1156            };
1157        }
1158    }
1159    for (var _d = 0, matchingsArray_1 = matchingsArray; _d < matchingsArray_1.length; _d++) {
1160        var matching = matchingsArray_1[_d];
1161        tutorAttendanceFormIndex[matching.tutor].mod[matching.mod] = {
1162            wasFormSubmitted: false,
1163            learnerId: matching.learner
1164        };
1165    }
1166  // EDIT!!!!!!
1167  Logger.log(JSON.stringify(tutorAttendanceFormIndex));
1168    // DEAL WITH THE UNSUBMITTED FORMS
1169    for (var _e = 0, _f = Object_values(attendanceDays); _e < _f.length; _e++) {
1170        var day = _f[_e];
1171        if (day.status === 'ignore' ||
1172            day.status === 'isdone' ||
1173            day.status === 'isreset') {
1174            continue;
1175        }
1176        if (day.status !== 'doit' && day.status !== 'doreset') {
1177            throw new Error('unknown day status');
1178        }
1179        // modify dateOfAttendance; round down to the nearest 24-hour day
1180        day.dateOfAttendance = roundDownToDay(day.dateOfAttendance);
1181        var isBDay = calculateIsBDay(day.abDay);
1182        // iterate through all tutors & hunt down the ones that didn't fill out the form
1183        for (var _g = 0, tutorsArray_2 = tutorsArray; _g < tutorsArray_2.length; _g++) {
1184            var tutor = tutorsArray_2[_g];
1185            for (var i = isBDay ? 10 : 0; i < (isBDay ? 20 : 10); ++i) {
1186                var x = tutorAttendanceFormIndex[tutor.id].mod[i];
1187                if (x !== undefined && !x.wasFormSubmitted) {
1188                    whenTutorFormNotFilledOutLogic(tutor.id, x.learnerId, i, day);
1189                }
1190            }
1191            if (tutor.attendance[day.dateOfAttendance] !== undefined &&
1192                tutor.attendance[day.dateOfAttendance].length === 0) {
1193                delete tutor.attendance[day.dateOfAttendance];
1194            }
1195        }
1196        // change day status
1197        if (day.status === 'doreset') {
1198            day.status = 'isreset';
1199        }
1200        if (day.status === 'doit') {
1201            day.status = 'isdone';
1202        }
1203        // update record
1204        tableMap.attendanceDays().updateRecord(day);
1205    }
1206    // THAT'S ALL! UPDATE TABLES
1207    tableMap.tutors().updateAllRecords(tutorsArray);
1208    tableMap.learners().updateAllRecords(learnersArray);
1209    return numAttendancesChanged;
1210}
1211function uiGenerateSchedule() {
1212    try {
1213        onGenerateSchedule();
1214        SpreadsheetApp.getUi().alert('Success');
1215    }
1216    catch (err) {
1217        Logger.log(stringifyError(err));
1218        throw err;
1219    }
1220}
1221// This is the KEY FUNCTION for generating the "schedule" sheet.
1222function onGenerateSchedule() {
1223    var sortComparator = function (a, b) {
1224        var x = a.tutorName.toLowerCase();
1225        var y = b.tutorName.toLowerCase();
1226        if (x < y)
1227            return -1;
1228        if (x > y)
1229            return 1;
1230        return 0;
1231    };
1232    // Delete & insert sheet
1233    var ss = SpreadsheetApp.openById('1VbrZTMXGju_pwSrY7-M0l8citrYTm5rIv7RPuKN9deY');
1234    var sheet = ss.getSheetByName('schedule');
1235    if (sheet !== null) {
1236        ss.deleteSheet(sheet);
1237    }
1238    sheet = ss.insertSheet('schedule', 0);
1239    // Get all matchings, tutors, and learners
1240    var matchings = tableMap.matchings().retrieveAllRecords();
1241    var tutors = tableMap.tutors().retrieveAllRecords();
1242    var learners = tableMap.learners().retrieveAllRecords();
1243    // Header
1244    sheet.appendRow(['ARC SCHEDULE']);
1245    sheet.appendRow(["Automatically generated on " + new Date()]);
1246    // Create a list of [ mod, tutorName, info about matching, as string ]
1247    var scheduleInfo = []; // mod = -1 means that the tutor has no one
1248    // Figure out all matchings that are finalized, indexed by tutor
1249    var index = {};
1250    // create a bunch of blanks in the index
1251    for (var _i = 0, _a = Object_values(tutors); _i < _a.length; _i++) {
1252        var x = _a[_i];
1253        index[String(x.id)] = {
1254            isMatched: false,
1255            hasBeenScheduled: false
1256        };
1257    }
1258    // fill in index with matchings
1259    for (var _b = 0, _c = Object_values(matchings); _b < _c.length; _b++) {
1260        var x = _c[_b];
1261        var name = learners[x.learner].friendlyFullName;
1262        index[x.tutor].isMatched = true;
1263        index[x.tutor].hasBeenScheduled = true;
1264        scheduleInfo.push({
1265            isDropIn: false,
1266            mod: x.mod,
1267            tutorName: tutors[x.tutor].friendlyFullName,
1268            info: x.specialRoom === '' || x.specialRoom === undefined
1269                ? "(w/" + name + ")"
1270                : "(w/" + name + " SPECIAL @room " + x.specialRoom + ")"
1271        });
1272    }
1273    var unscheduledTutorNames = [];
1274    // fill in index with drop-ins
1275    for (var _d = 0, _e = Object_values(tutors); _d < _e.length; _d++) {
1276        var x = _e[_d];
1277        for (var _f = 0, _g = x.dropInMods; _f < _g.length; _f++) {
1278            var mod = _g[_f];
1279            index[String(x.id)].hasBeenScheduled = true;
1280            scheduleInfo.push({
1281                isDropIn: true,
1282                mod: mod,
1283                tutorName: x.friendlyFullName,
1284                info: '(drop in)'
1285            });
1286        }
1287        if (!index[String(x.id)].hasBeenScheduled &&
1288            !index[String(x.id)].isMatched) {
1289            unscheduledTutorNames.push(x.friendlyFullName);
1290        }
1291    }
1292    // Print!
1293    // CHANGE COLUMNS
1294    sheet.deleteColumns(5, sheet.getMaxColumns() - 5);
1295    sheet.setColumnWidth(1, 30);
1296    sheet.setColumnWidth(2, 300);
1297    sheet.setColumnWidth(3, 30);
1298    sheet.setColumnWidth(4, 300);
1299    sheet.setColumnWidth(5, 30);
1300    // HEADER
1301    sheet.getRange(1, 1, 3, 5).mergeAcross();
1302    sheet
1303        .getRange(1, 1)
1304        .setValue('ARC Schedule')
1305        .setFontSize(36)
1306        .setHorizontalAlignment('center');
1307    sheet
1308        .getRange(2, 1)
1309        .setValue('Automatically generated on ' + new Date().toISOString())
1310        .setFontSize(14)
1311        .setHorizontalAlignment('center');
1312    sheet.setRowHeight(3, 30);
1313    sheet
1314        .getRange(4, 2)
1315        .setValue('A Days')
1316        .setFontSize(18)
1317        .setHorizontalAlignment('center');
1318    sheet
1319        .getRange(4, 4)
1320        .setValue('B Days')
1321        .setFontSize(18)
1322        .setHorizontalAlignment('center');
1323    sheet.setRowHeight(5, 30);
1324    var layoutMatrix = []; // [mod0to9][abday]
1325    var _loop_1 = function (i) {
1326        layoutMatrix.push([
1327            scheduleInfo.filter(function (x) { return x.mod === i + 1; }).sort(sortComparator),
1328            scheduleInfo.filter(function (x) { return x.mod === i + 11; }).sort(sortComparator) // B days
1329        ]);
1330    };
1331    for (var i = 0; i < 10; ++i) {
1332        _loop_1(i);
1333    }
1334    // LAYOUT
1335    var nextRow = 6;
1336    for (var i = 0; i < 10; ++i) {
1337        var scheduleRowSize = Math.max(layoutMatrix[i][0].length, layoutMatrix[i][1].length, 1);
1338        // LABEL
1339        sheet.getRange(nextRow, 1, scheduleRowSize).merge();
1340        sheet
1341            .getRange(nextRow, 1)
1342            .setValue("" + (i + 1))
1343            .setFontSize(18)
1344            .setVerticalAlignment('top');
1345        // CONTENT
1346        if (layoutMatrix[i][0].length > 0) {
1347            sheet
1348                .getRange(nextRow, 2, layoutMatrix[i][0].length)
1349                .setValues(layoutMatrix[i][0].map(function (x) { return [x.tutorName + " " + x.info]; }))
1350                .setWrap(true)
1351                .setFontColors(layoutMatrix[i][0].map(function (x) { return [x.isDropIn ? 'black' : 'red']; }));
1352        }
1353        if (layoutMatrix[i][1].length > 0) {
1354            sheet
1355                .getRange(nextRow, 4, layoutMatrix[i][1].length)
1356                .setValues(layoutMatrix[i][1].map(function (x) { return [x.tutorName + " " + x.info]; }))
1357                .setWrap(true)
1358                .setFontColors(layoutMatrix[i][1].map(function (x) { return [x.isDropIn ? 'black' : 'red']; }));
1359        }
1360        // SET THE NEXT ROW
1361        nextRow += scheduleRowSize;
1362        // GUTTER
1363        sheet.getRange(nextRow, 1, 1, 5).merge();
1364        sheet.setRowHeight(nextRow, 60);
1365        ++nextRow;
1366    }
1367    // UNSCHEDULED TUTORS
1368    sheet
1369        .getRange(nextRow, 2, 1, 3)
1370        .merge()
1371        .setValue("Unscheduled tutors")
1372        .setFontSize(18)
1373        .setFontStyle('italic')
1374        .setHorizontalAlignment('center')
1375        .setWrap(true);
1376    ++nextRow;
1377    sheet
1378        .getRange(nextRow, 2, unscheduledTutorNames.length, 3)
1379        .mergeAcross()
1380        .setHorizontalAlignment('center');
1381    sheet
1382        .getRange(nextRow, 2, unscheduledTutorNames.length)
1383        .setValues(unscheduledTutorNames.map(function (x) { return [x + ' (unscheduled)']; }));
1384    nextRow += unscheduledTutorNames.length;
1385    // FOOTER
1386    sheet
1387        .getRange(nextRow, 2, 1, 4)
1388        .merge()
1389        .setValue("That's all!")
1390        .setFontSize(18)
1391        .setFontStyle('italic')
1392        .setHorizontalAlignment('center');
1393    ++nextRow;
1394    // FIT ROWS/COLUMNS
1395    sheet.deleteRows(sheet.getLastRow() + 1, sheet.getMaxRows() - sheet.getLastRow());
1396    return null;
1397}
1398function onOpen(_ev) {
1399    var menu = SpreadsheetApp.getUi().createMenu('ARC APP');
1400    menu.addItem('Sync data from forms', 'uiSyncForms');
1401    menu.addItem('Generate schedule', 'uiGenerateSchedule');
1402    menu.addItem('Recalculate attendance', 'uiRecalculateAttendance');
1403    if (ARC_APP_DEBUG_MODE) {
1404        menu.addItem('Debug: test client API', 'debugClientApiTest');
1405        menu.addItem('Debug: reset all tables', 'debugResetEverything');
1406        menu.addItem('Debug: reset all small tables', 'debugResetAllSmallTables');
1407        menu.addItem('Debug: rebuild all headers', 'debugHeaders');
1408        menu.addItem('Debug: rewrite all tables', 'debugRewriteEverything');
1409    }
1410    menu.addToUi();
1411}