Skip to main content

How to provision Google Classroom using GAM and sync to Capita SIMS

This is a guide (or documentation for my own use!) to the provisioning of Google Classrooms across a large domain and how to achieve ongoing sync of student members. The advantages of doing this are:

  • Teachers don't have to do anything - all their classes are just there.
  • You can add multiple teachers to classrooms. No inviting a secondary teacher.
  • Students have all their classes at the start of the term.
  • Students are automatically in the correct classes.
  • Classes have totally consistent names.
  • You can do it for FREE - however, there are paid for options if you don't want to go the DIY approach.
The following requires you to have the student, staff and optionally, guardian emails on SIMS.

Step 1 - Setup up GAM - you need this to do command line processing on your domain. It's a very powerful tool to do batch commands.

Step 2 - Export a list of current Classrooms and archive any you don't want. I'd recommend this as it means users will only see the ones for the current academic year.

gam print courses todrive 

This will export all the details of all your Classrooms to a Google Sheet. Pick the ones you want to archive and save the list of class id's as a csv file.

gam csv toarchive.csv gam update course ~id status ARCHIVED

id = the header in the csv file for the column that contains all the id's of the classes to be archived.
toarchive.csv = the file name of the csv file

Step 3 - Provision new Classrooms and add the "primary teacher"
For this, you will need a SIMS report(s). Exactly what you do depends on a number of factors. If you want Tutor Groups and Classes and you have multiple teachers per class, then you will probably need two reports. One will return all the classes (including) tutor groups and the other will return all classes (excluding tutor groups) but including all teachers - not just the designated lead teacher. I have to join these two lists together. The reports I run are:



Once you have a spreadsheet with a list of class-names, subjects and who teaches them - export it to a csv file and run:

gam csv classes.csv gam create course alias ~alias name ~alias section ~subject teacher ~teacher

  • classes.csv = your list of classes (call it what you want)
  • alias = the class name e.g. 12C-Ch1-2017 or whatever SIMS spits out.
  • name = I set the same as the alias
  • section = subject - e.g. Chemistry
  • teacher = primary teacher

So the above are the headings in your csv file. This will make all of your classes.

Now add any other teachers (if you have more than one per class)

gam csv teachers.csv gam course ~alias add teacher ~teacher

teachers.csv = a full list of all the teachers and every class they teacher
alias = name of the class as above
teacher = email address of the teachers to be added

This will add anyone not already added as an additional teacher.

Now set the classes as ACTIVE

gam csv classes.csv gam update course ~id status ACTIVE

This uses the same csv file and headers as before. This means the teacher does not have to accept the invite to the Classroom - you are doing it for them = nothing for the teacher to do.

Step 4 - Add the students

How you do this depends on what else you have going on. We use Hapara Teacher Dashboard which creates Google Groups for every class and keep them up to date - see this blog post. So all we have to do is sync these groups to Google Classroom. If you use directory sync you might also be able to use this option:

gam csv groups.csv gam course ~group sync students group ~groupemail

Update (9/9/2017) - the above gam csv command stopped working for me - no idea why - just did not do anything. So I've switched to using powershell - which works:

$list = Import-Csv C:\GAM\groups.csv
foreach ($entry in $list)
  {
    .\gam.exe course $($entry.group) sync students group $($entry.groupmail)

  }
  • groups.csv = a list of all the groups (as in the other csv files)
  • groupmail = email address of the class Google Group
  • group = alias of the Google Classroom

So these two only differ by the Group Email having @wheatleypark.org on the end.
This list is static and in our case does not change during the year. Student membership does and is updated in the Google Groups. This command you can pop into a batch file and run as a scheduled task and it will keep your rosters up to date silently for as long as you want. I suggest running it every night as a scheduled task on a VM overnight.

Step 5 - Add Guardians (optional)
You can run a SIMS report to export your guardian emails. All you need is two columns - one for the student emails and the other for the guardian.
The SIMS report I run on a weekly schedule is:

Via the commandreporter SIMS function. This can be run as a scheduled task and export the csv file to a network share:

"C:\Program Files\SIMS\SIMS .net\commandreporter" /user:SIMSUSER /password:SIMSPASSWORD /report:"Student-Parent-email" /OUTPUT:\\Win10man\gam\guardians.csv /QUIET

You can then run the following GAM command to add and update guardian emails (again run as a batch file on a schedule).

gam csv guardians.csv gam create guardianinvite ~”Primary Email” ~Email  

  • guardians.csv = your csv file
  • Primary Email = the guardians email address
  • Email = the students email

That's it. The last two commands (parents and students sync) can be left to run as scheduled tasks. The rest is a once a year house keeping task. Once you have done it once is really quick and works reliably.

If you don't have students in groups, you can add them to individual classes with the command:

gam csv students.csv gam course ~alias add student ~student 

  • students.csv = a list of all the students and the classes they take.
  • alias = the course alias - so just the name of their classes.
  • student = the email address of the student.

If you want to do it the sheets way, you can use the sheets add-on Rostersync which will do the job, but is rather more of a manual process to update.

The most important thing is to test stuff with a few classes to check everything works, before creating 100's of classes.






Comments

  1. Where do I upload my CSV for the guardian and student emails? I'm still trying the GAM but it keeps failing the apps.alerts

    ReplyDelete
    Replies
    1. You don't upload it anywhere, you use it in a gam csv yourcsv.csv gam gam create guardianinvite ~parentemail ~studentgsuiteemail

      Delete

Post a Comment

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 rf

Adding subdomains to G Suite

This is how I add subdomains (so basically new schools) to out G suite setup. I've got these steps documented on a scruffy set of notes that I've now got in Keep - so time to document them - for myself as much as anyone else! Steps in order (roughly) Add the new domain Verify the new domain Add MX records to hosting Add SPF record for Google to hosting Turn on email authentication Add DMARC record to hosting Setup custom Directory and restrict students OU to this. Create an admin quarantine for the domain. Configure SPAM setting for the domain. Turn on and off services as appropriate. Map a blank Google Site to the naked domain - if required. Setup some basic groups - allstaff, allusers (for directory) and students with appropriate permissions. Deploy custom wallpapers. So the steps in a bit of detail: Add the new domain & Verify ownership Click on Domains in the admin console: Add/remove domains followed by "add a domain" At this

My favorite GAM commands - well a few of them at least!

Where would be without GAM? Paying for expensive syncing tools or doing tedious manual tasks in the admin console. GAM can automate most things you might want to do in G Suite. So these are a few of my favourite commands - one I use either as part of a batch file - or just standalone. There are loads more - but these are ones that are used daily. Classroom Create a spreadsheet of all your domains classes -  gam print courses todrive Create a spreadsheet of a teacher's classes:  gam print courses teacher fred@mydomain.com todrive Bulk create classes: gam csv classes.csv gam create course alias ~alias name ~alias section ~subject teacher ~teacher status ACTIVE where classes.csv is a list of classes you want to make. Add teachers: gam csv teachers.csv gam course ~alias add teacher ~teacher Add students: gam csv students.csv gam course ~alias add teacher ~student Sync Students (in this example to a group - but could be an ou/csv file) gam csv groups.csv gam course ~g