Assessment

6 Comments

I’ve mentioned the appear.in service a couple of times. This allows you to convene small meetings (up to 8 people) with video, voice and chat without the need for logins or additional browser plugins on both desktop and mobile (my quick video demo here). Today I got an email from appear.in saying

Get notified when someone enters your room!

We have now made it even easier to start a video conversation. When someone enters your appear.in room, you will receive a desktop notification that you can click to enter the room.

How can you use notifications?

  • Get notified when someone shows up for a meeting
  • People who want to talk to you can just go into your room
  • Make sure everyone on your team is alerted when your team meetings start

Read more on our blog.

Rooms you followNotifications work using a Chrome extension, but once you have this installed to can monitor multiple rooms.

So if you were wanting to run remote tutor support hours you could claim an appear.in room and enable notifications. Once you advertise your office ours you can monitor the room, get on with other work and wait for notification.

Because appear.in allows you to ‘lock rooms’ if you are providing one to one support you can prevent someone else ‘walking in’.

The awkward bit is handling the locked room. There is no queuing service and anyone visiting a locked room will be presented with the message below. Unfortunately if someone visits a locked room, sees the locked message when the message doesn’t go away when the room is unlocked.

Locked room

A way around this might be to have two rooms – corridor and office. The corridor room would always be open. As people arrive in the corridor room you could greet them and invite them to your ‘office’ and lock the office during consultation. Once done you could go back to the ‘corridor’ room if anyone else is waiting. If the ‘corridor’ gets busy (more than 7) you’ll have to sit in it yourself or lose the ability to enter (unless as an owner you get priority).

[Writing this it’s all sounding very faffy. I’d imagine you could do something similar with Google Hangouts but I love the fact appear.in requires no login. What do you think?]

Share this post on:
| | |
Posted in Feedback, Half baked, Mashup on by .

9 Comments

Update 16/06/2014: This idea has been revisited by the Elevate team at University Campus Suffolk. You can read more about and get a copy of the code here

 Open Badges Issuer Gadget for Google SitesWant to issue badges in Google Sites? That was the challenge I set myself. My solution is the Open Badges Issuer Gadget for Google Sites. This gadget wraps the Mozilla Issuer API to allow you to issue badges from a Google Site. To use the gadget is insert into a Google Site and prefix (base url) is set for your Assertion JSON. To allow users to collect their badges direct them to the web address of your Site page containing the gadget adding ?claim_code={assertion url post fix}.

For example, if my Site page with the Issuer Gadget is found at https://sites.google.com/a/hawksey.info/openbadges and by Assertion files are all found in the directory http://mysite.ac.uk/badges/ this would be my base url. If one of my Assertion files in this directory was v1mhaws.json, to claim the badge for that person I’d send them a link to https://sites.google.com/a/hawksey.info/openbadges?claim_code=v1mhaws.json

Get the Open Badges Issuer Gadget for Google Sites

The Open Badges Issuer Gadget for Google Sites is located here:

http://hosting.gmodules.com/ig/gadgets/file/108150762089462716664/openbadges.xml

To add to your own site

  1. Open/create a page on your Google Site
  2. Selecting Insert > More gadgets,
  3. Add gadget by URL inserting the address http://hosting.gmodules.com/ig/gadgets/file/108150762089462716664/openbadges.xml
  4. Insert a prefix (base url) for your Assertion JSON files (you can leave this blank if the host site varies) and click ‘Ok’
  5. For each or collections of Assertions direct users to visit the page your gadget is hosted on adding ?claim_code= followed by a comma separated list of the remainder of you Assertion file locations

Try it

If you’d like to try the gadget complete this form and you’ll be issued with a Gadget Issuer User Badge. Get the question right and you’ll also get the Gadget Issuer Gold Badge.

How the gadget works

For those of you unfamiliar with gadgets/widgets they are an easy way to embed content in other gadget/widget compatible sites. The majority of gadgets are simply XML wrappers for HTML content. The great thing is that gadgets can include JavaScript that doesn’t get sanitized/stripped out. If you want more information about making gadgets see My First Google Gadget. The source code for the is linked to above but can also be viewed on GitHub. Essentially it’s a wrapper for Mozilla’s Issuer API

The Issuer API is a [java]script that can be dropped-in to any badge issuer's website to provide a way for users to add an issuer's badges to their backpack.

Feel free to modify the gadget code to handle the success and error callbacks.

