Skip to main content

Creating "Homework" Google Sites

Tom Stoneham came to us with an interesting problem... "Can I automatically create 80 or so HomeWork Google Sites from a template for students? And when the deadline has been reached can their access be revoked and links sent out to examiners". The students' task will be create a site about a particular philosopher. The prototype looks like this...

I'd had a stab at solving this earlier to see it was possible, and maybe too quickly I jumped for python. But in the spirit of making something that a. worked, b. was sharable, c. I wouldn't have to maintain ( hopefully ), I thought I'd have a go a re-doing it in AppScript.

Having met with Tom, there were a few addition requirements:

  • Can student sites have unique IDs that are mapped on to a marking sheet?
  • Can the URLs be kept in a list because, if you have 80 students then 8 markers may be given 10 students each?
  • What is the best way for the University of keep the snapshot but still give the student the ability to take their work with them? The student may even continue working on it.
  • Can an Editor of a site (i.e not the Owner) make a copy of a Site?
  • Just as an afterthought, can there be guidance about Copyright etc?
So, my early experiments were this.

1. Create the Spreadsheet

The siteurl column would be used to store the site created's URL for use later.

2. Create A Menu To Do Stuff

In the Script Editor I added:

function onOpen() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Create Sites...", functionName: "create_sites"},
                      {name: "Add Students To Sites", functionName: "add_students_to_sites"},
                      {name: "Email Examiners", functionName: "notify_examiners"} ];
  ss.addMenu("Administration", menuEntries);

After a while, I hit this wall...,  when creating a site, because it can take an elastic amount of time, but the code continues. That means if you try to create a site, then set up who the users are, the site might not be there yet. My workaround was to create separate functions... so the process, and indeed the Menu Items are...

  1. Create all the Google Sites based on the spreadsheet data, copying the chosen template site
  2. Add the students as Editors to the the sites 
  3. When the deadline is reached, remove the students as Editors ( making them Viewers ) and also make the Examiners Viewers ( sending them emails with the sites they have to mark in with each students unique ID.


function create_a_site( template_site_name, student_unique_id ){
  var domain = "" ;
  var template_site = SitesApp.getSite(domain, template_site_name );
  var name = "" + template_site.getName() + "-" + student_unique_id .toLowerCase() ;

  var title = name ;
  var summary = "Deadline 21st, December 2012" ;
  // See the warning in about site creation speed
  var site = SitesApp.copySite(domain, name, title, summary, template_site);
  var sites_url = site.getUrl();
  return sites_url ;

function create_sites(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("students");
  var result = Browser.inputBox("Which Site is the template Site?", "project-template", Browser.Buttons.OK_CANCEL );
  if (result == "cancel"){
    //Browser.msgBox("CANCEL: " + result)
     var range = ss.getDataRange().getValues();
     var students = rangeToObjects(range);
     var template_site_name = result
           for(var i = 0; i < students.length; i++){
             var student = students[i];          
             var site_name = template_site_name +"-" + student.uniquereference.toLowerCase();
             var domain = "" ;
             var sites_url = create_a_site(template_site_name, student.uniquereference.toLowerCase() ); //Alter the values
             students[i].sites_url = sites_url;

             var x = i+2
             var cellnum = x.toString().replace("0", "");
             Logger.log( cellnum );

             var values = new Array();
             values[0] = sites_url
             var cellname = "D" + cellnum;
             var range = sheet.getRange( cellname )
             range.setValue( sites_url );
     Logger.log( e.message );
     // Now write the URLs back to the spreadsheet. Or not.

function add_students_to_sites(){
  //Browser.msgBox("Add Students To Sites!")
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("students");
  var range = ss.getDataRange().getValues();
  var students = rangeToObjects(range);
  for(var i = 0; i < students.length; i++){
    student = students[i];
    var url = student.siteurl;
    var email =;
    var examiner = student.examiner;
    var site = SitesApp.getSiteByUrl(url);

  Browser.msgBox("Added students to sites")

function test_create_a_site(){
 var x = create_a_site("project-template",  "Y63326039"  );
 Logger.log( "Done! " + x );

function add_people( site_name, student_email, examiner_email ) {
  var domain = "" ;
  var site = SitesApp.getSite(domain, site_name );
  site.addEditor( student_email ).addViewer(examiner_email);
  the_url = site.getUrl();
  //email a link to the student
                    "Your Philosophy Homework Site",
                    "A Google Site has been created for you to fill in. \n\n " +
                     the_url + "\n\n",                  
                    {name:"Philosophy Course"});*/


function notify_examiners ( ){
  // email a link to the examiner
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("students");
  var range = ss.getDataRange().getValues();
  var students = rangeToObjects(range);
  for(var i = 0; i < students.length; i++){
    student = students[i];
    var url = student.siteurl;
    var email =;
    var examiner = student.examiner;
    var site = SitesApp.getSiteByUrl(url);
                      "TEST: A Philosophy Homework Site To Mark",
                      "A Google Site has been created for you to mark. \n\n " +
                     url + "\n\n",                    
                    {name:"Philosophy Course"});
    //At this point we might want to record that the mail has been sent...


There are still a few bugs to iron out. It really doesn't like creating a site if a site already exists with that name, and I've found that deleteSite() never runs smoothly.

The next step is maybe to add some UI to select which site you want to use as a template. And of course to start making it a bit more robust. Ahem.

I'm actually quite surprised that this was easier to achieve in AppScript than it was in Python... probably...


Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems. A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it. The QR app looks like this. The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this. The Web Application The web application, which only checks items in or out and should be used on a phone in conjunctio

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)