Timetabling with aSc and ManageBac

Timetabling-ASC-ManageBac.md

Timetabling with aSc and ManageBac

This gist will explain how I built a schedule with aSc timetables, and used the exported XML data to turn it into CSVs compatible with ManageBac's bulk imports for classes and timetable uploads.

This is not intended to provide a complete end-to-end solution, but to provide timetablers with general strategies with specific examples. In particular, the use of aSc subjects and divisions, how they should coincide with complex scheduling needs, and then how to get it to a "flat" output for csv upload. There are a variety of ways to specify a timetable with aSc; this solution only uses constraints provided by subject and division specifications.

This example uses a 7-Day Timetable (Day 1, Day 2, … Day 7), where each day has four blocks (Block 1, Block 2, … Block 4), and each block can be divided into two periods. (Block 1 consists of periods 1 and 2, Block 4 consists of periods 7 and 8). There are seven subjects, plus some school-specific subjects. The total number of placements to make are 7 x 8 = 56 placements per grade, for grades 6 - 12.

This solution uses Google AppsScripts and Google Spreadsheets.

Problem Statement

The aSc Timetabling program builds a timetable given specified subjects, teachers, and classes, complete with room allocations. It also can export that information as an XML structured raw text file.

ManageBac is a learning management system that accepts bulk import of classes via csv, which needs to be formatted like this so that there is a standard-level class, and high-level class produced as such:

| Class ID          | Year     | Group    | Subject          | Name | Level | Section | Teacher E-mail | Description |
| ----------------- | -------- | -------- | ---------------- | ---- | ----- | ------- | -------------- | ----------- |
| G11-AT-CS-2122-HL | Grade 11 | Sciences | Computer science |      | HL    | AT      | a.teacher@     |             |
| G11-AT-CS-2122-SL | Grade 11 | Sciences | Computer science |      | SL    | AT      | a.teacher@     |             |

The above describes a course with two levels: HL and SL level, taught by the same teacher. Note that the HL and SL are indicated at the end of the class ID, after -2122 academic year. (The AT stands for the section, which are the teachers initials.) The level of the class is at the end for readability; in reality it is put after the subject, in this case CS and before the academic year.

A standard-level class meets three times a rotation, while a high-level class has an additional block of class for extension in the curriculum. In this school, there is a ManageBac class for each level. Some schools might combine the HL and SL classes into one, but ManageBac used to have some limitations preventing us from doing so. (Though they have since been rectified; see Appendix B for more info.)

In addition to the above CSV, ManageBac also requires a CSV that has similiar information for the same class. Below, blank lines are for readability:

| Class ID          | Day     | Period | 
| ----------------- | ------- | ------ | 
| G11-AT-CS-2122-SL | 1       | 1      |
| G11-AT-CS-2122-HL | 1       | 1      |
| G11-AT-CS-2122-SL | 1       | 2      |
| G11-AT-CS-2122-HL | 1       | 2      |

| G11-AT-CS-2122-SL | 3       | 3      |
| G11-AT-CS-2122-HL | 3       | 3      |
| G11-AT-CS-2122-SL | 3       | 4      |
| G11-AT-CS-2122-HL | 3       | 4      |

| G11-AT-CS-2122-HL | 7       | 5      |
| G11-AT-CS-2122-HL | 7       | 6      |

As can be seen, the SL and HL classes share the same timetable information three times a week, and there is fourth additional lesson. Since a block consists of two periods, ManageBac needs two rows per block, for each level that applies.

Building the timetable

Here is what a basic timetable could look like, when starting out. See appendix for legend of subject names and their corresponding numbers (which are the same as the IB):

