by David Miles

I’ll start by saying that I’m a spreadsheet addict. If a task involves any sort of repetitive manipulation of data, my first instinct is always to reach for a spreadsheet. And these days, tools such as Google Spreadsheets incorporate many of the capabilities of a database, and also incorporate scripting languages with which we can create powerful programmes to manipulate our data beyond what can be done within a bare spreadsheet. Pull all of this together, create live & constantly updating links to external datasets, and we can create some very powerful and robust solutions which save time and minimise errors caused when busy humans engage in detailed oriented tasks where accuracy matters.

The way I see it is: anything which can be automated and done by a computer probably should be, enabling the humans to spend their time working on the other stuff that can’t be automated.

A note on time investment for projects such as these. Building solutions takes time, sometimes more than the time you’d need to invest in the task itself each year. Think of the time invested in creating a solution not just in terms of your own time, but also in terms of the impact it has on other people’s use of time. By automating solutions, you create extra time for many people. You also learn a lot which lends itself to solving other problems. As I’ve spent more time approaching problems with this mindset, I’m finding I actually have more and more time available and am able to create solutions more quickly for a range of other issues. It’s a win-win, virtuous circle, and well worth the initial investment.

Workflow

Every one of these projects basically follows the same process

1. Get the data from ManageBac
2. Manipulate it as necessary within spreadsheets
3. Push it back into ManageBac, or out to wherever it is needed.

In this article, I outline one of the processes I created to streamline and automate setting ManageBac up for the new year. This process takes advantage of the ManageBac API to pull data directly out of the system into spreadsheets, where it is then manipulated, added to, and then re-imported into ManageBac. It cuts down significantly on setup time, freeing me and my team to work on various other time-sensitive tasks, and enabling our faculty and leadership teams to take their time on making certain decisions and getting set up for the new school year.

Getting your hands on the data from within ManageBac

I originally started writing my own Google Apps Script (GAS) code to access the API, but then Adam Morris shared what he had put together which was far, far superior to what I had built, and so I have since then transferred over to using the tool he created. You can learn more here. I’m going to call this sheet MBOALink as I talk about it below.

Since you’ll want to do lots of things with this data, and probably develop tools which other people will use, you’ll want to pull the data from your MBOALink file into other sheets. You will also want to set up time-based triggers in MBOALink which will ensure the data you are working with is fresh and reliable.

I pull the data into other Sheets in two different ways, depending on what I’m trying to achieve.

1. I use the =IMPORTRANGE() function in Google Sheets which enables me to pull the live data from my MBOALink file, and automatically updates whenever the source data changes. This is very effective if what you need to do is manipulate it, however it is problematic if you need to create additional fields (for example, you want to assign students to classes). For that, there is option 2
2. I use Google Apps Scripts to pull in the Student IDs from MBOALink and push them into a spreadsheet, and then to keep the list of IDs up to date, appending new IDs to the end of my lists. This ensures that each row in my Sheet is uniquely tied to a specific student. I then use option (1) above to pull in live data, using =VLOOKUP() to match the data to the list of Student IDs I already obtained. Because each row is now uniquely tied to a specific Student ID, we can add data to each row and not worry about what will happen if a new student is added to the list.

Setting up the school year

There are three major ManageBac set up tasks which are required at the start of the school year

1. Create the new classes
2. Update the classlists
3. Import the timetable

The project outlined here focuses on the second of these tasks - updating the classlists. It is an example of how a template can make life easier not just for us but also for the various stakeholder groups involved in this process.

Classlists are often very dynamic at the start of the year, often needing updating on multiple occasions during the first few weeks as enrollment changes, decisions are revisited and adjusted, and mistakes corrected. This spreadsheet-based process enables me to update classlists in just moments, whereas doing it manually would take a large block of time every time changes needed to be made.

You can view the file and make a copy if you wish to try it yourself. It contains a link to demo data. Note that if you make a copy and try to use this with your own data, you’ll need to make your own MBOALink file and adjust various queries to make it work properly.

MB ClassLists setup - demo file

What does this file do? 

It enables the various stakeholders to input necessary details into a specific structure which makes sense to them. In the background, this data is then automatically manipulated through formulae into the import format required by ManageBac. This can then be very quickly imported into ManageBac, updating Advisors and class memberships. 

Under Extensions, Apps Scripts you’ll find the associated code, and there’s an Instructions sheet there which explains how to set up for the new school year.

How does it work?

1. There is a SettingsForClasses sheet which contains data that has to be updated manually at the start of each year. This contains sets of named ranges (a way to create dynamic ranges which can be named and referred to in formulae, making them easier to understand and to troubleshoot). 

Note that in the screenshot below there appear to be two columns for each class, for example English & Class ID. 

The first column contains the class names, which need to be added manually. They are sorted into scheduling blocks, which helps ensure there are no clashes when assigning students, and are named in ways the aforementioned stakeholders will understand. Each column is a named range, which is then connected to via a Data Validation (See next step below)

The second column contains the corresponding Class ID, which is pulled in automatically from the MBOALink file. There are formulae elsewhere which will search the English column and then output the corresponding Class ID - readying the data for import into ManageBac
The Class ID column has to be updated manually, selecting from dropdown lists which are populated by data validations which limit the classes available to the year group we’re dealing with.

The screenshot below represents the classes for Grade 12. There is a similar set of data for each grade level.

1. There is a tab for each year-group. The relevant set of Student IDs are automatically written into each sheet via Google Apps Script, and then key demographic data is pulled in from OpenApply and ManageBac into the yellow columns, which enables us to check and confirm that these details are correct. These fields are protected so end-users can’t change anything directly here.
   The Homeroom advisor is updated via the white column, and class allocations are made in the blue-headed columns. (We also create a Homeroom class, the homeroom column updates automatically once the advisor is assigned - the error showing in the screenshot below is because there is no homeroom advisor at the moment)
   Relevant staff members are given access to this file to upload the necessary information, and I wait until they tell me the information is complete before I do any uploading.

1. The file opens by default to the Dashboard tab, which contains links to the year group tabs, and includes tracking so we can make a note when a process has been completed. Grade levels are updated one at a time, selected in the office area below, and then there are links to the sheet which contains the necessary data to be exported to ManageBac, and links to the relevant pages within ManageBac where the imports take place.
   Data is manually downloaded from the relevant sheets here in .csv format, and then uploaded into ManageBac as required. If there are mistakes in the data, we wait until they have been corrected, and then we repeat the download/upload process.

And that’s basically it. Even though we still refine some of the code each year as we’ve used this tool, it has significantly simplified the process of setting up classes. Once we have mapped the classes and names for the new school year, the only thing we have to do is download a couple of files and upload them, for each year group - which takes only a couple of minutes. If there are changes made, we can do this again in moments.

Note that we only use this process for the setup period at the start of the school year. Once that is over, changes are made manually by PAs or Programme Coordinators.

Conclusion

The process outlined here helped us enormously with one of the three key tasks. Over the past few months, we have refined the automation of the other two setup processes mentioned.

We can now reliably manipulate the timetable data from where it is created (we use ascTimetables) into something that ManageBac can handle. Our Class info is automatically generated from the timetable data, including Class IDs, and we can also quickly update the schedules when changes are made (and they are made a LOT here!)

We have a long list of tasks at this time of the year, many not related to ManageBac. These templates have dramatically reduced our stress levels and made our workload manageable.

If you are not currently automating these processes, I highly recommend you find time to learn how to do so before the start of the next school year!