Archive for the 'Google Apps Script' Category

gEVS – An idea for a Google Form/Visualization mashup for electronic voting

First I should say I don’t think this is the best solution, in fact an earlier post from 2008 DIY: A wi-fi student response system is probably a solution, if perhaps needing more tidying up, but I’m posting anyway just on the of chance that this might inspire or help solve someone else’s problem.

This post has come about as a result of a couple of things:

  1. I’m in a bit of a Google Apps run.
  2. I read and enjoyed Donald Clarks Clickers: mobile technology that will work in classes
  3. I saw and contributed to Tom Barrett’s crowdsourced X Interesting Ways to Use Google Forms in the Classroom (I added #39 Collaboratively building a timeline which I discovered through Derek Bruff’s class example.

Concept: Using Google Forms as an interface for a mobile/desktop based voting system.

Issue: If you are asking multiple questions the form needs prepopulating with options making it a long list for the student to navigate and potentially creating a predefined route of questions.

Solution: Use a generic form with one set of response options, the class using a unique question identifier for response graphs to be generated from.

The finished result

Below (if you aren’t reading this via RSS) is this Google Form. [You can make a copy of the related Spreadsheet and customise the text and options. For example, you might want to turn qID into a list option rather than free text.]

Loading…

And here is a page with a summary of responses, which allows the user to choose which response set to display (screenshot shown below):

Screenshoot of summary of responses

How it was done

Some of you might already be familiar with Google Chart. This service allows you to create chart images by encoding the data in the URL. I’ve used this service in a number of my mashups, in fact all of my previous voting mashups use it in some way, and not surprisingly in Generating charts from accessible data tables using the Google Charts API.

Google Chart works well if it easy for you to get the data and format it for the image URL. For more complex tasks there is Google Visualization. The advantage of Visualization is it gives you a way of querying a data source before displaying as a table or chart. To see what you can do (and the place where I aped most of the code for this mashup) you should visit the interactive gallery of Visualization examples.

Using the Using The Query Language example as a stating point I could see you could lookup data from a Google Spreadsheet and filter the response using Google Visualization API Query Language, which is very similar to SQL. What I wanted to do was SELECT the data from the spreadsheet WHERE it matched a question identifier and COUNT the number of occurrences for each GROUP of response options. An extract from the table of data is:

ABC
Timestamp qIDAnswer
-q1A
-q1B
-q1A

My attempts to convert the SQL version of this query which is something like:

SELECT C, Count(C) AS CountOfC WHERE B = ‘questionID’ GROUP BY C

initially I was left with keyboard shaped indentations on my forehead trying to get this to work but Tony Hirst (@psychmedia) was able to end my frustration with this tweet. This meant I was able to use the following query VQL friendly:

SELECT C, Count(B) WHERE B = ‘questionID’ GROUP BY C

The next part of the problem was how to let the user decide which question ID they wanted to graph. Looking at the Simple Visualization example I could see it would be easy to iterate across the returned data and push out some html using JavaScript. What I wanted to do was GROUP the questionID’s and COUNT the number of responses, which is possible using:

SELECT B, Count(C) GROUP BY B

This returns a table of unique question IDs and a separate column with a count of the number of responses. A form list element is populated with the results using:

for (i=0; i<data.getNumberOfRows(); i++){
  var ansText = data.getValue(i, 0)+' (No of votes '+data.getValue(i, 1)+')';
  var valText = data.getValue(i, 0);
  ansSet.options[ansSet.options.length]=new Option(ansText,valText);
}

And that’s it. If you want to play around with this the code is here. Enjoy and if you find this idea useful or if you spot any issues as always I value your comments.

Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]

Previously in  Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities] I show how you can use Google Spreadsheets to populate a Twitter list. A comment on this post by Colin Gray got me thinking, instead of having to drop a list of usernames from another source, what if the spreadsheet could use the Twitter Search to pull usernames and populate a list. As I mentioned in my response to Colin I have already documented pulling Twitter searches in Using Google Spreadsheet to automatically monitor Twitter event hashtags and more. So here it is:

*** The Auto Add to Twitter List Google Spreadsheet ***

The first sheet of this Spreadsheet has setup instructions. If you get into any problems leave a comment in the box at the end of this post. One thing I’ll say is be patient some operations can take over a minute if you having lists with lots of users.

Use case

The most obvious use case is to use this spreadsheet to generate a real-time conference hashtag community list. Variations on this might be to generate a list as you speak but this might be more gimmicky than of practical use.

Advance search

It’s worth noting that you are not limited to just searching for hashtags. The search terms used in the Spreadsheet can include most of the Twitter Search Operators (‘since’ is the only one that the Spreadsheet overrides). For example your search term could be ‘to:mhawksey near:Edinburgh within:400mi’ which would collect a list of users who tweet me within 400 miles of Edinburgh. 

The ‘techie bit’

So what’s new? The two main additions are utilising: Time-Driven Triggers which are part of Google Apps Script (these enable you to run Scripts on a Spreadsheet even when it is not open); and Mikael Thuneberg’s getTweets() function which is in the Script gallery.

The getTweets function uses the Apps Script UrlFetchApp.fetch to return results from the Twitter Search page.This is a pretty powerful tool for other mashineering as you can call a webpage using different methods (‘post’, ‘get’, ‘put’, ‘delete’).

The way I’ve modified Mikael’s script is to return JSON rather than XML data from Twitter (BTW Brian Kelly has an interesting post on Moves Away From XML to JSON?, my response is it is application specific, in this particular case it was easier to get JSON). I also added some code which only returned a unique array of twitter usernames from the search.

That’s about it really. If you have any questions about this script just drop them in the comments.

PS A big thank you to Lilian Soon (@xlearn) and Shri Footring (@ShriFootring) for help with testing/debugging

PPS I’m giving a face-to-face presentation as part of the JISC Winter Fayre (#jiscwf) looking some of the ways you can use Twitter to support the backend of teaching and learning on the 10th December at 12:20pm GMT. I’ll hopefully be streaming the session live via Livestream so please join me if you like.

Festive Tweets: Exploring the Utility of Twitter to Support Teaching and Learning

From passive to active communication, from one-way broadcast to two-way collaboration, there are a number of ways Twitter can be used to support teaching and learning. In this session we highlight some of Twitter’s hidden treats and explore how Twitter can be used as a free SMS broadcast system; how tweets from mobiles and other devices can be used as an alternative to electronic voting systems; how you can create and control a system to automatically tweet resources and announcements; how tweets can be used for lecture capture enhancement; and how Twitter can be used to leave timeline-based comments on videos.

Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities]

Update: I’ve revised this idea in Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]

Having recently rediscovered the joys of Google Apps Script I was looking for something to do after my Event Manager Spreadsheet. A couple of ideas I had were: something to do with linked data (possibly a email subscription list); or having read the Google Apps Script Twitter Approval Manager Tutorial something around an automated timezone retweet system.

But while wandering through the Virtual coffee shop at Innovating e-Learning 2010 I noticed a post from my colleague at RSC Eastern, Shri Footring asking:

Does anyone know whether there an automated way to create a twitter list of everyone who has used the tag #jiscel10 in a tweet?

I’ve been closely following Tony Hirst’s work on visualising community and user networks using Gephi (partly to play ‘Where’s Wally’ as I vainly try and spot myself in the various network visualisation). I also new Tony had released his Twitter Community Grabbing Code – newt.py, which if I had ever coded in Python would let me solve Shri’s problem in a blink of an eye.

Instead I thought it would be completely insane interesting to see if I could replicate some of Tony’s newt.py functionality in Google Spreadsheets. And guess what you can! So here’s how. [You can shortcut some of the steps in 1, 3–5 by copying this Spreadsheet]

How-to create a Twitter list from Google Spreadsheet

  1. Follow the steps in the Google Apps Script Twitter Approval Manager Tutorial up to and including authorising your spreadsheet with Twitter (if you start Tweeting you’ve gone too far)
  2. Generate a list of twitter usernames you would like to add to a list. I did this by exporting the jiscel10 hashtag archive from the Twitter archiving service Twapper Keeper (I used the export and download button. If you use Excel Permalink (beta) option increase the View Limit)
  3. Import this data to a sheet of the Twitter Approval Manager Spreadsheet. You can edit this data the important thing is that there is a column with the heading from_user and preferably it’s in column C
  4. Insert a new sheet called ‘To add to Twitter List’ and in the first cell enter ‘=UNIQUE(Sheet1!C:C)’ (this strips out an duplicate twitter usernames)
  5. Open the Spreadsheets script editor by clickingTools > Scripts > Script Editor and copy the code at the end of this post at the end of the spreadsheet script. Click on Run > onOpen (this should save changes to the script), then close the Script Editor.

In the Twitter drop down menu on the main spreadsheet  you should have 2 options Add to a List and Purge a List.

Add to a List
When you select add to a list you’ll be prompted for a list name. This list must already existing on your Twitter account even if it has no follower. Once you enter the list name the spreadsheet should start adding usernames. This process can take some time depending on the size of your list (the maximum is 500) and you will be prompted when it is done. You can always open a separate browser window with the twitter list to check if names are being added.

Purge a List
I’ve included a purge option to allow you to rebuild the list without deleting it entirely. This means followers of a list won’t be lost. Again this process can take some time so be patient.

The code

Note: For this code to work you also need the code from the Twitter Approval Manager

// Add list of twitter usernames to a Twitter list
// coded by @mhawksey
// Available under Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License
var SCREEN_NAME = "";
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Add to a List", functionName: "addToList"},
                      {name: "Purge a List", functionName: "purgeList"},
                      {name: "Tweet", functionName: "tweet"},
                      {name: "Configure", functionName: "configure"} ];
  ss.addMenu("Twitter", menuEntries);
}
function getScreenName() {
  var oauthConfig = UrlFetchApp.addOAuthService("twitter");
  oauthConfig.setAccessTokenUrl(
      "https://api.twitter.com/oauth/access_token");
  oauthConfig.setRequestTokenUrl(
      "https://api.twitter.com/oauth/request_token");
  oauthConfig.setAuthorizationUrl(
      "https://api.twitter.com/oauth/authorize");
  oauthConfig.setConsumerKey(getConsumerKey());
  oauthConfig.setConsumerSecret(getConsumerSecret());
  var requestData = {
    "method": "GET",
    "oAuthServiceName": "twitter",
    "oAuthUseToken": "always"
  };
  var result = UrlFetchApp.fetch(
      "http://api.twitter.com/1/account/verify_credentials.json",
      requestData);
  var o  = Utilities.jsonParse(result.getContentText());
  SCREEN_NAME = o.screen_name;
}
function twitterList(method, list, query){
  if (SCREEN_NAME==""){
    getScreenName();
  }
  var requestData = {
        "method": method,
        "oAuthServiceName": "twitter",
        "oAuthUseToken": "always"
      };
   try {
      var result = UrlFetchApp.fetch(
          "http://api.twitter.com/1/"+SCREEN_NAME+"/"+list+"/members.json"+query,
          requestData);
      var o  = Utilities.jsonParse(result.getContentText());
    } catch (e) {
      Logger.log(e);
    }
   return o;
}
function purgeList(){
  var list_id=Browser.inputBox("Enter list name to purge:");
  while(twitterList("GET", list_id,"").users.length>0){
    var object = twitterList("GET", list_id,"");
    for (var i = 0; i < object.users.length; ++i) {
      twitterList("POST", list_id, "?_method=DELETE&id="+object.users[i].screen_name);
      Logger.log(object.users[i].screen_name);
    }
  }
  Browser.msgBox("List purged");
}
function addToList(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("To add to Twitter List");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), 1);
  //get list name to add users to (must already exist)
  var list_id=Browser.inputBox("Enter list name to add users to:");
  objects = getRowsData(dataSheet,dataRange);
  for (var i = 0; i < objects.length; ++i) {
    var rowData = objects[i];
    if (rowData.fromuser!="" ){
      try {
        var o = twitterList("POST", list_id, "?id="+rowData.fromuser);
        dataSheet.getRange(i+2,2).setValue("Added");
      } catch (e) {
        Logger.log(e);
      }
    }
  }
  Browser.msgBox("The list now has :"+o.member_count+" members");
}
//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
//////////////////////////////////////////////////////////////////////////////////////////
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}
​

The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever

Back in March 2010 I looked into using Google Apps (Spreadsheet, Calendar and Sites) as an event management system utilising the power of Google App Scripts. The solution I came up with was okay, but wasn’t very user friendly and bits of the code were, well, a bit messy. Shortly after posting the final version I also came across Romain Vialard’s - Manage your events: Calendar Importer and Registration Form.

Romain had some really nice features including streamlining the setup and passing information to a booking form and for a long time I’ve vowed to return to my original solution and incorporate some of Romain’s ideas. So here it is:

The best Google Spreadsheet EventManager (Make a copy when opened)

The main features of the new Spreadsheet are:

  • Entering events in a Google Spreadsheet which are then pushed to Google Calendar and optionally Sites.
  • Improved registration with an autofill for an event ID populated using the the booking form link
  • Automatic email notification to admin for new bookings
  • Delegate email addresses automatically added to guest list in Google Calendar and added to Google Contacts
  • Option for the delegate to receive personalised travel directions in joining instructions email
  • General tidy up of the code

The video below describes these features and how to setup the EventManager Spreadsheet (I’ve I’m sounding a little lacklustre it was really late when I recorded this and I just want to get this homework out):

Gotchas (Google Apps Script Map Service)

There were a couple of gotchas along the way, mostly around the maps/direction service. I’m still not convinced that this feature is entirely worthwhile, but I decided to preserver with it just as an opportunity to familiarise myself with this service. I really shouldn’t gripe as most of the hard work was done in the ‘Send customized driving directions in a mail merge’ template. My main problem was setting the mode of transport which isn’t covered in the template. I wasn’t helped by the lack of documentation on the Google App Script site, but a friendly Google Employee was able to answer a question I posed on the forum.