Yep I’m issuing badges from a Google Form/Spreadsheet, here’s how

If you tried the demo you might be wondering how I went from a Google Form to issuing some badges. Here’s how. Google Spreadsheets includes Google Apps Script, a cloud scripting language with uses the JavaScript Syntax to automate processes across Google products and third party services and deploy/publish custom applications and data. Apps Script includes a Content Service, which amongst other things lets you publish JSON data. As the metadata blobs behind open badges are JSON based we can use Apps Script to process the form responses, email the recipient and create the JSON … well almost.

An issue with JSON files generated by App Script  is security measures put in place by Google prevent the cross-domain use when called by AJAX as used by the Issuer API. So currently I have to proxy the JSON via my webhost (Mozilla could fix this by also permitting JSONP, which can also be produced by Apps Script. I imagine this is however low priority. If you have any thoughts on other ways leave a comment).

Here’s a copy of the code running behind my Google Form (you’ll also need to include a rollup of the CryptoJS sha256 library to hash and salt the recipient’s email address).

[A pointer if you want to extend this work is you might want to use the native NoSQL style ScriptDb Service part of Google Apps Script to prepare and serve assertions. Also I found the Open Badges Validator is very useful for testing your Assertions.]

Related work

Some recent posts/upcoming events that have influenced this post are:

So what do you think? Inspired to issue badges from Google Apps? Have I missed anything? What isn’t clear? I look forward to your thoughts ;)

2 Comments

This method uses a UK based SMS gateway and most likely not suitable for international use

Two years ago I wrote how you could have a  free SMS voting using intelliSoftware SMS Gateway service. This recipe automatically forwarded text messages from the IntelliSoftware SMS gateway to a blogger account using posting via email. Votes were then extracted from messages from the blogs RSS feed using some PHP code on my server.

Last year a modified version of this was used to collect votes for the poster competition at eAssessment Scotland 2011. I was recently asked if the recipe would still work for this year’s conference. It does but I thought I could make it better.

30 lines of code, source is in the templateThe main change is to directly ingest SMS messages into a Google Spreadsheet (using 30 lines of code) which makes it easier for manipulation and presentation. The method for doing this is relatively simple because the IntelliSoftware gateway has a HTTP interface and you can also use Google Spreadsheets as a Database – INSERT with Apps Script form POST/GET submit method.

If you would like to do this yourself here’s how:

  1. Signup for an account at intelliSoftware (it’s free!)
    Note: the username you select is also used to direct texts so you might want to use a class or course name)
  2. Open a copy of this Google Spreadsheet template (also free)
  3. Open Tools > Script editor...
  4. Select Run > setup and okay, then Publish > Deploy as web app.. and:
    - enter Project Version name and click 'Save New Version'
    - set execute web app 'as me'
    - security level as 'anyone, even anonymously'
  5. Click Update and copy the service url you are given (it will look like https://script.google.com/macros/s/[random_characters]/exec
  6. Now open your IntelliSoftware control panel
  7. Click on Forwarding and change, tick 'Enable incoming message forwarding' and change forwarding type to http
  8. Copy the web app url into the address field and click Save

To receive messages tell users to send a text message to 07786 XXX XXX with ‘xyz and their message’ (where 07786 XXX XXX is the mobile number found in the Trial Service section and xyz is your username created with intelliSoftware).

Simple response graphIn the example template I show how you can parse messages to generate a response graph. You might also want to look at how I’ve used a Google Form for Hacking stuff together with Google Spreadsheets: A simple electronic voting system, at the very basic level you’ve got a free SMS textwall to play with. If you do come up with any interesting mashups please leave a note in the comments :)

1 Comment

Here is some text I prepared for a possible Google Apps Developer blog guest post. It doesn’t look like it’s going to get published so rather than letting it go to waste I thought I’d publish here:


Martin Hawksey is a Learning Technology Advisor for the JISC funded Centre for Educational Technology and Interoperability Standards (JISC CETIS) based in the UK. Prior to joining JISC CETIS, and in his spare time, Martin has been exploring the use of Google Apps and Apps Script for education. In this post Martin highlights some features of a Google Apps Script solution which combines Google Spreadsheet and Google Documents to speed up and standardise personal feedback returned to students at Loughborough College.

