App, App and Away: Workshop Handout #open4ed #GAS

Friday (20th May) was our Open for Education event. There was a real buzz as over 100 delegates squeezed into the NeSC to absorb a packed programme of open and free stuff. Once we get the videos from the event up I should do a separate post to highlight some of the best bits. In the meantime below is video and workshop handout from my App, App and Away workshop. I’m already working on version 2 for e-Assessment Scotland Conference on the 26th August.


This guide was written to support the App, App and Away workshop might be delivered on the 20th May 2011 as part of Open for Education event (unless otherwise stated available under CC-BY-SA). Shortlink:

1. Background

Some more background on Google Docs has been  collected by EdTechTeam (CC-BY-SA 3.0)

1.1 What is Google Docs?

1.2 Interactive Overview (with Links to Help Pages):

2. The new glue: Google Apps Script

  • Google service to allow easy customisation of Google products and 3rd party service
  • A bit like macros but much more
  • Written using a JavaScript syntax but run on Google servers
  • Not just for the coders

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services.
With Google Apps Script you can:

  • Automate repetitive business processes (e.g. expense approvals, time-sheet tracking, ticket management, order fulfillment, and much more)
  • Link Google products with third party services (e.g. send custom emails and a calendar invitation to a list from a MySQL database)
  • Create custom spreadsheet functions
  • New! Build and collect user inputs through rich graphics interfaces and menus (e.g. a company could power an internal application for purchasing office supplies where users could shop via a customized menu interface)

2.1 Key resources

2.2 What you can interact with

Google Apps Script includes objects and methods for controlling data in the following applications.

  • Google Spreadsheets
  • Google Documents – NEW
  • Gmail Contacts
  • Google Calendar
  • Google Sites
  • Google Maps
  • Gmail – NEW
  • More …

3. Survey Form Admin/Just in Time Teaching

Before joining this workshop I sent you all a link to a survey. This was sent using a Google Apps Script. Lets look at your responses and how it was done.

4. Automated grading using Flubaroo

Made by Dave Abouavm, a Google employee, in his 20% time. Flubaroo uses Google Spreadsheet/Forms and Apps Script to automatically grade quizzes.
Create a form that can be used as a quiz. Responses to the quiz are aggregated to give an overview of class performance and students receive personalised feedback regarding their performance.

  1. Create a new spreadsheet
  2. Insert > Script then find Flubaroo in Education section
  3. After a couple of seconds accept authorize
  4. Tools > Form > Create Form
  5. Create your form including name and email fields if you want to send results. You can use any question types you like as long as the student can exactly match the correct answer
  6. Can Form > Go to live form and fill out yourself with correct responses, before sending link to students
  7. Once quiz closes go to Flubaroo > Grade quiz, identify response with correct answer
  8. Once graded you can then go to Flubaroo > Email grades (you can provide additional feedback by adding text to the correct responses.

5. Creating custom interfaces to Google Apps

Part of the Google Apps Script service allows you to create custom interfaces (UI Services). An example of this was the dialog boxes in Flubaroo. These were all written using Apps Script and as well as allowing user input can include any information accessible to Google Apps Script (other Google services and 3rd party information)
This example from Simple Apps Solutions shows the degree of control you have in terms of customising layout. Until recently this all had to be manually coded but there now is an online interface designer.

5.1 Turning Google Spreadsheets into a personal or group bookmarking service

Create an interface to Google Spreadsheet which allows you to create a Delicious style bookmarking service.

  1. Make a copy of this spreadsheet (File > Make a copy)
  2. Click on Tools > Script editor then from Run > setup (you will need to do this twice)
  3. While still in the Script Editor select Share > Publish as service.

– If you want to be the only one to add bookmarks choose ‘Allow only myself to invoke this service’
– Enable Service
– Copy the URL and paste it into cell A8 of the Readme sheet

  1. Make a custom bookmark in your browser using the code provided (javascript: …) as the url (I’ll talk you through this. Basic instructions for Internet Explorer and Firefox)
  2. Start bookmarking stuff


6. Triggers

Google Apps Scripts can be run in three ways:

  • by the user
  • time-driven
  • on event (on form submit, on open etc.)

The big advantage of automated triggers is processes can be run in the background without the need for the Spreadsheet or Site being opened by the owner.

  • Archive Tweets to a Spreadsheet – uses time driven triggers to pull search results from Twitter and store the in a Spreadsheet (potential use might be to archive class tweets

7. Other opportunities: Uploading files

Create a custom system to allow students to make online submissions of their work.

  1. Open Google Sites
  2. Create new site
  3. Choose Blank template and enter a site name and url
  4. Then select ‘More actions’ > Manage site (right hand side of the page)
  5. Select Apps Script > Add new script
  6. In the window that opens select File > Add script from gallery …
  7. Select Education category from the Script Gallery
  8. Scroll to find Submit Assignment in Google Sites and click Install, Authorize then click close
  9. Back in the Script Editor select File > Open, opening Submit Assignment in Google Sites
  10. In line 22 change the folder name value to something else e.g. var folderName = “Assignments”; then save and close this window
  11. Open Google Docs in a new window and Create new > Collection using the same name used in step 10 e.g. Assignments
  12. Back in Google Sites click on Return to site then Edit page
  13. With the cursor in the main section of the page select Insert > Apps Script Gadget
  14. Select Submit Assignment in Google Sites and click select
  15. Set the permissions and click Save, then Save again (top right)

You can now test submitting an assignment by selecting a file and clicking ‘Submit Assignment’. The file will be uploaded to you Google Docs under the collection name you used. This example could be extended to include other form elements. For example, you could incorporate textareas to create a pro-forma for the student to fill in with their submission. Pro-forma questions might be ‘What grade do you think you’ll get’, ‘What are the strong areas of you submission’ etc

8. Issues

Google Apps Script is an evolving product and new features are regularly being added. There are a couple of issues to be aware of before using Apps Script:

  • Enterprise level deployment – once used Apps Scripts can’t be automatically updated
  • Relying on ‘the cloud’ – need to be online to edit/use
  • Consuming Apps Scripts is not always straightforward – certain scripts need the user to manually configure (e.g. publish as a service, set triggers)

9. Other useful links/resources