Using Google Sites for Self Assessment

I went to an interesting workshop run by Simon Davis this week looking at using Google Apps in Education. I was there to demo Google Hangouts and unfortunately had a heap of technical problems ( my laptop battery died... oops! Note: never believe it when your battery says it has 2 hours of juice ). Luckily, I had a Hangout I'd prepared earlier.

The best bit for me was Catherine Shawyer (right) showing how, in Education, they were using a variety of Google Apps with elegance and gusto.

They used template Google Sites for portfolio work (shown below). They found the sheer reliability of Google Apps was hugely important given their students loss of trust having used other tools and simply lost work by accidentally clicking the back button or similar. Usability really matters.

But their most enthusiastic use was in using Google Sites for the hugely valuable process of self assessment. Typically, this previously took place on paper that became increasingly dog-eared and was often lost.  Using a Google Site meant firstly, that it happened (better than before) and secondly that it could be "taken away" by the student in their next stage of their learning, two huge factors in their use and liking for the technology.

Catherine said she used to use USB sticks to store videos of students' presentation and was forever having to swap them and give them to the right person. She now stores them all online and said “I don't say this often, but Google Drive has changed my life” -

I'm starting to see a pattern recently, and that is, wherever there is dog-eared paper, there is probably a very strong case for replacing it with Google Docs.

By: Unknown On 3:02 AM

Continue Reading

Using Google Sites For Student Work ( Philosophy )

The Idea

Prof +Tom Stoneham and Nick Jones had the idea of using Google Sites as an alternative to textual documents for student work, in this case, a dissertation about a certain philosopher.

Google Sites give the opportunity for the creation of a network of information rather than a narrative document. A site can hold videos, audio and refer to other online resources with links.

The idea was that there would be a simple template site (see above), with boiler plate text and guidance about copyright issues etc. and the student could then start editing existing pages and creating new ones.

Administration

From an administrational perspective, the Google sites would need to be closed to student when the deadline was met. Ideally, it would good if the student could have a copy of their Google Site - both to continue working on it and to use in their portfolio of work.

Whilst Tom didn't need the student's identity to be anonymized, but we used a unique reference number for the name of the site anyway.

Technical

From a technical perspective, creating a 80 Google Sites would seem to be a simple task. It would be, were it not for the fact that often, when creating resources like Google Sites or Calendars or Groups there can be a variable lag from asking Google to create the resource and it being available for further use. And whilst there is a lag, the Apps Script code you write to create a Google site is asynchronous, which effectively means you do the coding equivalent of ...

var basket = new Basket()
basket.addEggs( 12 ) // At this point there probably is NO BASKET! Error!

The "solution" I hit upon was using a once-a-minute Trigger to pick off the tasks I wanted to run one at time, meaning that after each one Google's system have more than enough time to "catch up". The tasks being...

• create a Google Site from a template
• add the Student as Editor
• When the deadline is reached, add the examiner to the site as Viewer and remove the student as Editor
• Email the examiner that they have a site to mark
• create a copy of the student's Google Site and make them Owner for their portfolio.

I created a spreadsheet with three sheets ( to the wind ). The first, Administration is where you set up the name of your project, which Google Site to use as your template and when the deadline is. The second, Students is a list of student emails, unique references and examiner emails. And the last is a utility sheet that gets a list of your Google Sites ( for interface niceness ).

I then created a huge function to gently create the Sites and add the right permissions to the right people at the right time. It looks like this.