One of things that drew me to Apps Script over two years ago was the ease in which you could interact with other Google services. I also found that both using Google Spreadsheets and a coding syntax I recognised ideal as a ‘hobbyist’ programmer.

Late last year when I was approached by Loughborough College to take part in their ‘Fast Tracking Feedback’ project, I saw it as an ideal opportunity to get staff using Apps Script  and showcase the possibilities of Apps Script to the Google Apps for Education community.

The goal of the project was to produce a mechanism that allows tutors to input assignment grades using a custom UI that mirrors the final feedback sheet or enter details directly into a Google Spreadsheet.  These details are then pushed out as individually personalised Google Documents shared with the student. This sounds relatively simple, but the complication is that each assignment needs to map to a predefined set of rubrics which vary between units. For example in one course alone there are over 40 units and every unit can be assessed using multiple assignments with any combination of predefined criteria ranging from pass, merit and distinction.

Below is an example student feedback form highlighting the regions that are different for each assignment.

Example student feedback form highlighting the regions that are different for each assignment

The video below shows a demonstration of the current version of the of the ‘Fast Tracking Feedback’ system is set-up and used:

Solution highlights

A number of Apps Script Services have been used as part of this project. Lets look at how some of these have been implemented.

DocList Service – The self-filing Google Spreadsheet

The eventual plan is to rollout the Fast Tracking Feedback system to teaching teams across the College. To make the life of support staff easier it was decided to use a common filing structure. Using a standardised structure will help tutors stay organised and aid creation of support documentation.

When a tutor runs the setup function on a new feedback spreadsheet it checks that the correct folder structure exists (if not making it) and moves the current spreadsheet into the pre-defined collection.


Self-generating folder structure and organization

The code that does this is:

// code to generate folder structure and move spreadsheet into right location 
// + ROOT_FOLDER 
// |- SPREADSHEET_FOLDER 
// |- DRAFT_FOLDER 
// |- RELEASED_FOLDER 
var rootFolder = folderMakeReturn(ROOT_FOLDER); // get a the system route folder (if it deosn't existing make it 
// create/get draft and release folders 
var draftFolder = folderMakeReturn(DRAFT_FOLDER,rootFolder, ROOT_FOLDER+"/"+DRAFT_FOLDER); 
var releaseFolder = folderMakeReturn(RELEASED_FOLDER,rootFolder, ROOT_FOLDER+"/"+RELEASED_FOLDER); 
var spreadsheetFolder = folderMakeReturn(SPREADSHEET_FOLDER, rootFolder, ROOT_FOLDER+"/"+SPREADSHEET_FOLDER); 

// move spreadsheet to spreadhsheet folder 
var file = DocsList.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()); 
file.addToFolder(spreadsheetFolder); 

// function to see if folder exists in DocList and returns it 
// (optional - if it doesn't exist then makes it) 
function folderMakeReturn(folderName,optFolder,optFolderPath){ 
try { 
   if (optFolderPath != undefined){ 
     var folder = DocsList.getFolder(optFolderPath); 
   } else { 
     var folder = DocsList.getFolder(folderName); 
   } 
   return folder; 
} catch(e) { 
   if (optFolder == undefined) { 
     var folder = DocsList.createFolder(folderName); 
   } else { 
     var folder = optFolder.createFolder(folderName); 
   } 
   return folder; 
} 
}

UI Service – Hybrid approach

A central design consideration was to make the Fast Tracking Feedback system easy for College staff to support and change. Consequently wherever possible the Apps Script GUI Builder was used to create as much of the user interface as possible. Because of the dynamic nature of the assessment rubrics part of the form is added by selecting an element holder and adding labels, select lists and textareas. Other parts of the form like the student information at the top can be added and populated with data by using the GUI Builder to insert textfields which are named using normalized names matching the spreadsheet column headers. The snippet of code that does this is:

app.getElementById(NORMHEADER[i]).setText(row[NORMHEADER[i]]);

Where NORMHEADER is an array of the normalized spreadsheet column names and row is a JavaScript Object of the row data generated based on the Reading Spreadsheet data Apps Script Tutorial.

Hybrid UI construction using GUI Builder and coding

Document Services – Master and custom templates

The process for filling in personalized feedback forms has three main steps. First a duplicate of the Master Template is made giving it a temporary name (DocList Services). Next the required assessment criteria are added to the form using the Document Services mainly using the TableCell Class. Parts of the document that are going to be filled with data from the spreadsheet are identified using a similar technique to the Apps Script Simple Mail Merge Tutorial. Finally for each student the assignment specific template is duplicated and filled with their personalised feedback.

