Building A Booking System With Google Apps

In my previous post, Building a Booking System With Google Apps, I tried to use Google's UI Builder to be a front-end to saving events into a Bookings Calendar for students to book hot desks in the Berrick Saul Treehouse. I wasn't totally happy with the results... still.

Lately, I have tried a completely different, simpler approach ( with quite a few groans about icky Google Docs issues along the way ) which has a sort of spreadsheet visualisation of the bookings that have been made. 


It looks and works like this.

Rather than taking apart the code in fragments, the entire spreadsheet is available here. Go take a look, from the File menu choose Make A Copy. 

If you create a Calendar and change the Calendar ID in the Script Editor you might be able to get it working for you. There are setup scripts to generate a "calendar-like" spreadsheet. The perches sheet has a list of columns that you might want to alter to suit your needs. The weirdest part is the "Booking Queue" sheet, which is essentially a huge workaround. 

Do let me know if you work out how to do any of this in a cleaner/better/faster way. Thanks.

By: Unknown On 7:31 AM

Continue Reading

Building a Booking System with Google Apps ( THE MOAN! )

In a previous post I shared my initial attempts to create a Booking System with Google Apps. The approach was simple enough... I created a very simple form ( using Google Apps UI Builder ) and I would store peoples' booking information straight into a calendar, with some other bits and bobs being stored in a spreadsheet ( like the list of bookable things ).

The end result of my last attempt was passable, but not something I was particularly proud of in any way. So, my next step was to try and make it a bit more robust and maybe try some other approaches.

So. I feel the need to get this post out of the way first. It's a massive moan and will only clutter up the post that follows this one. The moans below are either because of Google Apps peculiarities or because of my stupidity. I'm the last person to be able to tell which is which.

Google Apps MOAN, MOAN, MOAN

Creation of Events in Google Calendar Is A Pain

When creating an event in a Google Calendar using AppsScript, the executing code isn't stopped ( until the event exists). This seems to be a known error/feature and is there because Google want to ensure your data is OK. Well thank you very much Google. Maybe that's why they also return the non-sensical error message "Service error: Calendar : Mismatch: etags = ". Very handy. Thanks.

The upshot of this feature is that when you create an event, with a date and title, you can't then alter anything about that event. You can't add a description, or add guests or anything.... UNTIL a non-specified amount of time has passed. I found 10 seconds worked ( Utilities.sleep( 10000) ) sometimes, but not reliably. It also breaks if you try and create an event with all the parameters in one go.

What this means, is that you can't have an application that creates events that is tied to a User Interface, because people aren't that patient. I even tried adding code that kept trying to create an event in a while loop.

    while( keepTryingToCreateSoddingEvent(title, date, email, notes)){
      Utilities.sleep(3000)
     }

Not fun. And it didn't work.

All Day Events Are A Pain

I've distant memories of this being a pain in other languages. When creating All Day Events, you have to pass in a DateTime object... which the observant will notice, have a TIME bit. I found myself having to do this...

date.setHours(0,0,0,0)

var event = cal.createAllDayEvent(title, date)

...just to be sure. And it still failed. Interestingly, I ended with a checkerboard effect calendar where "every other day" failed to be created. I'm guessing this is down to a BST issue with trying to create an All Day Event from 1AM to the next day at 1AM...  Who knows.... I couldn't get it working. I found if I made events 09:00AM to 17:00PM they worked more often.

Permissions of Access Issues

I'm guessing this is going to bite me in the arse anytime soon. I have created a solution ( read mess) that is a spreadsheet and a calendar with some AppsScript lurking. I'm not entirely sure what I have to do to make all of this usable by somebody else in terms of permissions. 

I have tried to use the permission calls to lock down the editing of certain sheets, but this isn't what I expect the issue will be. I can't wait.

Missing onOpen

This is an odd one. I think, if you add code not in a function in an AppsScript, that it may fiddle with onOpen getting called. So the first time I "tested" this spreadsheet with someone that wasn't me, the menu I'd created didn't load. Great.

Speed of the UI

The speed of the user interface ( UI ) form both in terms of loading and user interaction responsiveness isn't massively impressive. I thought I'd try a trick of closing the UI directing after the user clicks the submit button, to give the illusion of speed, but unless I bang in a Browser.msgBox dialog box, the experience is like wading through treacle in a long leather skirt with diving boots on.

Loading of Shared Resources

