Course planning tool to distribute individual Google Sheet course plans to students and advisors based on Blackbaud registration data
License: GNU General Public License v3.0
course-planning-tool/src/CoursePlan.ts
Line 136 in 40af46f
spreadsheet.deleteSheet(sheet);
}
});
// TODO make sure course list validation transfers to new spreadsheet
// TODO add access privileges
// FIXME file is getting neither moved nor shortcutted
const file = DriveApp.getFileById(spreadsheet.getId());
file.moveTo(CoursePlan.getFormFolder(student.gradYear));
CoursePlan.getAdvisorFolder(student).createShortcut(file.getId());
return spreadsheet;
}
private static getGraceEnrollments(student: Student): string {
return (
'=' +
s.fcn(course-planning-tool/src/CoursePlan.ts
Line 17 in d128efd
null
);
// TODO modify workflow to allow for updating enrollment history on existing course plans
// TODO document process for change of advisor
export default class CoursePlan {
public static readonly PROGRESS_KEY = 'course-plan';
course-planning-tool/src/Inventory.ts
Line 21 in c1ca303
}
private getData() {
// TODO reasonably, caching this should improve performance, no?
return this.inventorySheet
.getRange(
1,course-planning-tool/CoursePlan.ts
Line 136 in 4f766da
.build());
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);course-planning-tool/src/Student.ts
Line 60 in 38e277f
.getValues()
.slice(0, 10) // FIXME remove when testing complete
.map((row) => new Student(row))
.filter((student) => student.gradYear != thisYear); // TODO confirm filtering out seniors
}
}course-planning-tool/CoursePlan.js
Line 66 in bbcc483
}
}
// TODO deal with GRACE course selection
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)course-planning-tool/CoursePlan.js
Line 69 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
} protected abstract creator(key: Key): T;
private getData() {
// TODO reasonably, caching this should improve performance, no?
return this.sheet
.getRange(1, 1, this.sheet.getMaxRows(), this.sheet.getMaxColumns())
.getValues(); plans.deleteRows(3, plans.getMaxRows() - 2);
plans.getRange('A2:C2').setValues([['', '', '']]);
}
// TODO finish deleteAll by making course plan inventory active sheet
}
global.action_coursePlan_deleteAll = deleteAll;course-planning-tool/src/CoursePlan.ts
Line 204 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup'; }
private getData() {
return this.inventorySheet
.getRange(
1,
1,
this.inventorySheet.getMaxRows(),
this.inventorySheet.getMaxColumns()
)
.getValues();
}
public getFolder(key): GoogleAppsScript.Drive.Folder {
// FIXME advisor folders keep being re-created
const id = this.getData().reduce((id: string, [k, i, u]) => {
if (k == key) {
return i;course-planning-tool/src/CoursePlan.ts
Line 455 in 01eddd5
}
}
// TODO extract to @battis/gas-lighter
private clearEditors(protection: GoogleAppsScript.Spreadsheet.Protection) {
if (!CoursePlan.me) {
CoursePlan.me = Session.getEffectiveUser();course-planning-tool/src/CoursePlan.ts
Line 311 in 01eddd5
this.setValue('Template_Years', years);
}
// TODO extract to @battis/gas-lighter
private replaceFunctionsWithDisplayValues() {
const range = this.getWorkingCopy().getRange(
1, }
public static getProgress() {
// FIXME progress bar is not updating
return {
total: CreateAll.cache.get(CreateAll.TOTAL),
current: CreateAll.cache.get(CreateAll.CURRENT),course-planning-tool/src/CoursePlan.ts
Line 34 in fc88c9c
public constructor(student: Student) {
// TODO deal with GRACE course selection
this.setStudent(student);
this.createFromTemplate();
this.populateHeaders();
this.populateEnrollmentHistory();
this.setPermissions();
this.prepareCommentBlanks();
}course-planning-tool/CoursePlan.js
Line 67 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
}Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
course-planning-tool/src/CoursePlan.ts
Line 207 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup';course-planning-tool/src/CoursePlan.ts
Line 157 in fd5b009
}
}
private createFromTemplate() {
// TODO log link to plan in data sheet
const template = State.getTemplate();
this.setSpreadsheet(
template.copy(
this.applyFormat(
SheetParameters.getCoursePlanNameFormat(),
this.getStudent()
)
)
);
s.addImportrangePermission(this.getSpreadsheet(), State.getDataSheet());
this.setWorkingCopy(
this.getSpreadsheet()
.getSheetByName(Constants.Spreadsheet.Sheet.COURSE_PLAN)
.copyTo(State.getDataSheet())
);
}
private updateHeaders() {
this.setValue(Constants.Spreadsheet.A1Notation.NAMES, [
[this.getStudent().getFormattedName()],
[`Advisor: ${this.getAdvisor().getFormattedName()}`],
]);
const gradYear = this.getStudent().gradYear;
const years: (string | number)[] = [4, 3, 2, 1, 0].map(
(value) => `${gradYear - value - 1} - ${gradYear - value}`
);
years.splice(2, 0, gradYear - 3);
this.setValue(Constants.Spreadsheet.A1Notation.YEARS, years);
}
private replaceFunctionsWithDisplayValues() {
const range = this.getWorkingCopy().getRange(
1,
1,
this.getWorkingCopy().getMaxRows(),
this.getWorkingCopy().getMaxColumns()
);
const display = range.getDisplayValues();
range.setValues(display);
}
private moveToStudentCoursePlanSpreadsheet() {
this.getSpreadsheet().deleteSheet(
this.getSpreadsheet().getSheetByName(
Constants.Spreadsheet.Sheet.COURSE_PLAN
)
);
const plan = this.getWorkingCopy().copyTo(this.getSpreadsheet());
State.getDataSheet().deleteSheet(this.getWorkingCopy());
this.setWorkingCopy(plan);
this.getWorkingCopy().setName(Constants.Spreadsheet.Sheet.COURSE_PLAN);
this.spreadsheet.setActiveSheet(this.getWorkingCopy());
this.spreadsheet.moveActiveSheet(1);
}
private setPermissions() {
// TODO add access privileges
// FIXME file is getting neither moved nor shortcutted
const file = DriveApp.getFileById(this.getSpreadsheet().getId());
file.moveTo(this.getFormFolder());
this.getAdvisorFolder().createShortcut(file.getId());
return file;
}
private getGRACEEnrollmentsFunction(): string {
return (
'=' +
s.ifna(
s.join(
s.char(10),
s.sort(
s.filter(
Constants.Spreadsheet.Range.TITLE,
s.eq(
Constants.Spreadsheet.Range.HOST_ID,
this.getStudent().hostId
),
s.eq(Constants.Spreadsheet.Range.DEPT, Constants.GRACE)
)
)course-planning-tool/CoursePlan.js
Line 68 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
}Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
course-planning-tool/CoursePlan.js
Line 75 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
}course-planning-tool/src/index.ts
Line 6 in 170bf60
global.onOpen = Editor.onOpen;
global.onInstall = Editor.onInstall;
// FIXME include() should be in @battis/google-apps-script-helpers
global.include = (filename: string) => {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
};import Constants from './Constants';
import State from './State';
// TODO there is surely a less cumbersome way to do this
export default class SheetParameters {
private static params?: GoogleAppsScript.Spreadsheet.Sheet;
public static getParam(a1Notation) {
if (!SheetParameters.params) {
SheetParameters.params = State.getDataSheet().getSheetByName(
Constants.Spreadsheet.Sheet.PARAMETERS
);
}
return SheetParameters.params.getRange(a1Notation).getValue();
}
public static getNumOptionsPerDepartment = SheetParameters.getParam.bind(
null,
Constants.Spreadsheet.A1Notation.NUM_OPTIONS_PER_DEPT
);
public static getFormFolderNameFormat = SheetParameters.getParam.bind(
null,
Constants.Spreadsheet.A1Notation.FORM_FOLDER_NAME_FORMAT
);
public static getAdvisorFolderNameFormat = SheetParameters.getParam.bind(
null,
Constants.Spreadsheet.A1Notation.ADVISOR_FOLDER_NAME_FORMAT
);
public static getCoursePlanNameFormat = SheetParameters.getParam.bind(
null,
Constants.Spreadsheet.A1Notation.COURSE_PLAN_NAME_FORMAT
);
}course-planning-tool/src/CoursePlan/index.ts
Line 107 in 135672f
private bindToExistingSpreadsheet({ hostId, spreadsheetId }) {
// TODO double check that this exists in inventory
// TODO add option to update when "created"?
this.setStudent(Role.Student.getByHostId(hostId));
this.setSpreadsheet(SpreadsheetApp.openById(spreadsheetId));
}course-planning-tool/src/CoursePlan.ts
Line 45 in 85b869f
}
public static for(student: Student) {
// TODO update inventory page way sooner
if (!CoursePlan.coursePlanInventory) {
CoursePlan.coursePlanInventory = new Inventory('Course Plan Inventory');
}
return CoursePlan.coursePlanInventory.getCoursePlan(student);
}
// TODO extract Mockup as separate action
public static createMockup(student?: Student | string) {
student && State.setStudent(student);
student = State.getStudent();course-planning-tool/src/CoursePlan.ts
Line 202 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup';course-planning-tool/src/CoursePlan.ts
Line 487 in 85b869f
}
}
private protectNonCommentRanges(historyWidth: number, historyHeight: number) {
const history = this.getAnchorOffset(0, 0, historyHeight, historyWidth);
for (const range of [
history.getA1Notation(),
'Protect_LeftMargin',
'Protect_RightMargin',
'Protect_TopMargin',
'Protect_AdvisorInitials',
'Protect_AfterAdvisor',
'Protect_AfterStudiesCommittee',
]) {
const protection = this.getWorkingCopy()
.getRange(range)
.protect()
.setDescription('No Edits');
this.clearEditors(protection);
}
}
private clearEditors(protection: GoogleAppsScript.Spreadsheet.Protection) {
if (!CoursePlan.me) {
CoursePlan.me = Session.getEffectiveUser();
}
protection.addEditor(CoursePlan.me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
}
private prepareCommentBlanks() {
const numComments = SheetParameters.getNumComments();
const commentors = {
'Comments from Faculty Advisor': this.getAdvisor().email,
'Comments from Studies Committee': SheetParameters.getStudiesCommittee(),
'Comments from College Counseling Office':
SheetParameters.getCollegeCounseling(),
};
for (const commentor in commentors) {
// TODO extract finding a cell by content to @battis/google-apps-script-helpers
const row = this.getWorkingCopy()
.getRange(1, 2, this.getWorkingCopy().getMaxRows(), 1)
.getValues()
.findIndex(([cell]) => cell == commentor);
const protection = this.getWorkingCopy()
.getRange(
row + 3,
2,
2,
commentor == 'Comments from Faculty Advisor' ? 4 : 5
)
.protect()
.setDescription(commentor);
this.clearEditors(protection);
protection.addEditor(commentors[commentor]);
this.additionalComments(row + 3, numComments);
}
}course-planning-tool/src/CoursePlan.ts
Line 38 in 85b869f
}
public static for(student: Student) {
// TODO update inventory page way sooner
if (!CoursePlan.coursePlanInventory) {
CoursePlan.coursePlanInventory = new Inventory('Course Plan Inventory');
}
return CoursePlan.coursePlanInventory.getCoursePlan(student);
}
// TODO extract Mockup as separate action
public static createMockup(student?: Student | string) {
student && State.setStudent(student);
student = State.getStudent();course-planning-tool/src/Student.ts
Line 31 in 038f880
public getAdvisor() {
return Advisor.getByAdvisee(this.hostId);
}
public static getAll(): Student[] {
// TODO filter out seniors
return State.getDataSheet()
.getSheetByName(Constants.Spreadsheet.Sheet.ADVISORS)
.getRange(Constants.Spreadsheet.A1Notation.STUDENT_DATA)
.getValues()
.slice(0, 4) // FIXME remove when testing complete
.map((row) => new Student(row));
}
}course-planning-tool/webpack.config.js
Line 21 in 54612fb
const path = require('path');
const GasPlugin = require('gas-webpack-plugin');
module.exports = {
mode: 'production',
entry: {
main: './src/index.ts',
},
output: {
path: path.resolve(__dirname, './build'),
filename: '[name]-bundle.js',
},
resolve: {
extensions: ['.ts', '.js'],
},
module: {
rules: [
{
test: /\.ts$/,
loader: 'ts-loader',
options: { allowTsInNodeModules: true }, // TODO compile modules
},
],
},
plugins: [new GasPlugin()],
};course-planning-tool/src/CoursePlan/index.ts
Line 173 in f194454
private getNumDepartments = () => this.getValidationSheet().getMaxColumns();
// FIXME currently updating deletes history (probably merged cells?)
public updateEnrollmentHistory() {
this.setStatus('temporarily attaching plan to master data sheet');
this.setWorkingCopy(
this.getSpreadsheet()
.getSheetByName('Course Plan')
.copyTo(SpreadsheetApp.getActive())
);
this.populateEnrollmentHistory(false);
this.setStatus('restoring updated history to course plan');
const values = this.getAnchorOffset(
0,
0,
this.getNumDepartments() *
Terse.SpreadsheetApp.DeveloperMetadata.get(
this.getWorkingCopy(),
CoursePlan.META_NUM_OPTIONS_PER_DEPT
),
Terse.SpreadsheetApp.DeveloperMetadata.get(
this.getWorkingCopy(),
CoursePlan.META_HISTORY_WIDTH
)
).getValues();
this.setWorkingCopy(this.getSpreadsheet().getSheetByName('Course Plan'));
this.getAnchorOffset(0, 0, values.length, values[0].length).setValues(
values
);
}
private populateEnrollmentHistory(create = true) {
this.setStatus(
`${create ? 'writing' : 'updating'} course enrollment history`
);
const values = []; value: i + 1,
current: student.getFormattedName(),
});
CoursePlan.for(student);
});
// TODO make course plan inventory active sheet
}
private static putProgress(progress: Progress) {course-planning-tool/src/Inventory.ts
Line 73 in bae2b7f
const folder = this.getRootFolder().createFolder(folderName);
const row = [key, folder.getId(), folder.getUrl()];
this.inventorySheet.appendRow(row);
// TODO add advisor as viewer to newly created advisory folder
return folder;
}
course-planning-tool/src/CoursePlan.ts
Line 77 in 85b869f
.setHeight(150),
'Mockup'
);
// TODO finish mockup by opening course plan
}
public static getMockupResult() {course-planning-tool/src/CoursePlan.ts
Line 27 in 170bf60
private anchor?: GoogleAppsScript.Spreadsheet.Range;
private validationSheet?: GoogleAppsScript.Spreadsheet.Sheet;
// FIXME CoursePlan constructor should be private to enforce inventory updates
public constructor(student: Student) {
// TODO deal with GRACE course selection
this.setStudent(student);course-planning-tool/src/CoursePlan.ts
Line 139 in 40af46f
spreadsheet.deleteSheet(sheet);
}
});
// TODO make sure course list validation transfers to new spreadsheet
// TODO add access privileges
// FIXME file is getting neither moved nor shortcutted
const file = DriveApp.getFileById(spreadsheet.getId());
file.moveTo(CoursePlan.getFormFolder(student.gradYear));
CoursePlan.getAdvisorFolder(student).createShortcut(file.getId());
return spreadsheet;
}
private static getGraceEnrollments(student: Student): string {
return (
'=' +
s.fcn(course-planning-tool/src/CoursePlan.ts
Line 16 in d128efd
null
);
// TODO modify workflow to allow for updating enrollment history on existing course plans
// TODO document process for change of advisor
export default class CoursePlan {
public static readonly PROGRESS_KEY = 'course-plan';
course-planning-tool/src/CoursePlan.ts
Line 278 in 85b869f
);
}
// TODO setValue() should really be dumped into @battis/google-apps-script-helpers
private setValue(a1notation: string, value) {
const range = this.getWorkingCopy().getRange(a1notation);
Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
course-planning-tool/CoursePlan.js
Line 71 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
}course-planning-tool/src/State.ts
Line 11 in 63f4754
private static dataSheet?: GoogleAppsScript.Spreadsheet.Spreadsheet;
private static template?: GoogleAppsScript.Spreadsheet.Spreadsheet;
// TODO don't really need getDataSheet now that this is attached to the sheet
public static getDataSheet() {
if (!this.dataSheet) {
const id = Terse.PropertiesService.getScriptProperty('DATA');import { Terse } from '@battis/gas-lighter';
import CoursePlan from '../CoursePlan';
import Student from '../Student';
const P = Terse.HtmlService.Element.Progress.getInstance(
CoursePlan.PROGRESS_KEY
);
// TODO add confirmation dialog
global.action_createAll = () => {
P.reset();
const students = Student.getAll();
P.setMax(students.length);
SpreadsheetApp.getUi().showModalDialog(
HtmlService.createTemplateFromFile('templates/create-all')
.evaluate()
.setHeight(100),
'Create Course Plans'
);
students.forEach((student: Student, i) => {
P.setValue(i + 1);
CoursePlan.for(student);
});
P.setComplete('All course plans created');
};
global.helper_createAll_getProgress = P.getProgress;course-planning-tool/src/CoursePlan.ts
Line 325 in 4bd2e7c
private setPermissions() {
// TODO add access privileges
// TODO do we want to protect the course planning grid at all?
this.getFile().moveTo(this.getFormFolder());
this.getAdvisorFolder().createShortcut(this.getFile().getId());
}Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
course-planning-tool/src/CoursePlan.ts
Line 211 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup';course-planning-tool/src/CoursePlan.ts
Line 137 in 40af46f
spreadsheet.deleteSheet(sheet);
}
});
// TODO make sure course list validation transfers to new spreadsheet
// TODO add access privileges
// FIXME file is getting neither moved nor shortcutted
const file = DriveApp.getFileById(spreadsheet.getId());
file.moveTo(CoursePlan.getFormFolder(student.gradYear));
CoursePlan.getAdvisorFolder(student).createShortcut(file.getId());
return spreadsheet;
}
private static getGraceEnrollments(student: Student): string {
return (
'=' +
s.fcn(course-planning-tool/src/CoursePlan.ts
Line 205 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup';course-planning-tool/src/CoursePlan.ts
Line 203 in c03b382
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
CoursePlan.RANGE_TITLE,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_DEPT, CoursePlan.GRACE)
)
)
),
''
)
);
} else {
rowValue.push('');
}
} else {
const department = topLeft.offset(row, -1).getValue();
rowValue.push(
'=' +
s.fcn(
s.IFNA,
s.fcn(
s.JOIN,
s.fcn(s.CHAR, 10),
s.fcn(
s.INDEX,
s.fcn(
s.SORT,
s.fcn(
s.FILTER,
`{${CoursePlan.RANGE_TITLE}, ${CoursePlan.RANGE_ORDER}}`,
s.eq(CoursePlan.RANGE_HOST_ID, student.hostId),
s.eq(CoursePlan.RANGE_YEAR, year),
s.eq(CoursePlan.RANGE_DEPT, department)
),
2,
true,
1,
true
),
'',
1
)
),
''
)
);
}
} else {
rowValidation.push(
SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build()
);
}
}
values.push(rowValue); // TODO replace with actual values rather than equations
validations.push(rowValidation);
}
topLeft.offset(0, 0, values.length, values[0].length).setValues(values);
if (validations[0].length) {
topLeft
.offset(0, values[0].length, validations.length, validations[0].length)
.setDataValidations(validations);
}
const numOptions = SheetParameters.getParam(
SheetParameters.NUM_OPTIONS_PER_DEPT
);
for (var row = 0; row < numDepartments * numOptions; row += numOptions) {
CoursePlan.sheet.insertRowsAfter(topLeft.getRow() + row, numOptions - 1);
topLeft
.offset(row, -1, numOptions, values[0].length + 1)
.mergeVertically();
}
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return Terse.CardService.replaceStack(
errorCard(
`Mocked up ${student.getFormattedName()}`,
JSON.stringify(student, null, 2)
)
);
}
}
global.action_coursePlan_mockup = CoursePlan.createCoursePlan;
export default 'action_coursePlan_mockup';course-planning-tool/CoursePlan.js
Line 66 in 2d389e7
/** global: App, TersePropertiesService, TerseCardService, SpreadsheetApp */
const CoursePlan = {
actions: {
mockup() {
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Mockup'));
var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'));
sheet = spreadsheet.duplicateActiveSheet();
sheet.setName('Mockup');
const email = TersePropertiesService.getUserProperty('email');
const numYears = 5;
const replaceWithValue = (a1notation, value) => {
const range = sheet.getRange(a1notation);
if (Array.isArray(value)) {
range.setValues(value);
} else {
range.offset(0, 0, 1, 1).setValue(value);
}
if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
range.setValues(range.getValues());
} else {
range.setValue(range.getValue());
}
}
replaceWithValue('A3:E3', `=INDEX('Advisor List'!A:E,MATCH("${email}",'Advisor List'!B:B,0),0)`);
const [[hostId, , firstName, lastName, gradYear]] = sheet.getRange('A3:E3').getValues();
sheet.getRange('A3:E3').clear();
replaceWithValue('A1', `${firstName} ${lastName} '${gradYear - 2000}`);
replaceWithValue('A2', `="Advisor: "&JOIN(" ",INDEX('Advisor List'!G:H,MATCH("${hostId}",'Advisor List'!A:A,0),0))`);
replaceWithValue('B5:G5', `=ARRAYFORMULA(SEQUENCE(1,${numYears},${gradYear - 5}, 1)&" - "&SEQUENCE(1,${numYears},${gradYear - 4},1))`);
replaceWithValue('E5:G5', sheet.getRange('D5:F5').getValues());
replaceWithValue('D5', sheet.getRange('D5').getValue().substr(0, 4));
const topLeft = sheet.getRange('B6');
const validation = sheet.getParent().getSheetByName('Courses by Department');
const numDepartments = validation.getMaxColumns();
for (var row = 0; row < numDepartments; row++) {
for (var column = 0; column < numYears + 1; column++) {
if (topLeft.offset(-2, column).getValue() == 'GRACE') {
if (row == numDepartments - 1) {
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),FILTER('Historical Enrollment'!Q:Q, 'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!P:P="GRACE")),)`)
}
} else {
const year = topLeft.offset(-1, column).getValue();
if (Number(year.substr(0, 4)) < new Date().getFullYear()) {
const department = topLeft.offset(row, -1).getValue();
replaceWithValue(topLeft.offset(row, column).getA1Notation(), `=IFNA(JOIN(CHAR(10),UNIQUE(FILTER('Historical Enrollment'!Q:Q&IF('Historical Enrollment'!R:R<>""," ("&'Historical Enrollment'!R:R&")",""),'Historical Enrollment'!B:B="${email}",'Historical Enrollment'!G:G="${year}",'Historical Enrollment'!P:P="${department}"))),)`);
} else {
topLeft.offset(row, column, 1, (numYears + 1) - column).setDataValidation(SpreadsheetApp.newDataValidation()
.requireValueInRange(validation.getRange('A2:A').offset(0, row))
.build());
break;
}
}
}
}
// TODO deal with multiple planned courses from a single department
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
/*
* TODO create individual student spreadsheets
* Create in form-level folders (updating Form Folder Inventory) and add their ID and URL as columns on the Advisor List, update student access permissions
*/
/*
* TODO alias student spreadsheets to advisor folders
* Create advisor folders as needed and update Advisor Folder Inventory, update advisor access permissions
*/
return TerseCardService.replaceStack(App.cards.error('mockup()'));
}
},
}
function __CoursePlan_actions_mockup(...args) {
return CoursePlan.actions.mockup(...args);
}course-planning-tool/src/State.ts
Line 15 in 846d0e5
return dataSheet;
}
// TODO template should be a sheet parameter for visibility
export function getTemplate() {
if (!template) {
const id = Terse.PropertiesService.getScriptProperty('TEMPLATE');course-planning-tool/src/Student.ts
Line 36 in 038f880
public getAdvisor() {
return Advisor.getByAdvisee(this.hostId);
}
public static getAll(): Student[] {
// TODO filter out seniors
return State.getDataSheet()
.getSheetByName(Constants.Spreadsheet.Sheet.ADVISORS)
.getRange(Constants.Spreadsheet.A1Notation.STUDENT_DATA)
.getValues()
.slice(0, 4) // FIXME remove when testing complete
.map((row) => new Student(row));
}
}course-planning-tool/src/Student.ts
Line 39 in eb1c279
import State from './State';
export default class Student {
public hostId: string;
public email: string;
public firstName: string;
public lastName: string;
public gradYear: number;
public abbrevGradYear: number;
public constructor(data: object) {
if (Array.isArray(data)) {
const [hostId, email, firstName, lastName, gradYear] = data;
data = { hostId, email, firstName, lastName, gradYear };
}
Object.assign(this, data);
this.abbrevGradYear = this.gradYear - 2000;
}
public getFormattedName(): string {
return `${this.firstName} ${this.lastName} ‘${this.gradYear - 2000}`;
}
public getAdvisor() {
return Advisor.getByAdvisee(this.hostId);
}
public static getAll(): Student[] {
const thisYear = CoursePlan.getCurrentSchoolYear();
return State.getDataSheet()
.getSheetByName(Constants.Spreadsheet.Sheet.ADVISORS)
.getRange(Constants.Spreadsheet.A1Notation.STUDENT_DATA)
.getValues()
.slice(0, 10) // FIXME remove when testing complete
.map((row) => new Student(row))
.filter((student) => student.gradYear != thisYear); // TODO confirm filtering out seniors
}
}course-planning-tool/src/CoursePlan.ts
Line 373 in fd5b009
values.push(rowValue);
validations.push(rowValidation);
}
this.getEnrollmentOffset(0, 0, values.length, values[0].length).setValues(
values
);
this.replaceFunctionsWithDisplayValues();
this.moveToStudentCoursePlanSpreadsheet();
if (validations[0].length) {
this.getEnrollmentOffset(
0,
values[0].length,
validations.length,
validations[0].length
).setDataValidations(validations);
}
this.insertAndMergeOptionsRows(values[0].length);
// TODO deal with GRACE course selection
// TODO add advisor notes (protected)
// TODO add studies committee notes (protected)
// TODO add college counseling notes (protected)
// TODO honor number of comment blanks param
this.setPermissions();
}
public static createMockup(student?: Student | string) {
student && State.setStudent(student);
student = State.getStudent();
const plan = new CoursePlan(student);
SpreadsheetApp.getUi().showModalDialog(
HtmlService.createHtmlOutput(
`<a href="${plan.getFile().getUrl()}" target="_blank">${plan
.getFile()
.getName()}</a>`
),
'Created'
);
}
}
global.action_coursePlan_mockup = CoursePlan.createMockup;
export const Mockup = 'action_coursePlan_mockup';
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.