Monthly Archive for November, 2010

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.

What I’ve starred this month: November 28, 2010

Here’s some posts which have caught my attention this month:

Automatically generated from my Google Reader Shared Items.

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; }

The fall and rise of the webinar/web conference

In the last couple of weeks my aggregation channels have been gently humming to the sound of webinars. It seems a number of institutions are rediscovering the possibilities of these tools to support flexible delivery (and no doubt cut costs allow the reallocation of staff time to enhance the learner experience). This post is designed to highlight some projects and technical developments that I’ve come across.

Projects/Events

Two projects, both funded by the Higher Education Academy, have found their way into my inbox. At Queen Margaret University the Higher Education Academy Subject Centre for Dance, Drama and Music (PALATINE) has funded “An exploration of learner and tutor experience in using online synchronous learning environments across disciplines within the School of Drama and Creative Industries”.

The objectives of this 9 month study are:

    To conduct an in-depth, comparative study of tutor and student experiences of using an online synchronous learning environment (OSLE) in order to:

    • Build a rich picture of actual learner and tutor engagement with such technology across four drama programmes at undergraduate and postgraduate level;
    • Develop an understanding of the impact of using an OSLE in the learning experience;
    • Develop guidelines and case studies for educators to improve learner and tutor use of OSLEs.

As part of this project we are supporting QMU, PALATINE and the HEA Subject Centre for Medicine, Dentistry & Veterinary Medicine (MEDEV) in running the Crossing Virtual Boundaries - Teaching and Research with Online Synchronous Learning Environments (OSLEs) event to be held on the 10 June 2011 (click here for booking details). Here is a good starting point for find out more about the QMU project.

The second project is funded as of part of the HEA Discipline-focused Learning Technology Enhancement Academy Programme. The Collaboration for Excellence in a Distance Learning Environment (Excel-DL)  project is being led by the University of Salford. I haven’t seen much detail about this project but do know their aim is to:

evaluate the use of ‘Elluminate Live’, a synchronous online collaboration delivery method for postgraduate distance learners within the School of the Built Environment, University of Salford (to inform the wider context of Construction & Built Environment Community)

The main reason for highlighting this project is because they are hosting a free online session on ‘Synchronous Online Learning: Bridging the Divide’ on the 10th November 2010 from 1m to 4:15pm. The aim of the event is to:

“is to explore the pros and cons and the pedagogical inspirations of this particular innovation with distinguished academics”

Sessions in the event include:

  • Practice and pedagogies of synchronous online learning – Tim Neumann
  • Synchronous vs asynchronous online learning – Stefan Hrastinski
  • Play time: an interactive demonstration of the possibilities of synchronicity – Peter Chatterton
  • The blended situated learner context for synchronous collaboration – Ian Mills & Georgina Evans
  • Anticipating the future learner – Simon Kear

Click here for more information and to book a place

Software developments

As well as renewed interest in the human side of webinars there are also a couple of software developments worth flagging:

Blackboard:  Wimba + Elluminate = Gemini

If you are a Blackboard user you might be interested in Collborate on Gemini post by Kevin Brace at Aston University. This post reviews a recent webinar he attended outlining Blackboard’s roadmap for merging their recently acquired Wimba and Elluminate webinar platforms.

Adobe Connect Mobile for Android

Mobile technology is everywhere, quite literally, and if you are talking about flexible delivery it is inevitable that mobiles will come into the conversation. Looking at the disputed Wikipedia Comparison of web conferencing software you’ll see very few products are declaring mobile device support. For institutions using or looking into Adobe Connect you might be interested to hear that Connect Mobile for Android is now available. This adds to their support for iPhone related devices (iPad, iPod Touch and iPhone).

Open source alternative: Big Blue Button

I was initially dismissive about the open source webinar tool ‘Big Blue Button’ not finding the interface very intuitive, but then I had session in Adobe Connect and concluded all webinar software is counterintuitive. Rather than going through the details of Big Blue Button I recommend you read Steve Boneham’s blog post, which should have everything you need and more. The Moodle integration looks particularly interesting.   