The above shows a potential Grade 12 timetable completely filled-out — and where corresponding Grade 11 classes are necessarily combined. The numbers represent subjects, B blocks are reserved for school-based offerings (such as assemblies or a Wellness programme), and C blocks represents a "Core" block. Meanwhile, the classes of Grade 11 that are filled in illustrate the first timetabling problem to solve, which is that both Grade 11 & 12 Group 6 Arts happens at the same time (with the same teacher). The programme is designed this way because in the arts, collaboration is key and the teacher is prepared to have both grades in the same class. It also allows the school to resource art teachers' allocations more effectively.

Increasing the complexity of our example, the school also chooses to offer additional non-Arts subjects as an Elective (E) selection. Instead of an Art, they can choose either Business Management (whose natural group is Group 3) or Chemistry or Computer Science (whose natural group is Group 4). They are taught by the same teacher in G11 and G12, but unlike arts teachers cannot teach it at the same time across grades, and so cannot be timetabled at the same time, as would be depicted here:

The classes in the vertical columns with Grade 11 classes placed cannot be timetabled this way, as the E group is taught by the same teacher, but G11 and G12 are not combined (as it is for Group 6).

Given the flexiliblity of the timetable, students can choose a medical pathway of taking two sciences (usually Biology and Chemistry), or students can potentially pair up Business Management with Economics courses as well. Yet, those two courses happen at the same time as Arts, which is a combined class with the same teacher, which means they cannot be timetabled together. Flexibility for the student means complexity for the timetabler.

The mentioned, but not fully explained, C and CP blocks poses an additional problem down the line. They are the "DP Core" C and "CP Core" CP classes represent the "Core" of both programmes which are offered at the school. The problem is that there are two blocks available to them, which suffices for the DP Core. The CP Core, however, requires one additional block, for a total of three blocks. That means we need to explore how to empty one of the columns, somehow.

Optimizing the timetable

How can we use some clever timetabling tricks to collapse things so that we we can meet the timetabling requirements above?

Optimization #1: Identify rare combinations

Let's see if we can back up different subjects against each other, in that way creatively finding a way to collapse how many blocks we need to offer subjects 1-6, giving us an extra block somewhere. Are there rare combinations of subjects, especially at HL?

If a student is taking Maths HL, chances are they aren't going to be taking an Arts class also at HL. Another rare combination is doing Languages HL and Arts HL.

Thus, we can move the HL classes to be at the same time as one of the Arts block, with the disadvantage that the school cannot offer that combination. Since it's rare, it is considered a fair tradeoff. In any case, if the school can attract a student seeking such a combination, we can look at which combinations are not required, and follow a similar process with those combinations instead of the specific ones here.

Optimization #2: Use nomenclature to identify blocks

Let's use + along with the subject number to indicate that, for example, 5+ is when the high level subject 5 (Mathematics) is to occur. Let's also use E to indicate Electives, which are subjects that, to the students, are grouped in a kind of "virtual" Group 6. The subjects offered for E are two sciences (Chemistry and Computer Science) and a humanities (Business Management), which can be chosen instead of an Arts.

Optimization #3: Think of each grade having two sides with virtual subjects

Since we essentially need to mix blocks around and back various subjects against each other, let's act like each block for each grade has potentially two placements. In many cases, the two placements may not be significant, in which case we can merge the subject number. Where significant, however, we can place two different subjects.

Result:

Let's get a picture of before and after:

Before

Throughout all of our steps below, we have to have each subject (6, E, 2) have a total of four blocks. One of them has to be an HL class, which is donated by a +.

Step 1

It starts off in G12 by backing group 6 into the Grade 12 Group 2 HL block time (Day 1 Block 3), where the Grade 11 will have Group 6 and E placed. Then for the original column, a mirror image of the newly adjusted column is made, so that Grade 12 is taking either E or 6. We have five blocks of 6, we will take out the extra one on Day 4, and denote that with an /.