function triggered_site_maker( ){
 
  var ss = SpreadsheetApp.getActiveSpreadsheet( );
  var sheet = ss.getSheetByName( "Students" );
 
  // Get default values from the Administration sheet
  var admin_sheet =  ss.getSheetByName("Administration");
  var template_site_name =  admin_sheet.getRange("B1").getValues()
  var domain =  admin_sheet.getRange("B4").getValue( )
  var homework_id =  admin_sheet.getRange("B5").getValues( )
  var homework_title =  admin_sheet.getRange("B6").getValues( )
 
  // get all the data in the Students sheet
  var range = sheet.getDataRange().getValues();
  var students = rangeToObjects(range);
 
  for(var i = 0; i < students.length; i++){  
    var student = students[i];
   
    var student_unique_ref = student.uniquereference.toString().toLowerCase()
    var new_site_name = homework_id + "-" + student_unique_ref  
   
    var student_email = student.email
    var examiner = student.examiner
    var siteurl = student.siteurl
    var status = student.status
   
    var statuscellname = "E" + (i +2 )
    var timestampcellname = "F" + ( i +2 )
    var timestamp =new Date()
    var cellname = "D" + ( i +2 ) //
   
    if ( student_unique_ref != ''){
     
      switch (status){
        case '': // We're at the beginning, make a site for the student.
         
          try{
            // Note: sites_url is a NEW sites url not an already made one.
            var sites_url = create_a_site(template_site_name, new_site_name )
           
            sheet.getRange(cellname).setValue(sites_url)
            sheet.getRange(statuscellname).setValue('site made')
            sheet.getRange( timestampcellname ).setValue( timestamp )
            break
           
          }catch(e){
            ErrorMail(e)
          }
         
        case 'site made': // The site has been made, now add the student.
         
          try{
            var name = SiteUrlToName(siteurl) // workout site's name from URL. Had problems with getSiteByUrl()
           
            if (domain == ''){
              var site = SitesApp.getSite( name)
              }else{
                var site = SitesApp.getSite(domain, name)
             }
            site.addEditor(student_email);
            sheet.getRange(statuscellname).setValue('site made and student added')
         
            site.setTitle( homework_title ) // Handy for searching later I guess. Gulp.
            sheet.getRange( timestampcellname ).setValue( timestamp )
           
          }catch (e) {
            ErrorMail(e)
          }
          break
         
          // NOTIFY EXAMINER/REMOVE STUDENT AND COPY SITE  
          case  'site made and student added':
         
          var deadline =  admin_sheet.getRange("B2").getValue( )
          var now = new Date()
          if (now > deadline){
            try{
              var name = SiteUrlToName(siteurl) // workout site's name from URL
              if (domain == ''){
                var site = SitesApp.getSite( name)
                }else{
                  var site = SitesApp.getSite(domain, name)
                  }
              site.removeEditor( student_email )
              sheet.getRange(statuscellname).setValue('student removed')
              sheet.getRange( timestampcellname ).setValue( timestamp )
             
            }catch(e){
              ErrorMail(e)
            }
          }
          break
         
          case  'student removed': //Now, add the examiner
         
          try{
            var name = SiteUrlToName(siteurl) // workout site's name from URL
            if (domain == ''){
              var site = SitesApp.getSite( name)
              }else{
                var site = SitesApp.getSite(domain, name)
                }
            var site_name = site.getName()
            Logger.log( site_name)
            var viewers = site.getViewers( )
            if ( examiner != ''){
             
              site.addViewer( examiner )
              sheet.getRange(statuscellname).setValue('examiner added')
              sheet.getRange( timestampcellname ).setValue( timestamp )
            }
            break
           
          }catch(e){
            ErrorMail(e)
          }
         
        case  'examiner added': //Email them with a link to the site
          try{
            MailApp.sendEmail(examiner,
                              "A Google Site To Mark",
                              "As an examiner, a Google Site called '" + homework_title +"' has been created for you to mark. \n\n " +
                              "Unique student reference: " + student_unique_ref + "\n\n " +
                              "url: " + siteurl + "\n\n",                  
                              {name:"Google Site Marking", noReply:true});
            sheet.getRange(statuscellname).setValue('examiner emailed')
            sheet.getRange( timestampcellname ).setValue( timestamp )
            break
          }catch(e){
            ErrorMail(e)
          }
         
        case  'examiner emailed': //Make the student a copy for their portfolio.
          try{
           
            var name = SiteUrlToName(siteurl) // workout site'ss name from URL
            if (domain == ''){
              var site = SitesApp.getSite( name)
              }else{
                var site = SitesApp.getSite(domain, name)
                }
            var new_name = "" + name + "-copy"
            var title = site.getTitle()
            var summary = site.getSummary()
            var site_name = site.getName( ) // use this site as a template
           
            if (domain == ''){
              var copied_site = SitesApp.copySite(  new_name , title, summary, name)
              }else{
                var copied_site = SitesApp.copySite( domain, new_name , title, summary, name)
                }
           
            Utilities.sleep(4000)
            var copied_site_url = copied_site.getUrl()
            sheet.getRange( "I" + (i + 2) ).setValue(  )
            sheet.getRange(statuscellname).setValue('student copy created')
            sheet.getRange( timestampcellname ).setValue( timestamp )
            break
           
          }catch(e){
            ErrorMail(e)
          }
         
         
        case  'student copy created': // Add them as owners to these copies.
          try{
            var copied_url = sheet.getRange( "I" + (i + 2) ).getValue()
           
            var name = SiteUrlToName(siteurl)
            if (domain == ''){
              var site = SitesApp.getSite( name)
              }else{
                var site = SitesApp.getSite(domain, name)
                }
            site.addOwner(student_email)
            sheet.getRange(statuscellname).setValue('student added to copy')
            sheet.getRange( timestampcellname ).setValue( timestamp )
            break
           
          }catch(e){
            ErrorMail(e)
          }
        case  'student added to copy':
         
          sheet.getRange(statuscellname).setValue('Completed')
          sheet.getRange( timestampcellname ).setValue( timestamp )
          break
      }//end case
     
     
    }//end if empty student_unique_ref -- 
  }//end forloop
 
  // It would be good at this point if we could then de-activate the trigger....
 
}