If anyone else is looking at using the Map service is Google App Script I would recommend looking at the documentation for the main Google Maps API to let you take a punt at what some of the methods are (as highlighted in this post I made to someone else’s problem with getting a route duration and distance).

Another reason I might give up on the Google Apps Script Map service is you probably can pull most of this functionality straight from the Google Maps API as JSON, allowing you to use something like:

var url = "http://maps.googleapis.com/maps/api/directions/json?origin=Chicago,IL&destination=Los+Angeles,CA&sensor=false";
var response = UrlFetchApp.fetch(url);

PS I was also left scratching my head when Session.getUser().getUserLoginId() suddenly stopped working but I’m guessing this is Google patching a possible security problem.

Here are some links to an example event so you can see what the results look like:

Below is a copy of the code (mainly for SEO purposes):

// EventManagerV3 glued together by mhawksey http://www.google.com/profiles/m.hawksey
// Related blog post http://www.rsc-ne-scotland.org.uk/mashe/eventmanagerv3/
// With some code (settings, importIntoCalendar, sendEmails) from
// Romain Vialard's http://www.google.com/profiles/romain.vialard
// Manage your events: Calendar Importer and Registration Form
// https://spreadsheets.google.com/ccc?key=tCHuQkkKh_r69bQGt4yJmNQ
var ss = SpreadsheetApp.getActiveSpreadsheet();
var BOOKING_ACTION_COL = 10;
function onOpen() {
  var conf = ss.getSheetByName("Templates").getRange("B3").getValue();
  if (conf == "") {
    ss.toast("Click on setup to start", "Welcome", 10);
  }
  var menuEntries = [ {name: "Process Events", functionName: "importIntoCalendar"}, {name: "Process Bookings", functionName: "sendBookingConf"}, {name: "Email joining instructions", functionName: "sendJoinEmails"} ];
  ss.addMenu("Event Manager", menuEntries);
}
function settings(){
  var calendarName = Browser.inputBox("First create a calendar in Google Calendar and enter its name here:");
  if (calendarName != "cancel" && calendarName != ""){
    var templateSheet = ss.getSheetByName("Templates");
    templateSheet.getRange("E1").setValue(calendarName);
    var formURL = ss.getFormUrl();
    templateSheet.getRange("B3").setValue(formURL);
    var calTimeZone =  CalendarApp.openByName(calendarName).getTimeZone();
    ss.setSpreadsheetTimeZone(calTimeZone);
    var timeZone = ss.getSpreadsheetTimeZone();
    var siteUrl = Browser.inputBox("If you would like to update events to a Sites page enter your announcement page url");
    if (siteUrl != "cancel" && siteUrl != ""){
      templateSheet.getRange("E2").setValue(siteUrl);
    }
    var adminEmail = Browser.inputBox("Please enter your administrator email address");
    if (adminEmail != "cancel" && adminEmail != ""){
      templateSheet.getRange("B4").setValue(adminEmail);
    }
    ss.toast("You can now import events in your calendar. Time Zone is currently set to: "+timeZone+".", "Set up completed!", -1);
    SpreadsheetApp.flush();
  }
}
function importIntoCalendar(){
  var dataSheet = ss.getSheetByName("Put your events here");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
  var templateSheet = ss.getSheetByName("Templates");
  var calendarName = templateSheet.getRange("E1").getValue();
  var siteUrl = templateSheet.getRange("E2").getValue();
  if (calendarName !=""){
    var cal =  CalendarApp.getCalendarsByName(calendarName)[0];
    var eventTitleTemplate = templateSheet.getRange("E3").getValue();
    var descriptionTemplate = templateSheet.getRange("E4").getValue();
    // Create one JavaScript object per row of data.
    objects = getRowsData(dataSheet, dataRange);
    // For every row object, create a personalized email from a template and send
    // it to the appropriate person.
    for (var i = 0; i < objects.length; ++i) {
      // Get a row object
      var rowData = objects[i];
      if (rowData.eventId && rowData.eventTitle && rowData.action == "Y" ){
        var eventTitle = fillInTemplateFromObject(eventTitleTemplate, rowData);
        var description = fillInTemplateFromObject(descriptionTemplate, rowData);
        // add to calendar bit
        if(rowData.endDate == "All-day"){
          cal.createAllDayEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).addEmailReminder(15).setTag("Event ID", rowData.eventId);
        }
        else{
          cal.createEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).addEmailReminder(15).setTag("Event ID", rowData.eventId);
        }
        // add to site bit
        if (siteUrl != ""){
          var page = SitesApp.getPageByUrl(siteUrl);
          var announcement = page.createAnnouncement(rowData.eventTitle, description);
        }
        // create event sheet
        var temp = ss.getSheetByName("EventTMP");
        var eventSheet = ss.insertSheet("Event "+rowData.eventId, {template:temp});
        eventSheet.getRange(1, 2, 1, 1).setValue(rowData.numberOfPlaces);
        eventSheet.getRange(1, 3, 1, 1).setValue(rowData.eventTitle);
        eventSheet.getRange(2, 3, 1, 1).setValue(rowData.location);
        eventSheet.getRange(3, 6, 1, 1).setValue(rowData.startDate);
        eventSheet.getRange(3, 8, 1, 1).setValue(rowData.endDate);
        dataSheet.getRange(i+2, 1, 1, 1).setValue("");
        dataSheet.getRange(i+2, 2, 1, 1).setValue("Added "+new Date()).setBackgroundRGB(221, 221, 221);
        dataSheet.getRange(i+2,1,1,dataSheet.getMaxColumns()).setBackgroundRGB(221, 221, 221);
        // Make sure the cell is updated right away in case  the script is interrupted
        SpreadsheetApp.flush();
      }
    }
    ss.toast("People can now register to those events", "Events imported");
  }
}
function onFormSubmit() {
  var dataSheet = ss.getSheetByName("Bookings");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
  var templateSheet = ss.getSheetByName("Templates");
  var emailTemplate = templateSheet.getRange("E6").getValue();
  var adminEmail = templateSheet.getRange("B4").getValue()
  // Create one JavaScript object per row of data.
  data = getRowsData(dataSheet, dataRange);
  for (var i = 0; i < data.length; ++i) {
    // Get a row object
    var row = data[i];
    row.rowNumber = i + 2;
    if (!row.action) { // if no state notify admin of booking
      var emailText = fillInTemplateFromObject(emailTemplate, row);
      var emailSubject = "Booking Approval Request ID: "+ row.rowNumber;
      MailApp.sendEmail(adminEmail, emailSubject, emailText);
      dataSheet.getRange(row.rowNumber,BOOKING_ACTION_COL).setValue("TBC"); //9 is the column number for 'Action'
    }
  }
}
function sendBookingConf(){
  var dataSheet = ss.getSheetByName("Bookings");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
  var templateSheet = ss.getSheetByName("Templates");
  var emailSubjectTemplate = templateSheet.getRange("B1").getValue();
  var emailTemplate = templateSheet.getRange("B2").getValue();
  var emailSentColumn = BOOKING_ACTION_COL;
  // To add guests into Calendar
  var calendarDataSheet = ss.getSheetByName("Put your events here");
  var calendarDataRange = calendarDataSheet.getRange(2, 1, calendarDataSheet.getMaxRows(), calendarDataSheet.getMaxColumns());
  var calendarName = templateSheet.getRange("E1").getValue();
  // Create one JavaScript object per row of data.
  calendarObjects = getRowsData(calendarDataSheet, calendarDataRange);
  // Create one JavaScript object per row of data.
  objects = getRowsData(dataSheet, dataRange);
  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];
    if (rowData.action == "Y") {  // Prevents  sending duplicates
       // add guest in calendar
      for (var j = 0; j < calendarObjects.length; ++j) {
        // Get a row object
        var calendarRowData = calendarObjects[j];
        if (calendarRowData.eventId == rowData.eventId){
          var cal =  CalendarApp.openByName(calendarName);
          if(calendarRowData.endDate == "All-day"){
            var getDate = new Date(calendarRowData.startDate).getTime();
            var endDate = new Date().setTime(getDate + 86400000);
            var events = cal.getEvents(new Date(calendarRowData.startDate), new Date(endDate));
          }
          else{
            var events = cal.getEvents(new Date(calendarRowData.startDate), new Date(calendarRowData.endDate));
          }
          for (var k in events){
            if (events[k].getTag("Event ID") == rowData.eventId){
                events[k].addGuest(rowData.email);
              j = calendarObjects.length;
            }
          }
        }
      }
      // Generate a personalized email.
      // Given a template string, replace markers (for instance ${"First Name"}) with
      // the corresponding value in a row object (for instance rowData.firstName).
      calendarRowData.bookingId = rowData.eventId+"-B"+(i + 2);
      calendarRowData.firstName = rowData.firstName;
      var emailSubject = fillInTemplateFromObject(emailSubjectTemplate, calendarRowData);
      var emailText = fillInTemplateFromObject(emailTemplate, calendarRowData);
      var emailAddress = rowData.email;
      MailApp.sendEmail(emailAddress, emailSubject, emailText,{htmlBody: emailText});
      // add booking to right event sheet
      dataSheet.getRange(i + 2,emailSentColumn).setValue(calendarRowData.bookingId).setBackgroundRGB(221, 221, 221);
      var eventSheet = ss.getSheetByName("Event " + rowData.eventId);
      var rowNum = eventSheet.getLastRow()+1;
      eventSheet.getRange(rowNum, 3, 1, 1).setValue(calendarRowData.bookingId);
      eventSheet.getRange(rowNum, 4, 1, 1).setValue(rowData.timestamp);
      eventSheet.getRange(rowNum, 5, 1, 1).setValue(rowData.firstName);
      eventSheet.getRange(rowNum, 6, 1, 1).setValue(rowData.lastName);
      eventSheet.getRange(rowNum, 7, 1, 1).setValue(rowData.email);
      eventSheet.getRange(rowNum, 8, 1, 1).setValue(rowData.organisationName);
      eventSheet.getRange(rowNum, 9, 1, 1).setValue(rowData.startPostcode);
      eventSheet.getRange(rowNum, 10, 1, 1).setValue(rowData.preferredMode);
      eventSheet.getRange(rowNum, 11, 1, 1).setValue(rowData.otherInfo);
      eventSheet.getRange(rowNum, 12, 1, 1).setValue(rowData.comments);
      // Make sure the cell is updated right away in case  the script is interrupted
      // Add delegate to Contacts
      var curDate = Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm");
      var c = ContactsApp.findByEmailAddress(rowData.email);
      if (!c){
        var c = ContactsApp.createContact(rowData.firstName, rowData.lastName, rowData.email);
        var prop = {};
        prop.Organisation = rowData.organisationName;
        prop.Added = curDate;
        c.setUserDefinedFields(prop);
        var group = ContactsApp.findContactGroup(rowData.organisationName);
        if (!group){
          var group = ContactsApp.createContactGroup(rowData.organisationName);
        }
        c.addToGroup(group);
      } else {
        c.setUserDefinedField("Last activity", curDate);
      }
      SpreadsheetApp.flush();
    }
  }
  ss.toast("", "Emails sent", -1);
}
// Code to send joining instructions - based on simple mail merge code from
// Tutorial: Simple Mail Merge
// Hugo Fierro, Google Spreadsheet Scripts Team
// March 2009
function sendJoinEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getActiveSheet();
  var eventName = ss.getRange("C1").getValue();// pull event name from sheet
  var location = ss.getRange("C2").getValue();// pull event location
  var emailCount = 0;
  var dataRange = dataSheet.getRange(5, 3, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
  var templateSheet = ss.getSheetByName("Templates");
  var emailTemplate = templateSheet.getRange("B6").getValue();
  var emailSubject = templateSheet.getRange("B5").getValue();
  emailSubject = emailSubject.replace('${"Event Name"}', eventName);
  // Create one JavaScript object per row of data.
  objects = getRowsData(dataSheet,dataRange,4);
  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];
    rowData.eventName = eventName;
    rowData.rowNumber = i + 5;
    // Generate a personalized email.
    if (!rowData.emailed) {
      if (rowData.startPostcode && (location != "Online" || location)){
        rowData.directions = getMapDirections_(rowData.startPostcode, location, rowData.mode);
      }
      var emailText = fillInTemplateFromObject(emailTemplate, rowData);
      try {
        MailApp.sendEmail(rowData.emailAddress, emailSubject, 'Please view in HTML capable email client.', {htmlBody: emailText});
        emailCount++;
        dataSheet.getRange(rowData.rowNumber, 2).setValue(Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm"));
      } catch(e) {
        Browser.msgBox("There was a problem sending to "+rowData.emailAddress);
      }
    }
  }
  ss.toast("Joining instructions have been sent to "+emailCount+" delegates", "Joining instructions sent", 5);
}
// Modified from Send customized driving directions in a mail merge template
// http://code.google.com/googleapps/appsscript/templates.html
function getMapDirections_(start, end, mode) {
  // Generate personalized static map with directions.
   switch(mode)
  {
    case "Cycling":
       var directions = Maps.newDirectionFinder()
           .setOrigin(start)
           .setDestination(end)
           .setMode(Maps.DirectionFinder.Mode.BICYCLING)
           .getDirections();
      break;
    case "Walking":
        var directions = Maps.newDirectionFinder()
           .setOrigin(start)
           .setDestination(end)
           .setMode(Maps.DirectionFinder.Mode.WALKING)
           .getDirections();
      break;
    default:
        var directions = Maps.newDirectionFinder()
           .setOrigin(start)
           .setDestination(end)
           .setMode(Maps.DirectionFinder.Mode.DRIVING)
           .getDirections();
  }
  var currentLabel = 0;
  var directionsHtml = "";
  var map = Maps.newStaticMap().setSize(500, 350);
  map.setMarkerStyle(Maps.StaticMap.MarkerSize.SMALL, "red", null);
  map.addMarker(start);
  map.addMarker(end);
  var r1 = new RegExp('
', 'g'); var r2 = new RegExp('
', 'g'); var points = []; var distance = 0; var time = 0; for (var i in directions.routes) { for (var j in directions.routes[i].legs) { for (var k in directions.routes[i].legs[j].steps) { var step = directions.routes[i].legs[j].steps[k]; distance += directions.routes[i].legs[j].steps[k].distance.value; time += directions.routes[i].legs[j].steps[k].duration.value; var path = Maps.decodePolyline(step.polyline.points); points = points.concat(path); var text = step.html_instructions; text = text.replace(r1, ' '); text = text.replace(r2, ' '); directionsHtml += " " + (++currentLabel) + " - " + text; } } } // be conservative, and only sample 100 times... var lpoints=[]; if (points.length < 200) lpoints = points; else { var pCount = (points.length/2); var step = parseInt(pCount/100); for (var i=0; i<100; ++i) { lpoints.push(points[i*step*2]); lpoints.push(points[(i*step*2)+1]); } } // make the polyline if (lpoints.length>0) { var pline = Maps.encodePolyline(lpoints); map.addPath(pline); } var mapLink = "Click here for complete map and directions"; var dir = " Travel Directions "+mapLink+" Summary of Route"+ " Distance: " +Math.round(0.00621371192*distance)/10+" miles"+ " Time: "+Math.floor(time/60)+" minutes " + directionsHtml; return dir; }

Using Google Spreadsheet to automatically monitor Twitter event hashtags and more

I recently read Tony Hirst’s onFormSubmit – Raising Web Scale Events in Google Spreadsheets post which highlights opportunities with the new event triggers (the ability to trigger scripts on events like form submit or time-driven like every x minutes) and was blown away by the possibilities. My first thought was to re-examine my event booking spreadsheet but when I saw the twitter conversation below between Brian Kelly and Tony (psychemedia) I saw something better to do - a Google Spreadsheet which could capture and report the daily/weekly twitter activity from an event hashtag .

Brian Kelly (briankelly) @psychemedia @mweller Ive just captured stats & image of word cloud if#iwmw10 tweets to observe changes over time.
7:34 PM Jun 14th via Seesmic


Tony Hirst (psychemedia) @briankelly have you scheduled that capture using a service, or is it a manual chore?
7:43 PM Jun 14th via TweetDeck in reply to briankelly


Brian Kelly (briankelly)@psychemedia A manual capture now, week b4, during & after. Will help understand if worth development effort.
7:48 PM Jun 14th via Seesmic in reply to psychemedia


So here it is my Twitteralytics Google Spreadsheet. The video below gives a quick overview of how to set it up and what it does:

In summary the script is designed as a way to automatically pull daily or weekly results from twitter, summarise them and push an update via email. The spreadsheet also keeps a copy of the sampled tweets which could be used for further analysis down the line but I would recommend you only use this as a backup for a separate twitter archive on Twapper Keeper. There are also a number of other services which are probably better for more in-depth post analysis of event hashtag communities like Andy Powell Summarizr or Tony Hirst’s Yahoo Pipes for Personal Twitter Networks in Hashtag Communities.

While the spreadsheet was created to monitor event hashtags it could easily be modified for other applications. For example, I’m sure it could be easily modified to collect continuous student with twitter (as highlighted by AJCann). Also, it doesn’t have to be limited to Twitter results, the same framework could be used for other XML and HTML data.

How it was done

As always I like to document how it was done. The rough process of the script is:

  1. Open spreadsheet by ID (required when using event triggers)
  2. Copy the TMP sheet giving it the script execution date/time as a name
  3. getTweets from Twitter – more about this later
  4. Write results to new sheet
  5. Copy total and top tweeters to ‘Summary’ page
  6. Prepare and send email of summary results

The getTweets script I picked up from Mikael Thuneberg from AutomateAnalytics.com, making a small modification to include date limits. The script uses built-in functions for fetching external data and parsing the results.

The TMP sheet also has some useful formula’s worth highlighting.

  • H2 - ‘=UNIQUE(E2:E)’ creates a list of the authors removing duplicate entries.
  • I2 - ‘=EXPAND(ArrayFormula(IF(ISBLANK(H2:H), "", COUNTIF(E2:E,H2:H))))’  for every unique author in column H the formula count the number of times there name appears in column E
  • A2 - ‘=SORT(H2:I, I2:I, FALSE)’ sorts the values in column H (author names) by column I (the number of occurrences) 

All of these formula automatically fill the column with results. More information on Google Spreadsheet formulas is here.

Convert time stamped data to timed-text (XML) subtitle format using Google Spreadsheet Script

Wage dislikes spreadsheets
Wage dislikes spreadsheets
Originally uploaded by Dyanna

My post titles just get better and better. As part of my research into twitter subtitling I’ve focused on integrating with the twitter search and Twapper Keeper archive into the twitter subtitle generator tool, but I’m aware there is a wider world of timed data for subtitlizing. When Tony contacted me on Friday with some timed data he had as part of his F1 data junkie series it seemed like the ideal opportunity to see what I could do.

The data provided by Tony was in a *.csv spreadsheet format the first couple of lines included below:

timestamp,name,text,initials
2010-04-18 08:01:54,PIT,Lewis last car's coming into position now.,PW
2010-04-18 08:02:05,PIT,All cars in position.,PW
2010-04-18 08:02:59,COM,0802: The race has started,CM

My first thought was to just format it in Excel but quickly got frustrated with the way it handles dates/time, so instead uploaded it to Google Spreadsheet. Shown below is how the same data appears:

Google Spreadsheet of csv

Having played around with the timed-text XML format I knew the goal was to convert each row into something like (of course wrapping with the obligatory XML header and footer):

<p style="s1" begin="00:00:00" id="p1" end="00:00:11">PIT: Lewis last car's coming into position now.</p>

Previously I’ve played with Google Apps Script to produce an events booking systems, which uses various components of Google Apps (spreadsheet, calendar, contacts and site), so it made sense to use the power of Scripts for timed text. A couple of hours later I came up with this spreadsheet (once you open it click File –> Make a copy to allow you to edit).

On the first sheet you can import your timed data (it doesn’t have to be *.csv, it only has to be readable by Google Spreadsheet), and then clicking ‘Subtitle Gen –> Timed Data to XML’ on the XMLOut sheet it generates and timed text XML.

Below is the main function which is doing most of the work, the comments indicating what’s going on:

function writeTTXML() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheets()[0];
var data = getRowsData(dataSheet); // read data from first sheet into javascript object
var sheet = ss.getSheetByName("XMLOut") || ss.insertSheet("XMLOut"); // if there isn't a XMLOut sheet create one
sheet.clear(); // make sure it is blank
// Start the XMLOut sheet with tt-XML doc header
sheet.getRange(1, 1).setValue("<?xml version=\"1.0\" encoding=\"utf-8\"?><tt xmlns=\"http://www.w3.org/2006/10/ttaf1\" xmlns:ttp=\"http://www.w3.org/2006/10/ttaf1#parameter\" ttp:timeBase=\"media\" xmlns:tts=\"http://www.w3.org/2006/10/ttaf1#style\" xml:lang=\"en\" xmlns:ttm=\"http://www.w3.org/2006/10/ttaf1#metadata\"><head><metadata><ttm:title>Twitter Subtitles</ttm:title></metadata><styling><style id=\"s0\" tts:backgroundColor=\"black\" tts:fontStyle=\"normal\" tts:fontSize=\"16\" tts:fontFamily=\"sansSerif\" tts:color=\"white\" /></styling></head><body tts:textAlign=\"center\" style=\"s0\"><div>");
var startTime = data[0].timestamp; // collect start time from first data row, all subsequent relative to this
for (var i = 0; i < (data.length-1); ++i) { // looping through all the data one row at a time except last line (excluded because have no end date/time
var row = data[i];
var nextRow = data[i+1];
row.rowNumber = i + 1;
//calc begin and end for an entry converting to HH:mm:ss format.
var begin = Utilities.formatDate(new Date(row.timestamp-startTime), "GMT", "HH:mm:ss");
var end = Utilities.formatDate(new Date(nextRow.timestamp-startTime), "GMT", "HH:mm:ss");
// prepare string in tt-XML format. Conent is pulled by ref the column header in normalised format (e.g. if col headed 'Twitter status' normalsed = 'twitterStatus'
var str = "<p style=\"s1\" begin=\""+begin+"\" id=\"p"+row.rowNumber+"\" end=\""+end+"\">"+row.name+": "+row.text+"</p>";;
// add line to XMLOut sheet
var out = sheet.getRange(row.rowNumber+1, 1).setValue(str);
}
var lastRow = sheet.getLastRow()+1;
//write tt-XML doc footer
var out = sheet.getRange(lastRow, 1).setValue("</div></body></tt>");
}

If your timed data has different headers you can tweak this by clicking ‘Tools –> Script –> Script editor …’ and changing how the str on line 18 is constructed.

I’m the first one to admit that this spreadsheet isn’t the most user friendly and it only includes the tt-XML format, but hopefully there is enough structure for you to go, play and expand (if you do please use the post comments to share your findings)

Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts)

