Skip to main content

How to automatically create Google Classrooms for an entire school using RosterSync and Capita SIMS

In this guide, I'll look at how to automatically provision all your Google Classrooms for your school based on data held in SIMS (the most common UK MIS).

To carry out the following you need to be a super admin on your Google Apps domain. The end result will be the creation of a Google Classroom for each class in the school, pre-populated with a lead teacher and students.

Step 1 - Create a SIMS report to pull off all students and the classes they take.

You will need a report that contains the student forename, surname, Google Apps email and all the classes they take. So a pre-requisite is to have all your Google usernames on SIMS. The fields I use are shown below:
Upload the resulting spreadsheet and convert to a Google Sheet. 

Step 2 - Create a SIMS report to pull off all teachers and the classes they teach.

You will need a report that contains work email, full name, class name and subject of the class. See my report below:
In excel - use the 'remove duplicate' option in the data section to remove duplicate rows.
Again - upload the resulting spreadsheet and convert to a Google Sheet.

Step 3 - setup Rostersync

Create a new Google Sheet. Add the sheet add-on 'Rostersync'. It will prompt you to define some data sources - the two spreadsheets you have just made. 
Set the student spreadsheet as the 'Enrollment' data source.
Set the teachers spreadsheet as a 'joined source'. When you do this you need to define the connection between the two data sources - in this case the 'Class name' - 
You then need to tell RosterSync which headings in the two spreadsheets should be used to populate various fields. So it should end up looking something like:
The black bars are just where I've blacked out actual names it pulls from my spreadsheets.

Step 4 - the easy bit!

Simply follow the instructions that RosterSync provides - really just clicking the blue button to move onto the next stage. The last stage, where it actually creates the Classes, can take a few minutes depending on how many classes you have - but it does get there eventually. Once  complete, teachers have an option of accepting the class - a button appears that they click on. Once they click the button - the class is live. 

This will only put one teacher into a class. Whoever gets put as the lead teacher needs to add secondary teachers.

So after all that you get:


These are some of my auto generated classes. You can keep class lists up to data by rerunning the student report. 




Popular posts from this blog

Delete a specific email using GAM

If a user send an inappropriate email to a loads of people or get stung by some sort of email exploit you can quickly delete the email from all of the recipients using a GAM command.
Step 1 - get the email header Go into Google Vault and search for the offending user or someone known to have got the message.
Click show details and grab the email ID. This will be a long string of characters followed by @mail.gmail.com
Step 2 - find out who has the email Go into Google Vault and find the original message sent by the offending user. Look at the details to see who got it. Copy the list and dump it into a spreadsheet. Clean up to just a list of emails with a column header 'mail'. Save as a csv file.
Step 3 - delete messages with GAM Put your CSV file in your GAM folder - this e.g. assumes its called mail.csv
Run:
gam csv mail.csv gam user ~mail delete messages query rfc822msgid:MESSAGEIDHERE doit

The alternative nuke option is:
gam all users delete messages query rfc822msgid:MESSAGEI…

How to push bookmarks to users in Chrome via the management console

With the release of Chrome and ChomeOS 37 an update to the management console has arrived that allows you to push bookmarks to users.

Under Device Management > Chrome > User Settings > User Experience you will now find the option to add managed bookmarks.


In the example above, the bookmarks are applied to the sub-OU of 'students' - so all our students will get these bookmarks. Simply add your url and the bookmark name, click the + and save. These will appear in a folder called 'yourdomain bookmarks' - see below:



Be aware that to get these bookmarks applied on a Windows/OS-X device the user must be signed into Chrome. Update: if you install the latest group policy template you can push the bookmarks via policy on PCs - details are given here.
Video Guide:

How to do vertical text in Google Spreadsheet

After many years of waiting, this is now a feature of sheets - so this little hack is no longer needed. Options now are:


One limitation of Google Spreadsheets is the lack of a vertical text option - handy if you have very wordy headings to columns. This is being worked on - but a little work around for now is the use of the following formula:

=ARRAYFORMULA(CONCATENATE((MID( "English Lit"; ROW(INDIRECT("YY1:YY"&LEN( "English Lit" ))); 1)&CHAR(10))))

Don't worry how it works - just copy and paste - change both english lit's to whatever you want.