So what have I missed in all of this? Very interested to hear about other projects or tools in this area. Please share using the comments

iTitle: Full circle with Twitter subtitle playback in YouTube (ALT-C 2010 Keynotes)

ALT YouTube ChannelIn March 2009 Tony Hirst posted a solution for Twitter Powered Subtitles for Conference Audio/Videos on Youtube. A year and a half later, numerous evenings tweaking code, lots of support, advice and promotion from Tony, Brian Kelly and others, and we have come full circle. What began for me as a method to playback real-time tweets with the BBC iPlayer has returned to its origins, Twitter powered subtitles for a conference video on YouTube.

To date the examples of using the Twitter subtitling tool (iTitle), including Reliving ALT-C 2009 keynotes with preserved tweets, have focused on replaying externally hosted video content through this site using the JW Player. This method has allowed greater control over certain aspects like interface design and features like the timeline jump navigation. The disadvantage of this extra control is sustainability.

Whilst I’m very happy working for the RSC there will come the day when I move on, or this website might disappear altogether subsumed into another RSC system/service. If this were to happen there is no guarantee that iTitled videos would still be able to be replayed.

This issue has been at the back of my mind since the very beginning which is partly why from early on I made the iTitle code available for download (I should really update this version of the code). But there has been another solution which has been available since the very beginning but I’ve never had an example to demonstrate it. Just as Tony’s original post demonstrated how the SubRip (*.srt) subtitle file format could be uploaded as part of one of your YouTube videos, iTitle has had the ability to generate SubRip files almost since the very beginning.

So in August when I saw ALT had uploaded videos from ALT-C 2009 to their YouTube channel I thought it would be a great opportunity to amplify keynotes from this years ALT-C and highlight YouTube’s built-in subtitling tools. So after some idea dropping (via Twitter of course) and some follow up emails with Matt Lingard and other members of the ALT team you can now enjoy Donald Clark’s and Sugata Mitra’s keynotes with the ability to see what was said on Twitter in YouTube (links for these at the end of this post).

If you watch these videos via the YouTube site you might need to turn the subtitles on by clicking the ‘cc’ button in the playback toolbar. Annoyingly there doesn’t appear to be a setting for the video which forces captions to play every time, instead YouTube remembers your last choice, but captions can be forced on when a video is embedded. Here is the YouTube help for this feature.

A nice feature of YouTube’s implementation of subtitles/closed captions is their interactive transcript which has a navigable list of the subtitle track, highlighting the active caption. Hopefully YouTube will get around to providing some sort of filtering/search solution like the one used in iTitle’s timeline jump navigation.

Screenshot on YouTube showing subtitle navigation

If you would like to add a twitter track to you own YouTube videos, visit the iTitle tool and select to output in .srt format. This video then explains how to upload subtitle tracks to YouTube videos.

As well as hopefully enhancing the value of ALT’s of these resources to the viewer there is also an argument for doing this to make the videos more search engine friendly. For further explaination of this aspect you should read my guest post Making ripples in a big pond: Optimising FOTE10 videos with an iTitle Twitter track

PS For the ALT videos they wanted to filter out RTs. This was a long overdue feature for iTitle so it made sense to add it.

PPS I also didn’t know you could move the position of subtitles on YouTube videos by click and dragging them.

Click to see "Don't lecture me" - Donald Clark at ALT-C 2010 w/h Twitter track on YouTube“Don’t lecture me” - Donald Clark at ALT-C 2010 w/h Twitter track on YouTubeClick to view "The hole in the wall: self organising systems in education" - Sugata Mitra at ALT-C 2010 w/h Twitter Track on YouTube“The hole in the wall: self organising systems in education” - Sugata Mitra at ALT-C 2010 w/h Twitter Track on YouTube

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:

Powered by NEWSTOEBOOK.COM

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