I'm not convinced that loading a Calendar, or Spreadsheet on EVERY function call is fast, but when I tried to only load them onOpen, connections started getting broken. 

Lack of Bog Standard Functions/Library for working with Spreadsheet data

This is a minor gripe, but the suggested ways of working with spreadsheets, in terms of the ObjService library really doesn't match my way of thinking or my spreadsheet structure come to that. The idea that you read spreadsheet data into an array, work on that array, then write it back in one big lump just doesn't fit my data.

I event tried leveraging Google Spreadsheet functions to do some heavy lifting, but both QUERY() and FILTER() don't seem to do what I need. This may be down to my lack of understanding. I wish I knew.

So. There we have it. A BIG MOAN. Hopefully that's cleared the air enough to be able to write a useful blog post about creating a Booking System with Google Apps.

I have no idea if I've managed to cobble something together that is actually useable by the people that asked for it. I hope so. Testing needed...

By: Unknown On 5:00 AM

Continue Reading

Building a Dashboard with Google Apps

The other day I went for a chat with reluctant blogger Paul Bushnell in Estates ( pictured right) to find out what they've been doing with Google Apps. The answer is lots, almost too much to fit into one blog post, but I'll have a go.

Estates have created a system for working with the numerous fix requests they get, from broken toilets to fire alarm faults to potholes and people stuck in lifts.

1. Data Logging with Android Tablets

The team in Estates all have tablets with which they can get access to all the recent reports and log when problems have been dealt with and add any notes. For this they mainly just use simple spreadsheet data entry, rather than Forms or fancy user interfaces.

2. Workflow System 

Once a problem has been dealt with, it gets moved from one sheet, onto the next. From here, the job might get logged as "we need to make sure this never happens again" and moved into other sheets. 

3. Working With External Suppliers

In each of the spreadsheets, when a job was logged as needing parts from an external supplier, the parts were ordered. Interestingly, rather than slow down the fix time, external suppliers were given access to this part of the spreadsheet and could add notes along the lines of "we can get two of these to you by Friday". An interesting part of this is that the urgency for supplying the part is handed to the supplier, first come, first sale rather than having to order it from their system... genius.

4. Automatic Email Reports

Throughout this whole process/system, automatic email reports were sent, with real data - sometimes even chasing up people who needed a reminder. Often lots of people get these personalised mails. 

This sort of mail merging is something we think lots of people want to do at the University ( with Google Apps ) and it is quite simple. We both thought it'd be a good idea to share this Spreadsheet/code as a starting point for other people to make their own mail merge systems. 

4. KPIs For Every Spreadsheet

Of the many spreadsheets I saw, each had an "Intelligence" sheet. This was sometimes an aggregation of the data, sometimes just one number, for example, "How many toilets were fixed this month" or a trend item. The bit I found interesting was that for every spreadsheet, or clump of data there was a KPI sheet... the measurable had been thought about.

5. The Dashboard Itself

There was more than one dashboard actually, but they tended to look like this... And in it, Paul could see that Estates' fault fixing effectiveness was definitely improving over the last six months.

And the really amazing thing about this, is that, at its heart, it is just a collection of Google Spreadsheets with some timed triggers that move data around.

I've also been helping them to import and integrate automatically generated CSV data from their Fire Alarm system. I also find this interesting, not only because as you start being able to mix automated data with human-data ( if you know what I mean ) new things start being possible.

And on top of all this, this work by Estates isn't happening in some technologically arcane corner somewhere, BECAUSE it's in the cloud, using a codebase and hosting solution all of us have access to, potentially, some of the ideas or solutions in here could wind up being used by other departments. 

The only hard part is working out how and where to share this expertise well

There is, I think, a sweet spot in sharing code ( or spreadsheets or AppsScript stuff ) where it is complex enough to do the job properly (enough), but not so developed that the end solution is fixed and it can still be hacked. 

Often I find beautiful code, that works so well I have no idea how it is doing what it is doing, it is like magic, whereas what I want is something that I can look at, understand the components, take apart and put them back together in a new shape. 

So, I propose that, working with Paul, I will "take parts of his code" and share it here, breaking it down into smaller pieces that you may find useful. I also have a number of other projects I'm currently working on I could do the same with. I will try to resist the temptation to overwork them and share my work in progress here. 

I'll also keep badgering Paul ( and others ) to give this blogging thing a whirl. 

By: Unknown On 4:48 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