Skip to main content

Posts 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);

Recent posts

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…

Lenovo N23 Yoga Chromebook

The Lenovo Yoga N23 is the latest touch/flip Chromebook that I've got my hands on. This device competes with the Acer R11 (4Gb/32Gb/N3060 variant) in terms of its price point. Unlike the more expensive HP 360 and Acer Spin, this does not have a stylus or second camera.
Some key features
Drop resistant from table height.Water resistant.4Gb RAM32 Gb SSDQuad core Mediatek processor - same as found in the Acer R13 - scores about 1000 points more than the N3060 and runs some Android Apps much faster.11" IPS touch display360 hinge - so laptop to tablet.USB C ChargingSupport for PlayStore Apps on the Stable Channel
We are considering this device instead of the Acer R11 (which we have been deploying) for a future deployment to our new intake. Compared to the very familiar R11, the Lenovo is a little bit faster, especially using Android Apps and feels more rugged. Its also a little bit cheaper to buy.
Keyboard and touchpad are very similar to other Chromebooks of this price point and t…

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 academi…

How to Sync Students to Hapara Teacher Dashboard from Capita SIMS MIS

As a long time user of Hapara Teacher Dashboard, I thought I should document how you can update classes on a daily basis from Captia SIMS - the most common MIS in the UK and the one we use.

Step 1 - Generate a suitable students report (same one I use for syncing Google Classroom - see another blog post...)
Step 2 - Set this report to run on a schedule and output to a network share on a Virtual machine that is online all the time. You do this with the "commandreporter" on your SIMS server. An example command that lives in a batch file that runs as a scheduled task (the username and password is a SIMS user with sufficient privileges to run the report):

"C:\Program Files\SIMS\SIMS .net\commandreporter" /user:USERNAME /password:PASSWORD /report:"haparastudents" /OUTPUT:\\Win10man\gam\hapara\hapara.csv /QUIET

Step 3 - Write a script to reformat the csv file to an acceptable Hapara format. So key things:

Convert "/"'s into "-" as you cannot …

Collecting Photos/Videos with a Google Form

I recently realised that an easy way to collect and sort Photos and Videos was to use Forms. We had to take photos of every room in the school and have them archived for easy discovery in the event of an insurance claim. So I made a simple form that had the room name as the first question and a file upload question for the next:

I linked this to a spreadsheet and added the formula =split(C2,",") to an extra column. This simply splits the list of image urls into separate cells to make them clickable.

On a phone, you just load up the form, add the room number, hit the upload button, select upload file and choose Camera. You can then add photos straight from your camera. 

Took a couple of minutes to setup and no photo organising required at the end of the process.

Quite a neat way of uploading photos and automatically organising them. Also useful as multiple people can use the form and upload photos to the same folder and have their names added to the start of the file name as …

Enabling Windows resizing on Play Store Apps on ChromeOS

ChromeOS devices that support Android Apps from the Play Store are starting to receive Android 7.1.1. One of the main features is better windows sizing. Brief video on how to enable this feature: