Creating A Shared Logging System

This is an approach we've used and re-used a number of times. Imagine you want a group of people to share some information using a Google Form. But although you don't really want to share the spreadsheet of the collected data, you do want people to use a subset of it.

In this example, we will create a "Research Logger". Here's the first form, go fill it in.

The Confirmation Page of the Form has a link to a web application made earlier, like this.

The web application uses a Table Chart visualisation to show a subset of the data but has really nice filters so that you can drill down on the information. It looks like this.

The web application above is browsable here.

The code to display a table like this is...

 function doGet(e) {    
  var spreadsheet_id = 'YOUR_SPREADSHEET_ID'
  var ss = SpreadsheetApp.openById(spreadsheet_id)
  var sheet = ss.getSheetByName("Form Responses")
  var last_row = sheet.getLastRow()
  var last_column = sheet.getLastColumn()
  var range = sheet.getRange(2, 1, last_row-1, last_column)
  var data = range.getValues( )
  
  var dataTable = Charts.newDataTable()      
        .addColumn(Charts.ColumnType.STRING, "Added by")     
        .addColumn(Charts.ColumnType.STRING, "Department")      
        .addColumn(Charts.ColumnType.STRING, "Researcher")  
        .addColumn(Charts.ColumnType.STRING, "Funder")  
        .addColumn(Charts.ColumnType.STRING, "Name of Call") 
        .addColumn(Charts.ColumnType.STRING, "URL") 
  
  for ( r in data){
    var row = data[r]

    var username = row[1]
    var department = row[2]
    var researcher_name = row[3]
    var researcher_email = row[4]
    var tags = row[5]
    var notes = row[6]
    var funder = row[7]
    var name_of_call = row[8]
    var deadline = row[9]
    var research_title = row[10]
    var folder_url = row[21]
    
    
    if ( researcher_email != '' & typeof researcher_email != 'undefined' ){
      researcher_name = '' + researcher_name + '' 
    }
    
    folder_link = 'files'
    dataTable.addRow( [username, department, researcher_name, funder, name_of_call, folder_link ])    
  }
     
  dataTable.build( );
  
  var chart = Charts.newTableChart()
      .setDimensions(1200, 500)
      .setDataTable(dataTable)  
      .setOption('allowHtml', true)
      
      .build();
  
  var name_of_callFilter = Charts.newStringFilter().setFilterColumnLabel("Name of Call")
        .setLabelStacking(Charts.Orientation.HORIZONTAL)
        .setLabel("Name of Call")
        .setRealtimeTrigger(true)
        .setCaseSensitive(false)
        .setMatchType(Charts.MatchType.ANY)
        .build()
  
  var departmentFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Department")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Department')
       .setSortValues(true)
       .build();
   
     var funderFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Funder")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Funder')
       .setSortValues(true)
       .build();
   
   
   var dashboard = Charts.newDashboardPanel()
       .setDataTable(dataTable)
       .bind(name_of_callFilter, chart)
       .bind(departmentFilter, chart)
       .bind(funderFilter, chart)
       .build();
   
   
   var app = UiApp.createApplication().setTitle("Research")
   var panel = app.createVerticalPanel().setSpacing(10)
   
   panel.add(name_of_callFilter).add(departmentFilter).add(funderFilter).add(chart );
   
   dashboard.add(panel)
   app.add(dashboard)

   var label = app.createLabel().setText("TOTAL: " + data.length + " research bid projects").setStyleAttribute("color", "#442233").setStyleAttribute("font-size", "18px")
   app.add( label )
   
   
   var link_to_form = app.createAnchor("Add a new Research bid", "https://LINK_TO_YOUR_FORM/viewform")
   link_to_form.setStyleAttribute("color", "blue").setStyleAttribute("font-size", "18px")
   app.add( link_to_form )
   app.setWidth(1200)
   return app
}

One added useful feature is that when someone submits the form, a GDrive folder is created for that item and they are added as an Editor to that folder. A link is then added to the table for ease of access.

The code automatically create a GDrive folder when the form is submitted is...

function onFormSubmit(e) {            
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Form Responses")
  
  var row = e.range.getRow() 
  
  var values = e.namedValues
  var department = values['Department']
  var researcher_name = values['Researcher name']
  var researcher_email = values['Researcher email']
  var name_of_call = values['Name of Call']
  var funder = values['Funder']
  
  var folder_title = department + " - " + researcher_name + " - " + funder + " - " + name_of_call
  
  var destination_folder = DriveApp.getFolderById('CHANGE_TO_YOUR_FOLDER_ID')
  var folder = destination_folder.createFolder(folder_title)
  folder.addEditor(researcher_email)
  var folder_url = folder.getUrl()
  

  
  sheet.getRange( row, 22).setValue( folder_url ) 
}

This collection of a Google Form, a Google Spreadsheet and a Web Application means that staff can easily add information and be able to easily browse the information other people have added. I think although calling a Table Chart a visualisation might be a bit grand, they are incredibly useful ways of presenting information in a navigable and filterable format.

By: Unknown On 6:54 AM

Continue Reading

Confusion about Apps Script Projects in Spreadsheets.