if (email && (row.feedbackLink =="" || row.feedbackLink == undefined)){
  // Get document template, copy it as a new temp doc, and save the Doc's id
  var copyId   = DocsList.getFileById(newTemplateId)
                         .makeCopy(file_prefix+" - "+email)
                         .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  // move doc to tutors folder
  var file = DocsList.getFileById(copyId);
  var folder = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_FOLDER);
  file.addToFolder(folder);

  // select the document body
  var copyBody = copyDoc.getActiveSection();

  // find edittable parts of the document
  var keys = createKeys(copyDoc);

  // loop through elements replacing text with values from spreadsheet
  for (var j in keys) {
    var text = keys[j].text;
    var replacementText = ""; // set the default replacement text to blank
    if (row[keys[j].id] != undefined){ // if column value is defined get text
      replacementText = row[keys[j].id];
    }
    copyBody.replaceText('{%'+keys[j].text+'%}', replacementText); // replace text
  }
  copyDoc.saveAndClose();

  // create a link to the document in the spreadsheet
  FEEDSHEET.getRange("B"+(parseInt(i)+startRow)).setFormula('=HYPERLINK("'+file.getUrl()+'", "'+copyId+'")');
  FEEDSHEET.getRange("C"+(parseInt(i)+startRow)).setValue("Draft");
  // you can do other things here like email a link to the document to the student
}

Currently the system is configured to place generated feedback forms into a draft folder. Once the tutor is happy for the feedback to be released either individual or class feedback forms are distributed to students from a menu option in the feedback spreadsheet for the assignment, a record being kept of the status and location of the document.

Easy record keeping

Next steps/Get the code

The Fast Tracking Feedback System is currently being piloted with a small group of staff at Loughborough College. Comments from staff will be used to refine the system over the next couple of months. The current source code and associated template files are available from here.

The Fast Tracking Feedback project is funded by the UK’s LSIS Leadership in Technology (LIT) grant scheme.

2 Comments

Next week I’ll be presenting at Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) doing a session on ‘Hacking stuff together with Google Spreadsheets’ – other online spreadsheet tools are available. As part of this session I’ll be rolling out some new examples. Here’s one I’ve quickly thrown together to demonstrate UNIQUE and FILTER spreadsheet formula. It’s yet another example of me visiting the topic of electronic voting systems (clickers). The last time I did this it was gEVS – An idea for a Google Form/Visualization mashup for electronic voting, which used a single Google Form as a voting entry page and rendering the data using the Google Chart API on a separate webpage. This time round everything is done in the spreadsheet so it makes it easier to use/reuse. Below is a short video of the template in action followed by a quick explanation of how it works.

Here’s the:

*** Quick Clicker Voting System Template ***

The instructions on the ‘Readme’ tell you how to setup. If you are using this on a Google Apps domain (Google Apps for Education), then it’s possible to also record the respondents username.

record the respondents username

All the magic is happening in the FILTERED sheet. In column A cell 1 (which is hidden) there is the formula =UNIQUE(ALL!C:C). This returns a list of unique questions ids from the ALL sheet. If you now highlight cell D2 of the FILTERED sheet and select Data > Validation you can see these values are used to create a select list.

create a select list