Update: New version is posted here The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever

A while ago I was looking at Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an event booking system). This idea was inspired by Tony Hirst’s work on updating a google calendar from a spreadsheet the endpoint being a way to manage a simple event booking system using Google Apps. This all started to unravel as I couldn’t find a way to create a custom booking form for each event. Tony suggested that I should look at just using a generic form which was manually updated with new events, the system handling the rest of the booking process. So with that little hurdle out of the way I revisted my script and as well as rewritting most of it I took the concecpt a little further.

So what do we have now? The video below walks through the workflow:


[You might want to enable full screen view to see what is happening]

The core code is at the end of this post and you can access the full spreadsheet and script here (if you want to copy this spreadsheet to your Google Apps domain account you’ll need to edit the link to http://spreadsheets.google.com/a/yourdomainname.com/ccc?key=0AtqJUiWU5zvNdGZCV3pmTDFWVlVXVDBodFZKR2czSXc <- thanks @Eion). Once you open it click File –> Make a copy to allow you to edit. The bulk of this code is actually a reworking of some existing Google Apps Script tutorials:

To use this code yourself some variables need defining (I could have just called these from a sheet but ran out of time). To do this click Tools –>  Scripts –> Script editor… and you’ll see the variables at the top. The first time you run the script a security dialog will popup. You will need to ‘grant access’ for it to work.

Instructions

Once you do this here are some instructions for use (in general yellow fields are for user input):

  1. In the ‘Events’ sheet enter title, description, dates etc. You can enter as many events as you like.
  2. To make an event public enter the text ‘Add’ in the action column, then click Booking System –> Process events. This will push it to calendar and site and create a unique sheet for the event.
  3. To allow bookings click on Form –> Edit form and add the event to the drop down using the format ‘ID#{theEventNumberUsedOnTheSheet} - {theNameOfYourEvent}’ – !this format including whitespaces is really important
  4. A limitation of the Google Apps Script is it doesn’t yet handle onFormSubmit actions (Google are looking to add this), so for now to process bookings you need to click Booking System –> Process Bookings. This sends an email to your admin to notify them that there is a booking
  5. To approve a booking enter ‘Y’ in the Action column and again click Booking System –> Process Bookings. (You can approve as many booking as you like in one go). The script will then send a confirmation to the delegate and copy their details to the appropriate event sheet.
  6. When you are ready to send joining instructions you can edit the message in the EmailTemplates sheet (you can also edit the format of the other emails used). When you are ready to send go to the correct Event sheet then click Booking System –> Email joining instructions

Limitations/waiting for Google to fix

Deleting events from calendar and sites is still a manual process (I don’t recall anywhere in the API which allows you to do this). You will also see in the code I’ve commented out a section which would add all delegates to Google Contacts.

The code

// AppEventManger Script
// by mhawksey at http://bit.ly/mashe
// User defined variables
var BOOKING_FORM_URL = "http://bit.ly/bookingurl"; //your booking form url (I bit.ly'd mine to shorten
var SITE_DOMAIN = "Your domain"; // your apps domain name
var SITE_NAME = "Name of your site"; // your apps site name
var CALENDAR_EVENTS = "Name of your calendar"; // the name of the calendar to update
var STATE_MANAGER_EMAIL = "[email protected]"; //email address for booking notifications
// some additional variables to change at your peril
var COLUMN_ACTION = 8;
var COLUMN_STATE = 9;
var COLUMN_BOOKING_ID = 10;
var COLUMN_COMMENT = 11;
var COLUMN_EVENT_ID = 2;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Process Events", functionName: "processEvents"}, {name: "Process Bookings", functionName: "onFormSubmit"}, {name: "Email joining instructions", functionName: "sendEmails"} ];
ss.addMenu("Booking System", menuEntries);
}
function processEvents() {
//declare vars
var cal = CalendarApp.openByName(CALENDAR_EVENTS);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Events"); // ref sheet name (thought I might have ended up with multiple sheets)
var data = getRowsData(dataSheet);
var cal = CalendarApp.getDefaultCalendar();
var site = SitesApp.getSite(SITE_DOMAIN, SITE_NAME); // .getSite(domain, sitename)
var annPage = site.getAnnouncementsPages();
// pull data
for (var i = 0; i < data.length; ++i) {
var row = data[i];
row.rowNumber = i + 2;
if (row.action =="Add"){
var descText = row.description + "More info: " + BOOKING_FORM_URL;
cal.createEvent(row.title, row.start, row.stop, {location:row.location, description:descText}); // create calendar event
var message = "<strong>Start:</strong> " + Utilities.formatDate(row.start, "GMT", "dd/MM/yy HH:mm")
+ "<br/><strong>Finish:</strong> " + Utilities.formatDate(row.stop, "GMT", "dd/MM/yy HH:mm")
+ "<br/><strong>Location:</strong> " + row.location
+ "<br/><strong>Description:</strong> " + row.description
+ "<br/><a href='" + BOOKING_FORM_URL + "'>Click here to book a place</a>"; // prepare message
site.createAnnouncement(row.title, message, annPage[0]); // add announcement to site
var annList = site.getAnnouncements();
var eventID = annList.length; // get announcement ID
var eventSheetName = "Event#" + eventID;
ContactsApp.createContactGroup(eventSheetName); //create a contact group for the event
dataSheet.getRange(row.rowNumber, 2, 1, 1).setValue(new Date()); // add today's date/time to 'Added Date' column
// create a new event booking sheet from template
ss.setActiveSheet(ss.getSheetByName("EventTMP"));
var nuSheet = ss.duplicateActiveSheet();
ss.setActiveSheet(nuSheet);
ss.renameActiveSheet(eventSheetName); // rename using event Id
// insert data into sheet
var eventSheet = ss.getSheetByName(eventSheetName);
eventSheet.getRange(1, 2, 1, 1).setValue(row.numberOfPlaces);
eventSheet.getRange(1, 3, 1, 1).setValue(row.title);
eventSheet.getRange(2, 3, 1, 1).setValue(row.location);
eventSheet.getRange(3, 6, 1, 1).setValue(row.start);
eventSheet.getRange(3, 8, 1, 1).setValue(row.stop);
ss.setActiveSheet(ss.getSheetByName("Events")); // switch back to events sheet
dataSheet.getRange(row.rowNumber, 3, 1, 1).setValue(eventSheetName);
dataSheet.getRange(row.rowNumber, 1, 1, 1).setValue("Added by "+Session.getUser().getUserLoginId()); //set the fact that we have updated the calendars for this event
Browser.msgBox(row.title + " has been published to the calendar and website. IMPORTANT: Add this event to the Form to allow delegates to book in.");
}
}
}
function onFormSubmit() {
// This function has been designed for when App Scripts automatically runs when a Form is submitted.
// For now it has to be manually started. See http://code.google.com/p/google-apps-script-issues/issues/detail?id=4
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Bookings");
var templateSheet = ss.getSheetByName("EmailTemplates");
var emailTemplate = templateSheet.getRange("A7").getValue();
// Create one JavaScript object per row of data.
data = getRowsData(dataSheet);
for (var i = 0; i < data.length; ++i) {
// Get a row object
var row = data[i];
row.rowNumber = i + 2;
if (!row.state) { // if no state notify admin of booking
var emailTemplate = templateSheet.getRange("A7").getValue();
var emailText = fillInTemplateFromObject(emailTemplate, row);
var emailSubject = "Booking Approval Request ID: "+ row.rowNumber;
MailApp.sendEmail(STATE_MANAGER_EMAIL, emailSubject, emailText);
dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue("TBC");
} else if (row.action == "Y") { // if admin have approved send confirmation
var approvedOrRejected = (row.action == "Y") ? "confirmed" : "rejected";
// capture the sheet to copy booking to
var eventID = row.event;
eventID = eventID.substring(eventID.indexOf("#")+1,eventID.indexOf(" -"));
var eventSheet = ss.getSheetByName("Event#" + eventID);
// create a booking ID
var bookingID = "ID#"+eventID+"B"+row.rowNumber;
dataSheet.getRange(row.rowNumber, COLUMN_BOOKING_ID).setValue(bookingID);
// copy booking details to event sheet
var rowNum = eventSheet.getLastRow()+1;
eventSheet.getRange(rowNum, 3, 1, 1).setValue(bookingID);
eventSheet.getRange(rowNum, 4, 1, 1).setValue(row.timestamp);
eventSheet.getRange(rowNum, 5, 1, 1).setValue(row.firstName);
eventSheet.getRange(rowNum, 6, 1, 1).setValue(row.surname);
eventSheet.getRange(rowNum, 7, 1, 1).setValue(row.email);
eventSheet.getRange(rowNum, 8, 1, 1).setValue(row.organisation);
eventSheet.getRange(rowNum, 9, 1, 1).setValue(row.otherInfo);
eventSheet.getRange(rowNum, 10, 1, 1).setValue(row.comments);
//Add/edit details of new/existing delegate to Google Contacts
// This code generates a 'Service error: Contacts : POST method does not support
// concurrency' looks like an API bug.
// http://www.google.com/support/forum/p/apps-script/thread?tid=5f4a4b645490381e&hl=en
/*
var curDate = Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm");
var c = ContactsApp.findByEmailAddress(row.email);
if (!c){
var c = ContactsApp.createContact(row.firstName, row.surname, row.email);
var prop = {};
prop.Organisation = row.organisation;
prop.Added = curDate;
c.setUserDefinedFields(prop);
var group = ContactsApp.findContactGroup(row.organisation);
if (!group){
var group = ContactsApp.createContactGroup(row.organisation);
}
c.addToGroup(group);
} else {
c.setUserDefinedField("Last activity", curDate);
}
//var group = ContactsApp.findContactGroup("EventID#"+eventID);
//c.addToGroup(group); // add contact to event group
*/
//prepare email
var emailTemplate = templateSheet.getRange("A4").getValue();
var emailText = fillInTemplateFromObject(emailTemplate, row);
var emailSubject = "Booking Approved (Booking ID: "+ bookingID + ")";
// fill in the template using stored variables
emailText = emailText.replace("STATE_MANAGER_EMAIL", STATE_MANAGER_EMAIL || "");
emailText = emailText.replace("APPROVED_OR_REJECTED", approvedOrRejected || "");
emailText = emailText.replace("BOOKING_ID", bookingID || "");
MailApp.sendEmail(row.email, emailSubject, emailText);
// Update the state of bookings
dataSheet.getRange(row.rowNumber, COLUMN_STATE).setValue(STATE_APPROVED+" by "+Session.getUser().getUserLoginId());
dataSheet.getRange(row.rowNumber, COLUMN_ACTION).setValue("");
}
}
}
// Code to send joining instructions - based on simple mail merge code from
// Tutorial: Simple Mail Merge
// Hugo Fierro, Google Spreadsheet Scripts Team
// March 2009
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getActiveSheet();
var eventName = ss.getRange("C1").getValue();// pull event name from sheet
var dataRange = dataSheet.getRange(5, 3, dataSheet.getLastRow() - 3, 8);
var templateSheet = ss.getSheetByName("EmailTemplates");
var emailTemplate = templateSheet.getRange("A10").getValue();
// Create one JavaScript object per row of data.
objects = getRowsData(dataSheet,dataRange,4);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
rowData.rowNumber = i + 5;
// Generate a personalized email.
// Given a template string, replace markers (for instance ${"First Name"}) with
// the corresponding value in a row object (for instance rowData.firstName).
if (!rowData.emailed) {
var emailText = fillInTemplateFromObject(emailTemplate, rowData);
emailText = emailText.replace("EVENT_NAME", eventName);
var emailSubject = "Joining Instrucations for " + eventName;
MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
dataSheet.getRange(rowData.rowNumber, 2).setValue(Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm"));
}
}
}
// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
//   - template: string containing markers, for instance ${"Column name"}
//   - data: JavaScript object with values to that will replace markers. For instance
//           data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
var email = template;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = data[normalizeHeader(templateVars[i])];
email = email.replace(templateVars[i], variableData || "");
}
return email;
}
// there are also some Google example functions used getRowsData, getObjects, normalizeHeaders, normalizeHeader, isCellEmpty, isAlnum and isDigit

Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an event booking system)

