Very few tech-related experiences come close to the nerdy thrill of cracking that spreadsheet formula that’s been puzzling you all week. On Thursday afternoon, I managed a small feat that will streamline communications in my school: synchronising school data with student email addresses to create shared contact groups using Mac Numbers, Google Apps Education Edition, Moodle and Shared Contacts (an app from a German company by the name of Floreysoft).
For some years now, it’s been puzzling me that while students have email addresses and schools have plenty of student data, never the twain have been able to automatically meet. How convenient would it be for teachers if the classes that are created in schools’ databases could then translate into shared contact groups to enable teachers to easily subscribe to their own student class lists! Ah, but the simple things in life so often elude us, do they not?
What’s made this all-too-difficult in the past has been our school system’s use of antiquated email software by a company named Editure, which did not allow for a shared directory, searchable addresses, let alone groups that could be created. The result of this was that while students did use their email addresses to contact their immediate friends, staff were unable to find an address easily, much less create a list of addresses.
Companies like Google have – for at least the last eight years – brought sophisticated ways of searching email archives and directories. With the flexibility of importing and exporting CSVs, all that is required is a spreadsheet program that can create the lists required.
Enter Mac Numbers and a few simple steps.
- From my school’s student management system (a horribly clunky and much out-dated MS Access-based program with the misleading title of ‘General Access’) I manage to export a student list with core classes and homerooms, which becomes the ‘General Access’ sheet like so:
- From Moodle, I manage to export a list of student names and Google Apps email addresses – let’s call it the ‘Moodle and Email Addresses’ sheet:
- Using the CONCATENATE formula, I string first and last names together on both sheets, to make unique search strings.
- I then create a third sheet – call it ‘The Works.’ It reads the names, classes and homerooms directly from the General Access sheet and will do the work of cross-referencing these against the Moodle data, to give us a list with all the fields we want – in this case, names, classes, homerooms and email addresses. I also CONCATENATE names on this sheet in a separate column.
- The LOOKUP formula then searches both the Moodle and General Access sheets to match the concatenated names and produce the Google-style CSV for creating the shared contacts groups.
- Having made a sheet with all the data, the ‘Reorganise’ tool in Mac Numbers lets us display only the email addresses for, say, the 8.3 class or the Mackillop homeroom. Hiding lets us display whichever rows and columns we need for whichever purpose. Hooray!
At this point I feel about as nerdy as a guy in a lab coat pouring fluorescent green liquid into a test tube. Well, not quite – but close.
If anyone is interested in this mock-up copy of the Mac Numbers spreadsheet, drop me an email or comment and I’ll pass it on. After all, who wouldn’t give their right arm for Celine Dion’s class, homeroom AND email address?!