The last bit of magic is in cells D4:D8. The first half of the formula [IF(ISNA(FILTER(ALL!D:D,ALL!C:C=$D$2,ALL!D:D=C4))] checks if there is any data. The important bit is:

COUNTA(FILTER(ALL!D:D,ALL!C:C=$D$2,ALL!D:D=C4))

This FILTERs column D of the ALL sheet using the condition that column C of ALL sheet matches what is in D2 and column D matches the right response option. This formula would return rows of data that match the query so if there are threee A responses for a particular question, three As would be inserted, one on each row. All we want is the number of rows the filter would return so it is wrapped in COUNTA (count array).

Simple, yes?

3 Comments

Note: This is a personal post made outwith my current employment at JISC CETIS

Back in May 2011 Tony Hirst looked at the Visual UI Editor For Google Apps Script and commented that he thought before long I would have posted something about it. Well almost a year later here’s what I’ve got for you.  As part of the Fast-tracking feedback project (funded by the LSIS Leadership in Technology grant) with Loughborough College I ran a training session at the beginning of the month to help staff learn about user interface construction in Google Apps Script. The session follows on one of my earlier blog posts releasing some code to batch fill in Google Docs from a spreadsheet of feedback comments. As part of the session I produced a step-by-step guide for creating a Google Sites based form/gadget that could read and write data to a spreadsheet. As part of the project this guide is available for re-use using the link below. Before you download/use a couple of things worth bearing in mind:

  • the guide has been tweaked slightly for publication and as a consequence I may have inadvertently broken it. If you find something is wrong leave a comment in the document
  • in the guide an image is used to help you layout a form and is not intended to be part of the final navigation

Introduction to Google Apps Script: Custom Interfaces Guide

4 Comments

In October last year the Sport Learning Technologists at Loughborough College successfully won funding from the LSIS Leadership in Technology (LIT) grant scheme for the Fast-tracking feedback using Google Scripts project. Here’s an extract from the project summary:

This project will effectively combine Google Apps for Education and Google Apps Script in order to create a tool which allows tutors to enter grades and feedback in a single spreadsheet which then automatically populates individual feedback proforma, simultaneously sharing these results with students, progress tutors, and administrators as appropriate.

The benefit will be an increase in the efficiency with which assessment feedback can be shared, improving the speed and quality of paper-less student feedback. A successful conclusion to this project will be demonstrated by reduced submission turnaround times and a reduction in the errors brought about by inconsistencies in data entry.

Project funding is not just for deploying technology but also increases the capacity within the organisation at the operational level. With this in mind I have been working with Loughborough, helping them in the technical aspects of developing the Fast-Tracking Feedback System and also learn about Google Apps Script via a series of workshops. Friday was the first of these and I thought I’d share the story so far.  

The Loughborough group had already got of to a flying start successfully modifying My #eas11 world premier: Creating personalised Google Documents from Form submissions. 5 months is a long time in Google Apps Script and since then not only is there some new functionality in Apps Script, but I’ve also picked up some new tips. My own understanding has come on along way thanks to receiving a preview copy of Google Script: Enterprise Application Essentials by James Ferreira [due out 27th January]. I’ve been a regular visitor to James simpleappssolutions.com site and tutorials so wasn’t sure if his book would teach me much more, but how I was wrong. Part of the reason I believe for this is the book is geared towards ‘enterprise applications’ so concentrates on documents and workflows, just as assessment in education (for better or worse) is concentrated on documents and workflows.   

So below are two links of the current version of the Google Apps Script Spreadsheet and example Document template followed by a quick video to show how it is used. Obviously these are still work in progress as there is still 6 months to run on the project but there’s already enough there for others to benefit from and perhaps feedback on design.

Stay tuned for more developments

14 Comments

Back in April 2009 I posted  Evernote – a personal e-portfolio solution for students?. In the post I highlighted how the features of this young start-up potentially made it a nice solution for a FREE ‘personal’ e-portfolio (that is, removed from the shackles of institutionally bought systems). At the time though I did point out some potential shortcomings:

  • lack of mobile application for non iPhone/iPod Touch and Windows Mobile users
  • an easy way to privately share assests
  • notes are stored in proprietary Evernote format
  • the limit to only uploading pdf documents with the basic free service 

Over time these original issues have been whittled down.

Mobile - In May 2009 it was announced Evernote for BlackBerry Is Here and then in December Evernote for Android: It’s here! and there have been been numerous software updates and enhancement for tablet devices when they come along.

Sharing – From January 2010 there have been several updates adding note sharing with Mac, web, Windows and mobile apps. Sharing isn’t done privately instead using ‘security by obscurity’ (having publically available notes accessed via an obscure url). Update: Oops You'll see from the comment below that it is possible to share notebooks privately. From the sharing knowledge base:

Evernote allows both free and premium users to share notebooks privately with other Evernote users. Notebooks shared by premium users have the option of being editable by the users with whom the notebook is shared. In other words, if Bob the premium user shares a notebook with Fred the free user, Bob may choose to allow Fred to edit the contents of his shared notebook.

Export – When I started presenting Evernote as a personal e-portfolio system back in 2009 one of the questions I usually got asked is how could a student back-up or export notes stored on Evernote servers. At the time the desktop clients for Mac and Windows, which synchronise with Evernote so that you always have a local and remote copy of your files, could export your notes in a proprietary XML format. This meant you could import them into another Evernote account but that was it. In May 2009 Evernote however started rolling out html export for single or batches of notes starting with Mac (May 2009) and eventually getting around to Windows (November 2010).

File types – Back in April this was the deal breaker for me. With the free account you could only upload text, image, audio and PDF files. Having a place to also backup word documents and other electronic resources as well as making this searchable was the one thing I thought would put most tutors off of suggesting Evernote as a tool for their students. Fortunately this month (September 2011) Evernote announced that they had Removed File Type Restrictions for Free Accounts.

So what’s left? Will you be recommending Evernote to your students?

PS Here's a collection of links from Purdue University on Evernote in Education and not surprisingly Evernote themselves ran an Evernote in Education Series.

PPS I recently downloaded the free Android App Droid Scan Lite which lets me snap and reshape pics of docs which I can then share to Evernote as a JPEG (Evernote OCR's images to make them searchable ;)

1 Comment

Tomorrow (Friday 26th August) for my eAssessment Scotland 2011 workshop I’ll be doing a world premier extending the form submit sheet split example to let tutors create individual student Documents from submitted data (e.g. taking a sheet like this and at a click of a button produce this document.

Update: Still some refinement in the instructions below. In the meantime if you want to reverse engineer here is a copy of the final spreadsheet and Google Document Template used. David Sloan from the University of Dundee who was at the workshop has also usefully highlighted some accessibility issues to be aware of when using Google Apps.

[gview file="http://mcdn.hawksey.info/wp-content/uploads/2011/08/Using-Google-Forms-for-Assessment-and-More1.pdf"]

1 Comment

Even though our RSC is toast after this July we’ve continued our support for the eAssessment Scotland Conference until that time. Our involvement with eAS goes back to the very beginning with 2009’s Strategies across sectors and since then this event has continued to grow becoming the largest UK conference dedicated to e-assessment.

For this year we’ve moved the booking form to Google Spreadsheets (moving to the cloud to avoid the issue of our soon to be mothballed event system), which has been an opportunity to continue to tweak my previous work on Google Spreadsheet based booking systems (Event Manager v3, Game To Learn: Take 2, framework for custom interfaces). For this version I’ve implemented:

  • 3rd party website integration (e.g. hosting the form remotely and in our case wrapping it in a WordPress template);
  • better handling of parallel session selection e.g. quotas for maximum number of delegates;
  • email notification confirming booking details and registration; and 
  • Twitter integration with automatic ‘tweet out’ of registrations and adding delegates to a named Twitter List

I realise not everyone is going to be interested in all of this, but the solution is modular so you can pick the bits you like. So if you want to replicate any of this here’s how to do it (video followed by written instructions):

Setting up the Spreadsheet

To get you started I’ve published a template for you. What this has is a basic outline with the beginnings of a Google Form and some templates for the emails sent to delegates. It also comes preloaded with some Google Apps Script to handle the emailing and Twitter stuff:

*** Booking Form with Twitter Integration Template ***
BTW if anyone knows a better way of sharing templates could you let me know (avoiding the iframe embed because it’ll be stripped in some RSS readers)

From the link above click ‘Use this spreadsheet’. This will take you in to the form view. From here you can add additional questions (you can edit at a later point if you prefer). Protected names (e.g. field names that are used later in the script) are First Name, Email Address, Twitter Username and ‘Tweet Out’. You can change these if you really need to, but you’ll also have to find them in the script.

Optional – Parallel session selection

If you want to include parallel session selection Add item > Choose from a list to the form. Start you session option text with a unique identifier e.g. ‘[PM3] App, App and Away’. You can also have multiple parallel sessions, morning and afternoon etc. Some tweaking is required on the main spreadsheet to access this go to ‘See responses –> Spreadsheet’. Next:

  • On the ‘Public’ sheet enter the sessionIDs and sessionName you used in the Form. Note: these must exactly match the Form values or the embed later on won’t work.
  • In the Max column you can also change the number of delegates.
  • In the Count column there is a formula that counts the of the sessionID in the ‘Bookings’ sheet.
  • If you are embedding the form (as explained later) you’ll also need to publish the Public sheet by selecting Share –> Publish as a web page and changing Sheets to publish to ‘Public’.

Spreadsheet setup cont’d

If you are not already in spreadsheet view click on ‘See responses –> Spreadsheet’. Next you might want to edit the spreadsheet ‘Templates’ sheet. The workflow to bear in mind when filling this in is:

  • Delegate fills in form
  • On submit they get automated email response summarizing entered data
  • Confirming the booking allows the admin to send confirmation email, adding delegate to twitter list and if allowed ‘tweets out’ booking

To incorporate other booking information in the confirmation emails you can use the markup ${"The column name"} in the template eg ${"Email Address"}

Optional - Connecting to Twitter

Adding the twitter bits is another optional bit. If you want to do it then in the Template sheet there are some cells for a default twitter message and twitter list (if you are using a Twitter list of delegates you’ll need to create this in Twitter as well). You’ll also need to register for a Twitter API key which is both free and relatively painless. Below are the instructions for registering for a API key and using it with the spreadsheet:

  1. Register for an API key with Twitter (if you've already registered a Google Spreadsheet/Twitter mashup you can reuse your existing Consumer Key/Consumer Secret).
  2. In the form these are the important bits: 
         - Application Website = anything you like
         - Application Type = Browser
         - Callback URL = https://spreadsheets.google.com/macros
         - Default Access type = Read & Write
  3. Once finished filling in the form and accepting Twitter's terms and conditions you'll see a summary page which includes a Consumer Key and Consumer Secret. Take a copy of these as you'll need them later.
  4. Back in the spreadsheet there should be a Bookings menu option with Configure Twitter (you might need to select this option twice, once to authorize the script and a second time to enter the customer key and secret saved from earlier.
  5. Now in the Spreadsheet select Tools > Script Editor ... and from the Script Editor window select Run > authorize.
  6. This should bring up an authorization message box, click on Authorize.
  7. In the Twitter Authorization window enter your login details of the event Twitter account and select 'Allow'.

Now, time for a cup of tea …

Quick recap: we’ve copied the template spreadsheet; edited the booking form; customised the email templates; and if needed connected the spreadsheet to twitter. Next I’m going to show you how to embed the Google Form in another website. This is again an optional step, you can just use the live Google Form but you won’t be able to prevent full parallel sessions from being selected (unless you manually remove them from the form).

Optional – Embedding booking form in to another website (in this case WordPress)

To do this you are going to need access to a webserver where you can host a .php file. I’ve created two versions of the file you’ll need. There is the:

Both of these files need a bit of editing. In both files some variables are needed:

  • $formKey – in your Google Spreadsheet click on Booking –> Get Form Key and copy the value from the dialog box
  • $redirectUrl – this is the page you want to send users to after they submit their booking
  • $dataUrl – you can get this value by opening your Google Spreadsheet and clicking Share (top right) –> Publish as webpage. This should open a window similar to the one below. Things to check are you are publishing ‘Public’ with automatic republish on changes and that the get a linkto the published data is CSV and the Public sheet. The value you need for $dataUrl is the entire link generated in the text area.

Url for the session counts required for the booking form

What the code does is read the Google Form and sessions counts and then modifies a copy of the original form. To prevent incomplete data from being submitting the code also uses the jQuery Validation plugin to check the data.

Required fields and format are set around line 90. The plugin documentation contains information on additional configuration but hopefully you can see the pattern of field name (as defined in the form html source) and requirements. So in the example code:

  • entry.2.single is required
  • entry.5.single is required and in email format
  • entry.6.single is required and a valid twitter username without ‘@’
  • entry.8.single is used for a parallel selection. If a selected session is full it has no value so the delegate is asked to select again 

Summary

At this point you are probably due another cup of tea. I realise that this isn’t an ‘out of the box’ solution, but hopefully there is another of an outline to help you do something similar/use information here to solve a different problem/loose many hours of sleep.

Event booking amplification using Twitter

The Twitter integration still intrigues me. Currently 80% of delegates have allowed us to ‘tweet out’ their booking, which is a lot higher than I anticipated. At the same time none of the delegates with Twitter accounts have retweeted their ‘tweet out’ meaning that in terms of amplification the information is only reaching the followers of @eassessscotland. May be the next iteration should be to get delegates to optionally sign in with Twitter, pre-populating some of their registration form and then choose to tweet out on their own account as well as the conference account. [It would also be good if the process could add delegates to the lanyrd site, but their API doesn’t allow this … yet   

PS I’ll be doing my App, App and Away workshop at eAS11 which has a couple of places left. This session covers very simple uses of Google Forms and Apps Script to support assessment.