Update: New version is posted here The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever

I’m going to start this most with a small homage to Tony Hirst. Tony has the honour of posting the first ever comment on this blog and ever since I’ve been an avid follower of his work on OUseful.info. When I saw Tony had started playing around experimenting with using Google Apps Scripts to post data from Spreadsheet to Calendar and vice versa it looked like he was having way to much fun, so like with so many of Tony’s other ideas I thought I would have a go too.

The challenge I set myself was to take Tony’s work and see if I could use it as the basis on an online events booking system. The idea was to enter event details into a spreadsheet which would then be used to automatically populate a site and spreadsheet, delegates being able to book in via a form. Here’s how I got on …

#Issue 1 – This solution requires Google App Scripts which aren’t available to in the basic version of Docs/Site. To get access you need at least a Google Apps Standard, which is free but you need to register with a domain url.

Update: I like to think it was because of my post that Google now makes Apps Script available to everyone using Spreadsheet ;-)

Having signed up to Google Apps I then started following Tony’s posts on Updating Google Calendars from a Google Spreadsheet and Maintaining a Google Calendar from a Google Spreadsheet, Reprise. My spreadsheet layout is almost identical except I added 2 more columns, an ‘Added Date’ and ‘ID’.image

Tony, I was able to set the locale for the date in the spreadsheet via ‘File –> Spreadsheet Settings’ and added some conditional formatting to highlight items which hadn’t been added yet.