function test_trigger(){
  triggered_site_maker()
}





function create_a_site( template_site_name, new_site_name ){

  var ss = SpreadsheetApp.getActiveSpreadsheet( );
  var admin_sheet =  ss.getSheetByName("Administration");
  var domain =  admin_sheet.getRange("B4").getValues( )
 
  if ( domain == ''){
    var template_site = SitesApp.getSite( template_site_name )
    }else{
      var template_site = SitesApp.getSite(domain, template_site_name );
    }
 
  var title = template_site.getTitle()
  var summary = template_site.getSummary()
 
  // See the warning in https://developers.google.com/apps-script/class_sitesapp
  if ( domain == ''){
    var site = SitesApp.copySite(  new_site_name, title, summary, template_site);
  }else{
    var site = SitesApp.copySite( domain, new_site_name, title, summary, template_site);
  }
  Utilities.sleep( 3000 ) // Yawn! 
  var sites_url = site.getUrl( );
  return sites_url ;
}





function my_sites_names(){   /////  GET A LIST OF SITES YOU'VE MADE 
  // This is used in the interface as a data validation thing.
  var ss = SpreadsheetApp.getActiveSpreadsheet( );
  var admin_sheet =  ss.getSheetByName("Administration");
  var my_sites_sheet =  ss.getSheetByName("My Sites");
 
  // clear the ole data...
  var last_row = my_sites_sheet.getDataRange().getLastRow()
  var range = my_sites_sheet.getRange(2, 2, last_row)
  range.clear()
 
  var domain =  admin_sheet.getRange("B4").getValue( )
 
  if (domain == ''){
    var sites = SitesApp.getSites()
    }else{
      var sites = SitesApp.getSites(domain)
      }
 
  var site_names = new Array();
  for(var i = 0; i < sites.length; i++){
    var site = sites[i]
    var site_name = site.getName()
    //set name
    var range = my_sites_sheet.getRange(i+2, 1)
    range.setValue( site_name )
    Logger.log( site_name )
   
    //set URL
    var range = my_sites_sheet.getRange(i+2, 3)
    range.setValue( site.getUrl() )
   
    site_names.push(  site.getName() )
  }
  return site_names
}

Take a Copy And Try It Yourself

Note: This will only work with Apps for Education/Business accounts ( and won't work for consumer accounts because you can't create a Google Site for random people willy nilly ).

1. File > Make a copy of the spreadsheet here.

2. Go to the Script Editor and Run Event Handling > onOpen() . This authenticates the Script to ask which are your Google Sites. This is just for interface niceness.

3. Fill in the Administration sheet. This needs a domain and a deadline. There's also a "project ID" which can be anything, but is used to differentiate different "assignments".