(Note, not aligning the 6+ into the same column across grades is simply to indicate that one block has to be considered the HL block. They cannot be on the same day for G11 & G12, so as balance the number of students present in a class during the timetable. In other words, the HL block cannot be placed together; it doesn't matter where though. However, teacher preference may vary.)

Step 2

Looking at Day 4 Block 2 and 3, we remember that Group 5 HL can be placed during Group 6 time, so let's swap the E with the 5, giving the E a block in the timetable to itself. We have to have identify the Block 3 as 5+. We will have all of the number of placements for subjects adding up to 4, and with that / block we'll add the CP at that place, as we require three blocks of that.

We have succeeded in finding a way to meet the requirements of offering the Electives, and the Arts, where Arts are combined across grade levels, but the Electives are not.

Step 3: Extension exercise

The school has an additional requirement. It needs two of the three CP blocks to also be combined across G11 & G12. No sweat. From the experience we have from above, we:

• Identify a combination that is rare.
• Combine the two subjects that are rare, and make two placements at each of those blocks
• Swap the placements

In our case, no students whoe are enrolled in the Careers-related Programme (and thus need the CP Core CP class) takes a language, which is Group 2. So let's use Group 2, again, as our pivot point:

And now it becomes an exercise of how to proceed with programming the timetabling software appropriately.

Map the nomenclature to aSc's assignments

How do we translate this to aSc's timetabling needs? When you build classes, you need to assign a teacher, a subject, and a division. Divisions are an important use in aSc timetable, as this is the primary way to indicate to the software that certain classes are supposed to be held at the same time. Each grade ("class" in aSc, which is confusing), can have any number of divisions, and within each division are the classes that happen at the same time. This is a screenshot of a real application of divisions in just one Grade:

Please be wary of our terminology. A "class" in aSc is really normally called a grade. A division is like a class. Also note the use of _ in the division names, which is explained below.

Let's consider how to layout our divisions in each grade. For example on Day 1 Block 3, we need two divisions in Grade 12, and two divisions in Grade 11. The 6+ (which is the Arts HL class) will need to be combined with the 6 division in Grade 12.

| Grade 11 Divisions   | 
| -------------------- | 
| 2+        | 6+       |

| Grade 12 Divisions   | 
| -------------------- | 
| 6         | E        |

(6 and 6+ are a combined class, thus ensuring the entire column above for Day 1 Block 3 are timetabled at the same time)

In reality, because we have three Group 6 "Arts" classes (Visual Arts, Music, and Theatre), and two Group 2 classes where HL is offered (Chinese and Korean), we'll need to write the divisions like this:

| Grade 11 Divisions                                               | 
| ---------- | --------- | -------------- | ----------- | -------- |
| ...                                                              |
| Chinese HL | Korean HL | Visual Arts HL | Theatre HL  | Music HL |
| ...                                                              |

| Grade 12 Divisions                                               | 
| -------------- | ----------- | -------- | ---------- | --------- | 
| ...                                                              |
| Visual Arts HL | Theatre HL  | Music HL | Chinese HL | Korean HL |
| ...                                                              |

Okay, but that is just for one column in our layout as given in the screenshots above. In order to include all of the Group 6 classes, you also need to likewise define divisions similarly for Day 2 Block 3, Day 4 Block 2 & 4, and Day 5 Block 3. Since we want to use the names of our divisions in our output file, but since aSc requires us to use unique names (per grade) for divisions, the author chose to use the _ as a suffix, which is then removed through code when output.

In addition to the divisions that we need to layout in aSc, we also need to assign a subject. In aSc, whereas the divisions determine what classes are placed in the timetable on the same Day and Block, subjects are how you tell it to best distribute across the timetable. You want to give every Group like 1 and E in the timetable that is supposed to be spread out — so that it doesn't get clumped together three days in a row, for example — the same subject in aSc. They are essentially best thought of as being virtual subjects. The name you give it, which doesn't have to be unique, can be used to output the intended correct subject group name on ManageBac.

In order to provide all of the information that is needed in the output, the author chooses to write subjects like so:

| Name         | Comment     |
| ------------ | ----------- |
| ...                        |
| DP Sciences  | Biology, …  |
| DP Sciences  | Chemistry, …|
| DP Arts      | Theatre, …  |
| ...                        |

The easy part is understanding that the "DP" prefix indicates the name of the csv file that will be created. ManageBac's CSV upload is done per programme, so we need a way to mark each subject as to which tab it will eventually fall under.

Harder is understanding the reason we need need two different subjects sharing the same title "DP Sciences". It's because we need two virtual DP Sciences, since there are two groups of science classes offered in the timetable. Chemistry in E should be associated to one of these, and the natural 4 group, such as Biology, should be associated with the other one. Likewise all of the E assignments should share the same subject, and group 4 the other one. If we assigned all of those classes to just one subject, the program would attempt to distribute Group 3 and E (representing six blocks) evenly, when in fact it would make perfect sense for a student to have Group 3 and E on the same day; that wouldn't be considered poor distribution.

Before Generating

In our example we have to cleanly get to the magic number of 56 for every grade, as that is how many placements there are per rotation. The count column of this screenshot has to read 56 and not *56, nor 64 … just fifty-six.

We're good to go. With this, we know that generating will probably work, and if it doesn't, it'll be because of some complication with the complexity of the timetable itself, and not because of inconsistent input error.

An * in the count means either (a) one of the divisions has no class assigned to it, or (b) there is an imbalance of assignments across the divisions (two many or two few).

This is the key to generating. The author has learned not to trust the generator at all until and unless you have reached the point where the count column has the correct number, no asterix.

Exporting with XML, converting to Google Sheets

After generating properly, you then have to get the data into a Google Spreadsheet so you can take advantage of the "download as csv" feature.

While there is some code in the ShapeData below, there may be some work involved in getting it to production on your system. This section describes the process.

The aSc timetable software exports raw XML, which is just text. A Google Doc is then created and the XML text is placed into there. The author does this manually via copy'n'paste, but automation could be achieved by having a trigger run on save into a particular local directory, which then executes an upload. The author didn't think it was worth investigating, as a service account would probably have be created and maintained for authentication purposes.

I then run a command shapeData that is written with AppScripts, and all it does is convert the XML into tabs that holds the raw data output by the program. Here is an example of the result of the "subjects" tab:

There will be a tab for each entity, such as lessons and classrooms that the programme exports. Now we need to tie it together. One solution could be to use database like queries in order to join the information together; the author stuck to simple VLOOKUP solution:

Now that we have all the IDs lined up, another VLOOKUP step and we derive the corresponding values that we need:

Okay, now we got the values, we just need a script to walk through it and save tabs onto a spreadsheet. The idea is that each tab of the spreadsheet, we can use the "Download as csv" feature to use on ManageBac.

After running updateTimetableForMB, we get the following in the DP tab for example:

And then, building on that, using simple query formula, we can derive the two CSVs we need, one to create all of the classes (save the G11 -> G12 which can just be migrated), and another one to upload the timetable data.

Conclusion

Given the flow above, we have built a way whereby we can regenerate the timetable in aSc, and quickly have the cooresponding CSV downloads ready for upload to ManageBac.

xAppendixA

## Appendix

These subjects coorrespond in this case to the following (same as in the IB groupings):

1. Literature & Languages (English, French …)
2. Languages (Spanish, Chinese, …)
3. Humanities (History, Economy, …)
4. Sciences (Physics, Biology)
5. Mathematics
6. Arts (Visual Arts, Theatre, Music, …)

xAppendixB.md

Generally, teachers would prefer to combine HL and SL ManageBac classes to ease maintenance. However, there were some implications to doing that:

1. Students of one class all had to be assigned every task, but HL and SL students would often do different assignments
2. Weightings might be different in SL and HL, and there wasn't a way to reflect that
3. For attendance, SL students would have to be marked as "Other" during the HL block

The first two have been rectified, and #3 isn't much of an issue. There is now a student differentiation setting in MB that allows teachers to assign different tasks to different students. And there is now a weightings set feature, so you can have one set for HL and one set for SL.

xShapeData.gs

function shapeData () {
  var xml, root, ss, result = {}, sheetTitles = [];
  xml = getData_('<XML Document ID>');
  ss = SpreadsheetApp.openById('<target spreadsheet>');

  root = xml.getRootElement();

  for (const child of root.getChildren()) {
    const rows = []; 
    const tab = child.getName();
    Logger.log(`---${tab}---`);
    for (const grandchild of child.getChildren()) {
      const obj = {};
      const id_flags = [];
      for (const attr of grandchild.getAttributes()) {
        const name = attr.getName();
        const value = attr.getValue();
        if (name.endsWith('ids') && value.includes(',')) id_flags.push(name);
        obj[name] = value;
      }
      if (id_flags.length === 0) {
        rows.push(obj);
      } else {
        if (id_flags.length === 1 && id_flags.includes('teacherids') ) {
          // simple heuristic; just take the first one (yikes!)
          obj.teacherids = obj.teacherids.split(',')[0];
          rows.push(obj);
        } else if (id_flags.length === 2 && id_flags.includes('classids') && id_flags.includes('groupids') ) {
          const classids = obj.classids.split(',');
          const groupids = obj.groupids.split(',');
          if (classids.length !== groupids.length) {
            Logger.log("Unequal number of groupids and classids");
            Logger.log(obj);
          } else {
            //
            const newObj = {...obj};
            for (let idx = 0; idx < classids.length; idx++) {
              newObj.classids = classids[idx];
              newObj.groupids = groupids[idx];
              rows.push({...newObj});
            }

          }
        } else {
          //Logger.log(obj);
        }
      }
    }
    
    // uses "dottie" library to convert an array of objects to just plain spreadsheet rows ... very handy
    // https://classroomtechtools.github.io/dottie/
    result[tab] = dottie.jsonsToRows(rows);
  }

  for (const [sheet, values] of Object.entries(result)) {
    // check if sheet exists, if not make it
    let tab = ss.getSheetByName(sheet);
    if (tab == null) tab = ss.insertSheet(0, sheet);  // insert at the start
     
    // write it
    // TODO: Should clear it first, so that when it writes it won't have leftover stuff at bottom
    tab.getRange(1, 1, values.length, values[0].length)
       .setValues(values);
  }
  
}

/**
 * Returns the raw XML of the document at `ID`
 */
function getData_(id) {
  var doc, body, text;
  doc = DocumentApp.openById(id);
  body = doc.getBody();
  text = body.getText();
  return XmlService.parse(text);
}

xUpdateTimetableForMB.gs

function updateTimetableForMB(destId, sourceId) {   
  const ss = SpreadsheetApp.openFromId('<target ss>');
  const data = ss.getSheetByName('JoinValues').getDataRange().getValues();
  const tabs = {'MYP':[], 'DP': [], 'MS': [], 'HS': [], 'CP': []};

  for (const row of data.values.slice(1).filter(r => r[0].length>0 && r[5].includes('Grade'))) {
    const [lessonid, subject_phrase, teacher, section_phrase, division_phrase, grade, classroom, day, period] = row;
    const teacher_email = teacher.startsWith('#') ? '' : `${teacher}@igbis.edu.my`;
    const section = section_phrase.startsWith('#') ? '??' : section_phrase.toUpperCase();
    const [_, gr] = grade.split(' ');
    const gradeNum = parseInt(gr);
    if (gradeNum < 6 || gradeNum > 11) continue;

    const programme = subject_phrase.split(' ')[0];
    const subject_group = subject_phrase.split(' ').slice(1).join(' ').split('_').join('');

    let title = '';
    // take out underscores and dashes completely
    const [division, level] = division_phrase.split('_').join('').split('-').join('').split(' ');

    // division "/" in level means separated, needs to make additional items
    // ampersand "&" means shared class in MB

    const grde = `G${grade.split(' ')[1]}`;
    const subject = subject_mapper(division);
    const uniq = `${grde}-${section}-${division}{LEVEL}-2122`.toUpperCase();
    const description = '';

    if (programme === "MYP" && (level && level.length > 0)) {
      // myp phases
      title = `Select phases for ${subject}: ${level}`;
    }
    if (programme === "DP" && (subject_group == "Group 2")) {
      title = `Select B or abi for ${subject}: ${division_phrase}`;
    }

    let append = [];
    if (programme === 'DP') {
      if (level && level.includes("/")) {
        for (const lvl of level.split('/')) {
          append.push([uniq.replace('{LEVEL}', lvl), grade, subject_group, subject, title, lvl, section, teacher_email, description, day, period, classroom]);
        }
      } else {
        append.push([uniq.replace('{LEVEL}', ''), grade, subject_group, subject, title, level, section, teacher_email, description, day, period, classroom]);
      }
    } else if (level && level.includes('&')) {
      if (programme === 'DP') {
      // combining whatever levels is actually the default, so no level
        append.push([uniq.replace('{LEVEL}', level.split('&').join('')), grade, subject_group, subject, title, level.split('&').join(''), section, teacher_email, description, day, period, classroom]);
      } else {
        append.push([uniq.replace('{LEVEL}', ''), grade, subject_group, subject, title, section, teacher_email, description, day, period, classroom]);  
      }
    } else {
      // no level provided
      append.push([uniq.replace('{LEVEL}', ''), grade, subject_group, subject, title, section, teacher_email, description, day, period, classroom]);
    }

    for (const app of append) {
      tabs[programme].push(app);
    }
  }

  for (const [tab, values] of Object.entries(tabs)) {
    const sheet = ss.getSheetByName(tab);
    sheet.getRange(1, 1, values.length, values[0].length)
         .setValues(values);
  }

  
}


/** 
 * Function that standardizes subjects from the mess that is in the divisions area
 */ 
var subject_mapper = (before) => {
  if (before.toUpperCase().startsWith('AMP')) return 'AMP';
  if (before.toUpperCase().startsWith('ESOL')) return 'ESOL';

  const handle = before.replace(/[^a-z]+/g, '');
  return {
    science: 'Sciences',
    engla: 'English',
    phe: 'Physical and health education',
    visarts: 'Visual Arts',
    hostnations: 'Host Nations',
    indsoc: 'Integrated Humanities',
    tok: 'Theory of Knowledge',
    mathstandard: 'Standard mathematics',
    mathextended: 'Extended mathematics',
    math: 'Mathematics',
    psych: 'Psychology',
    geo: 'Geography',
    bio: 'Biology',
    perfarts: 'Performing Arts',
    designtech: 'Design technology',
    econ: 'Economics',
    designech: 'Design technology',
    chem: 'Chemistry',
    busman: 'Business Management',
    mathaa: 'Mathematics: analysis and approaches',
    mathai: 'Mathematics: applications and interpretation',
    cpcore: 'Personal and professional skills'
  }[handle] || (handle[0].toUpperCase() + handle.slice(1));
}

This is fantastic! ..And thanks for putting it together.
For new aSc schedulers, I would add that all Year Groups can/should be done this way. For example, in a school with two classes in Year 7 (7.1 and 7.2), you set up a Year 7 class and then use Divisions to divide the classes into different groups. If all the students in 7.1 are together for Humanities (Hum1) and all the students in 7.2 are in a second Humanities (Hum2) class, the division would be Hum1 and Hum2. If they are mixed by ability, choices or level, use a simple catchphrase to help you on the Divisions: "If they are in the same line, they are at the same time". This also means that you can have a division that has 7.1 in ICT and 7.2 in PE, if you wish to have different subjects take place at the same time.
Hope this helps even more :)