Skip to main content


Showing posts from October, 2017

Automate Securly Guardians upload from SIMS

Securly has recently allowed automated uploads of guardian contact details. This requires getting your guardians details into a csv file in a specific format. This is easy to do manually - but takes a little work to do automatically. This is the procedure that I use to extract the data from Capita SIMS and sync it to Securly on a daily basis. You can see the basic Securly guide here - but don't believe the bit about the columns being in any order - I've found they have to be in the order specified!
SIMS Report The field I use are:
Note - you have to edit the names of the fields to the ones Securly want.
I run this on a daily schedule and output the result to a csv file on a virtual machine that I use for lots of automated tasks.
"C:\Program Files\SIMS\SIMS .net\commandreporter" /user:xxxxxxx /password:xxxxxxx /report:"securlyreport" /OUTPUT:\\Win10man\gam\parents.csv /QUIET
Modifying the output to meet the Securly requirements  SIMS outputs csv files with do… data to Sheets on a schedule

Below is an approach that I use to sync data from (our MIS) to a Google Sheet. We do this for a fair number of things now to keep stuff up to date automatically.

The script I use to pull csv data into a sheet:

function copyfromcsv() {

  var file = DriveApp.getFilesByName("yourcsvfile.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh2 = ss.getSheetByName("sheettoimportto");

  sh2.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);


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 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 ~groupmail sync students g…