4. Add students, unique references and examiners to the Students sheet ( there is some Utility code to help generate them if you don't have unique IDs ).

5. You might want to add your email address to the Utility > ErrorEmail function. When a Script is running from a Trigger I don't think you get access to any Logger.log() messages, so this is a handy way of debugging the app.

6. You are now ready to run the triggered_site_maker() function. You can run it straight from the ScriptEditor, but you will need to add it as a Trigger for it to do all it needs to do. Like this...

Once running, you should see various values being populated as your Google Sites get created and permissions added. When the deadline is met, the students are removed as Editors and given a copy of their site. Once this is finished you can delete the Trigger.

By: Unknown On 5:43 AM

Continue Reading

The Day I Dropped Round The Security Guy's

After discovering that my direction of work for the Booking System was from a security perspective, deeply flawed, I thought that I could perhaps work around giving people access to the code by embedding a web application within a Google site. I thought this would be a big structural change, but it only took a few minutes. It looks like this.

There's a slightly different approach. Firstly the spreadsheet is embedded as view only. The spreadsheet is only used a visualisation of availability now - there's no direct manipulation of any data. 

Because, almost without thinking, I made the published web app a HTML based one, it meant that I could easily add jQuery and interface niceties like the date choosing dropdown (shown above).

Because all the code runs as me, and I've already authorized the code, the end user isn't presented with any awful dialogs. I make adding the booking something that the end user does, by hand themselves. You can pre-populate a Google Calendar new event form by hacking the url variables easily. Like this...

http://www.google.com/calendar/event?action=Template&text=Toms+Next+Birthday&dates=20130903/20130903&sprop=website:http://york.ac.uk.libcal.com&details=Bring+CAKE!

Docs on how to do that here.

Conclusions?

Well, it's good to know that I have something that we can happily share around the university securely.

The disappointing aspects are that...

Google Spreadsheets don't seem to allow you separate the concepts of a "data editor" and a "code editor" in any useful way. That means, if you allow someone to add data, they can change the way that data gets added. The only way to "share" collaborative data is to cludge an awkward and ugly interface in the front of it. This means that, AppsScript is best used within a spreadsheet for individual ( or selected ) users... not in a collaborative scenario.

Whilst "jumping ship" into a HTML based web app, rather that using widgets in a Spreadsheet does add more complexity but also brings more control. I may end up not using the spreadsheet viewing gadget and build a table of bookings by scratch, one that can take direct manipulation to book multiple hot-desks across multiple days. I had wanted to leverage many of the spreadsheet's abilities, I now have to think of the spreadsheet as backend-data storage. I also have to grapple with jQuery to build a table as complex as a the spreadsheet shown above (with some cells coloured in). 

When working with the UI Builder, it isn't possible to have copy-and-pasteable UIs or share them amongst different spreadsheets. This makes for unmaintainable code, with each spreadsheet containing its own hand-crafted UI. Were UIs standalone objects, like AppsScripts are becoming, then they could at least be duplicated, or loaded and copied-n-pasted. 

Google's whole "Publish Your Apps Script" concept is flawed. Who would be daft enough to click the dialog that says, give this unknown author complete access to all my docs and emails? 

What All Of This Means

Over the last few months we've seen a lot of new developments from Google, many of them very welcome, but new developments are often very easy self-contained concepts... I would like to see a bit more depth and sophistication from Google in how the bits fit together, where the gaps are and where things could be smoother.

For example, the UI Builder components could include a date-picker surely? And what about type-ahead scrolling perhaps. And how about different classes for working with data, instead of SpreadsheetApp how about CodeAuthorSpreadsheetApp ( which can't read all the user's data  )? 

The feeling that I'm getting is that AppsScript ( and UI Builder widgets ) when used with spreadsheets is only useful for data that you own... and isn't appropriate for shared data... a web app front-end to working with shared data seems much more sensible ( but a fair bit more work ). It means that only the hot-desk admin team get to do the more complex things, but hey... it sort of makes sense.

Google really could do to take a look at HyperCard and make it work in AppsScript. It would solve a million problems ( yes, they're mostly all mine, but ... ).

My next challenge is to help develop a better way of storing students marks than the system that is currently used. This seems already to be breaking down into a spreadsheet with UI Builder widgets to do certain admin tasks and a web app for exam markers to record results. 

I'll let you know how I get on and share the code/files from the Booking System when I've got it running smoothly.

By: Unknown On 3:54 AM

Continue Reading

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.

So...

function create_a_site( template_site_name, student_unique_id ){

  var domain = "york.ac.uk" ;

  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 https://developers.google.com/apps-script/class_sitesapp 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)

        }

   else{

     

     var range = ss.getDataRange().getValues();

     var students = rangeToObjects(range);

     var template_site_name = result

     

     try{

           

           for(var i = 0; i < students.length; i++){

             var student = students[i];          

             var site_name = template_site_name +"-" + student.uniquereference.toLowerCase();

             var domain = "york.ac.uk" ;

           

             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", "");

             

             //Browser.msgBox(x);

             Logger.log( cellnum );

             var values = new Array();

             values[0] = sites_url

             var cellname = "D" + cellnum;

             var range = sheet.getRange( cellname )

             

             range.setValue( sites_url );

             

     }      

         

     }catch(e){

     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 = student.email;

    var examiner = student.examiner;

    var site = SitesApp.getSiteByUrl(url);

    site.addEditor(email);

    site.addViewer(examiner);

   

  }

  Browser.msgBox("Added students to sites")

}

function test_create_a_site(){

 var x = create_a_site("project-template",  "Y63326039"  );

 Logger.log( "Done! " + x );

 //Browser.msgBox(x)

}

function add_people( site_name, student_email, examiner_email ) {

  var domain = "york.ac.uk" ;

  var site = SitesApp.getSite(domain, site_name );

  site.addEditor( student_email ).addViewer(examiner_email);

  the_url = site.getUrl();

 

  //email a link to the student

  /*MailApp.sendEmail(student_email,
                    "Your Philosophy Homework Site",
                    "A Google Site has been created for you to fill in. \n\n " +
                     the_url + "\n\n",                  
                    {name:"Philosophy Course"});*/

 

  Logger.log("Done!");

 

}

function notify_examiners ( ){

  // email a link to the examiner

  // https://developers.google.com/apps-script/class_mailapp

 

  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 = student.email;

    var examiner = student.examiner;

    var site = SitesApp.getSiteByUrl(url);

   

    MailApp.sendEmail(examiner,

                      "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...

     

  }

 

  Browser.msgBox("Done!");

}

 

Conclusions

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...

By: Unknown On 7:56 AM

Continue Reading