I'm in some confusion about how Apps Script projects work when embedded in a Google Spreadsheet. In my current spreadsheet, when I choose "Script Editor", I see this...

As you can see, there are multiple projects in the spreadsheet. I don't get this. It always happens that when I copy a spreadsheet too... that I end with a "Copy of XXX" and "XXX" Apps Script projects inside the spreadsheet. 

I can add extra projects via the "Create a new project" link but I can't remove projects from spreadsheets. This gets more confusing if both "Copy of Web App n Stuff" and the "Term Week Dates Booking Project" have a doPost () function in them. Which function gets called? 

Why would I want more than one Apps Script project in a spreadsheet?

Why can't I flip a project out to be a standalone Apps Script project?

When making copies of spreadsheets - why do I end up with multiple projects in a spreadsheet?

How might I remove an Apps Script project from a spreadsheet?

By: Unknown On 4:52 AM

Continue Reading

Inserting A Google Doc link into a Google Spreadsheet

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 all look too similar and you don't know what you are getting until you have clicked it.

For example, one part of your spreadsheet might ask "Do you have any supporting files?" and it would be handy to be able to insert a link to any number of Google Docs which might include a project plan Google Document, a PDF letter of support and a budget spreadsheet.

Like this.

When you select "A file from Google Drive" a dialog is presented showing you your Google Drive files, like this.

In this example, I am selecting the "What goes where: Action Plan" file. A =HYPERLINK() formula with the name and link of this Google Document is then inserted into spreadsheet.

Adding More Abilities

Once I'd created the ability to attach a Drive document, I knew that people would ask for other obvious features.

1. Uploading a file directly from your computer

You may have noticed that the menu also allows you to "Upload a file from your computer". This simply uploads the document into your Google Drive and then links to that uploaded file.

2. Attaching a regular link

There is also the ability to add a simple "regular hyperlink" - we often have documentation in our wiki or on web servers that need linking to as well as GDrive documents. This tool provides a simple interface

Conclusions: Keeping It Simple

This tool doesn't try to make sure that the permissions on the attached Drive file match those of the spreadsheet, but that is a good thing and anyone without access to a particular file can request access the usual way.

I experimented with showing an icon of the file type, or a link icon if linking to an external site, but that made the code need to look in adjacent cells to see if the data was empty.

This code doesn't save the uploaded files into a particular folder because it would make the code less "copy and pastable" but you could easily add that feature.

I also thought it might be nice to add a cell comment to say who uploaded the file. Again, that wouldn't be hard to add yourself.

There is a little interface clunkiness with the UiApp which means that the Google Drive chooser dialog window is a bit cramped, but all the functionality works well enough to add the extra dimension of collections of related files to your spreadsheets. Hopefully by publishing this article I may get some help with this.

You never know, maybe Google will roll some of these features into the main applications, but until then you can use this addition to easily collect together references to various Google Docs in a spreadsheet.

Also, I wonder when Google will add a Script Editor, or the ability to add menus to Google Documents? ( Update: Since I wrote this, Google have now added this. Cool. )

How To Add This Menu To Your Spreadsheet

The code to add an "Attach..." menu to your spreadsheet is in this spreadsheet called "Inserting GDrive docs into a spreadsheet" here:

https://docs.google.com/spreadsheet/ccc?key=0Ajnu7JgRtB5CdGtoUmM1YnlHaS1KWVowVkxtMnFzWFE#gid=0



1. Make a copy of the spreadsheet.
2. Go to menu Tools >> Script Editor
3. Copy and paste the code into the Script Editor of another spreadsheet of your choice.
You will have to run the onOpen() function to get the menu to appear.

By: Unknown On 5:46 AM

Continue Reading

Using Spreadsheets INSTEAD of Forms

Google Forms are a great way to quickly collect data into a spreadsheet but what if the data you are collecting is a bit too complex for a simple form to handle?

We've been experimenting with using a Google Spreadsheet, instead of a Form to gather information and finding that this approach has many advantages. We still use a Form to "initiate" the process, and the data gathered from the form is saved in a "central" spreadsheet.

When the form is submitted, the central spreadsheet makes a copy of a "template" spreadsheet. This spreadsheet is more "human readable" than a regular spreadsheet. When the form is submitted, the data is saved as normal, but it also fills in certain values in the copied template sheet, sets the right permissions and mails all the people who need to know about it a link to edit the template copy.

This template sheet has a "form-like" layout, including help ( shown at the right hand side ) and additional tools added with Apps Script. Tools include the ability to add a link to another Google Drive file. There are times when the data you want already exists ( as a document ) and you don't really want people to copy and paste that data into the form.

The copied template looks like this. Each of the fields has been sized ready to fit the data we expect. Some are even "colour bordered" to help different departments find their bits.

The "Central" Spreadsheet

The central spreadsheet, or the one that receives the input from the initial Form keeps a track of which files have been created, saving links to them in each row. It also has a "status" column that the copied spreadsheets know how to update as they get completed. This makes it really easy to add a simple "Status listing" web app to a Google Site ( shown below with dummy data ).

This means you can give nice "live" summaries to certain groups of people without scaring them with the prospect of looking a huge and hairy spreadsheet.

When Might I Use A Spreadsheet Instead of a Google Form?

If Your Data Will Be Very Textual and Complex

If you want to gather lots and lots of long bits of text, a Google Form might not be the best way for people to enter that data, mainly because a Form expects you add your data in one sitting. ( You can allow people to be able to edit their responses, but somehow this still doesn't feel right ).

We've found that when data is long and textual, even viewing it in a standard spreadsheet if difficult, and so have created lots of scripts to render someone's form data into a Google Document in a more readable format.

If Your Data Needs To Be Filled In Collaboratively

It's much easier to work on a shared spreadsheet that looks like a form than in a per-row spreadsheet.
Additional benefits of using spreadsheets, as opposed to Forms, include:

• all the changes are saved in the file's "Revision History". You can see who added or deleted what and when.
• All the edits are saved on-the-fly.
• You can easily lock down parts of the spreadsheet so that people don't accidentally change it.

The "Insert Comment" feature, with the ability to "+" add someone is fantastic. If a part of the input isn't clear to you, you can ask for help from someone and they get a link to come and comment on that part of the document.

If Your Data Is Modular

In our spreadsheet, we even have a template sheet that, using custom made menus can be used to make "more sheets like this one". This is great in those circumstances when you might want people to submit something like recipes, each with the same items ( such as ingredients, method and picture ) and you want to be able to allow them to add one, two or twenty recipes.

Conclusions

So far, we haven't used this in anger yet, but as an approach I really like it. It uses a combination of Google Apps and doesn't get too fancy. There is a huge temptation to make this sort of system do things that maybe don't need doing.

Keeping it simple has been the main design ethic.

Most of the things we thought we'd need in terms of functionalities have turned out to already exist ( such as "Named and Protected Ranges" ) in the tools themselves, OR they can be achieved by just agreeing to work a certain way ( no software creation required ).

One of the things I like about this approach is that at the end of the day, they are all just first class Google files that one could, if need be, keep updated by hand, but it makes the whole process easier by gently easing all the things you'd need to do by hand, sending emails, adding permissions, creating files, adding calendar entries etc.

I'll let you know how we get on.

By: Unknown On 5:40 AM

Continue Reading

Using Google Spreadsheets To Improve Student Accommodation

The Problem

Tim Saunders, is fast becoming the poster boy for my belief that getting non-technical people coding is good idea.

Tim started in the University of York accommodation office and inherited a task of managing students' requests to change their room.

This room changing process was paper-based and requires various peoples' agreement and signatures. It resulted in a student having to carry an increasingly dog-eared form to college administrators and back to Tim and then to the old college administrators. It was slow, actively encouraged signature forging and reliably error prone.

All the data collected then needed to be entered into SITS, our student database, which involves various charging and set up costs, so it really helps if this data is correct, having being verified by everyone in the chain.

The Solution

Armed with a some self-taught Javascript using the online learning platform CodeAcademy, Tim thought that the pile of paper forms in his office and queue of frustrated students could be improved with some Google applications.

He used a combination of tools to help manage the flow of information, including Google Forms, Spreadsheets and Apps Script to automatically send emails to college administrators and students.

The (massively simplified version of the ) process begins with a student filling out a Request to Transfer form ( shown below ).

The data collected from the form is added to spreadsheet (shown below) that has extra data integrated with it about the rooms features and contract details. An email is sent to the current college administrator that they can then approve or deny and then, once approved it is then sent to the prospective college administrator to confirm availability.

In the spreadsheet above you can see notes on when to "Check CA ( College Administrator ) and tools to fire off a particular email. The colouring of the spreadsheet is done automatically to help with navigating and understanding what the overall status of accommodation requests is.

Once agreed, students then receive an email in which an additional form is used to confirm their order. ( shown below ).

Once everything is agreed and confirmed, the data needed is sent to Tim in a format that means he doesn't have to enter it manually into our student database, SITS. A poor man's API if you will and still massively better than entering names, numbers and data by hand!


Interestingly, Tim and the Accommodation can now also for the first time, see the whole process from a bird's eye view by using the charting tools in the spreadsheet to see, from which college the most requests are being received, and when the most requests come in ( the start of term ).

One thing that I really like about Tim's implementation in this project is its lightweight approach. They didn't sit back and design a whole complicated web application that ultimately wouldn't have fit the many edge cases and workarounds in these sorts of scenarios. I like how the system primarily uses email, something College Administrators are comfortable with, and more importantly, remember to do.

Now that Tim has been promoted, the next real challenge for Accommodation ( and Tim ) is to find the best way to ensure that all this work is usable and maintainable by the Accommodation team. I know they're doing all they can to make sure that it is well documented and as collaboration-friendly as possible. They're even considering screencasts to help explain how the pieces fit together.


Tim Saunders, (like Paul Bushell in Estates with his Dashboard system ) has shown that anyone who wants to can take control of both messy processes and code to make life better, not just for themselves ( even though that would be justification enough ) but for students and colleague too. What was a slow, arduous, error prone process is now elegantly handled in a few Google Spreadsheets and Google Forms.

By: Unknown On 3:27 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