Below is the code I dropped into the spreadsheets script editor.

function processEvents() {
 //declare vars
 var cal = CalendarApp.getDefaultCalendar();
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var dataSheet = ss.getSheetByName("Events"); // ref sheet name (thought I might have ended up with multiple sheets)
 var maxcols = dataSheet.getMaxColumns();
 var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, maxcols);
 var data = dataRange.getValues();
 var cal = CalendarApp.getDefaultCalendar();
 var site = SitesApp.getSite("sub-tweeter.net", "mashe-events-from-calendar"); // .getSite(domain, sitename)
 var annPage = site.getAnnouncementsPages();
 var col_added, col_title, col_desc, col_tstart, col_tstop, col_loc, col_num, col_ID;
 // pull column header
 for (var j=1;j<=maxcols;j++){
   var header= dataSheet.getRange(1, j, 1, 1).getValue();
   switch(header){
     case "Status/Action":col_added=j-1;
     case "Title":col_title=j-1; break;
     case "Description":col_desc=j-1; break;
     case "Start": col_tstart=j-1; break;
     case "Stop": col_tstop=j-1; break;
     case "Location": col_loc=j-1; break;
     case "Number of places": col_num=j-1; break;
     case "ID": col_ID=j-1; break;
   default:
   }
 }
 // pull data
 for (i in data) {
   var row = data[i];
   var added = row[col_added];  //Check to see if details for this event have been added to the calendar(s)
   if (added == "Add"){
     // collect event details
     var title = row[col_title];
     var desc = row[col_desc];
     var loc = row[col_loc];
     var tstart = row[col_tstart];
     var tstop = row[col_tstop];
     cal.createEvent(title, tstart, tstop, {location:loc, description:desc}); // create calendar event
     var message = "Start:" + Utilities.formatDate(tstart, "GMT", "dd/MM/yy HH:mm") +"<br/>Finish: "+Utilities.formatDate(tstop, "GMT", "dd/MM/yy HH:mm")+"<br/>"+desc; // prepare message
     site.createAnnouncement(title, message, annPage[0]); // add announcement to site
     var annList = site.getAnnouncements();
     var eventID = annList.length; // get announcement ID
     // set value in spreadsheet
     var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
     dataSheet.getRange(v, 3, 1, 1).setValue(eventID); // add the event ID
     dataSheet.getRange(v, 2, 1, 1).setValue(new Date()); // add today's date/time to 'Added Date' column
     dataSheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
   }
 }

My main tweaks were:

  • dynamically capture the data range using getMaxRows() and getMaxColumns();
  • define a Google Site to push data to (as well as the Calendar);
  • adding the event to a Google Site using createAnnouncement(); and
  • adding the ‘added date’ and ‘Event ID’

Here is the public calendar and Google site populated with data from the spreadsheet (web page view) - I’m currently playing around with the script a bit more so these links are turbulent.

Having gotten this far my next plan was to use the Expense Report Approval Tutorial as a basis for a booking form which would allow a submit/approve workflow. To do this my plan was to have a template spreadsheet for each event which I duplicate using the event ID as an identifier.

# Issue 2 – Google Apps Script doesn’t have a method for duplicating spreadsheets

I think I might have got around this by creating a function which would cycle through an existing spreadsheet, storing the values and then creating a new sheet reversing the process to populate it with data. I don’t think it would however have duplicated form functionality. The concept was beginning to unravel however because …

# Issue 3 – You can only have one form per spreadsheet.

Oh dear!

It was all getting a bit too messy which is a shame because it would have been fun to use Contact Services API to add delegates to Google Contacts either using the setNotes() or setUserDefinedField() to record events the delegate had signed up for, dietary requirements etc. and lots more interesting stuff.

One solution to the who registration issue  might have been to Leveraging Google App Engine services from scripts, but that is going into a whole realm of coding I would prefer to avoid.

So SharePoint anyone? ;-)

About

This blog is authored by Martin Hawksey e-Learning Advisor (Higher Education) at the JISC RSC Scotland N&E.

JISC RSC Scotland North & East logo

If you would like to subscribe to my monthly digest please enter your email address in the box below (other ways are available to subscribe from the button below):

Subscribe to MASHe to monthly email updates

Loading...Loading...


The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by:
Bluga.net Webthumb

The MASHezine (eBook)

MASHe is also available in ebook and can be downloaded in the following formats:

Visit feedbooks.com to manage your subscription

Archives

Opinions expressed in this blog are not necessarily those of the JISC RSC Scotland North & East.

JISC Advance Logo

JISC Advance is a new organisation that brings together the collective expertise of established JISC services:

For further information visit www.jiscadvance.ac.uk

Creative Commons Licence
Unless otherwise stated this work is licensed under a Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License