Monthly Archives: October 2011

Reading List Mark 2 – Part 5

This is Part 5, the last part in a series about an example app that I put together to demonstrate and describe the use of various Google Apps Script features. See Part 1 for an introduction. This part is “Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page“.

Parts Overview

  1. Introduction to the app, and a short screencast showing the features
  2. Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component
  3. Using the UrlFetch Services to interact with the Google+ API and grab info on articles pointed to by users in their activity stream
  4. Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist
  5. Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page <– You Are Here

Putting it all together

So at this stage we’ve done pretty much everything required for this example app. The final task is to extend the standard Spreadsheet menu to give the user access to the custom features of selecting a tasklist, and kicking off an update (URL pull and synchronisation). It’s very easy to extend the menu; in a few lines of code we’re going to end up with something like this:

Menu

It’s as simple as this:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ 
    {name: "Update", functionName: "update"},
    {name: "Select Task List", functionName: "taskListUi"}
  ];
  ss.addMenu("Articles", menuEntries);
}

We use the addMenu() method of the Spreadsheet class to create a new menu entry with an array of objects representing menu items. And the function name? onOpen() is one of a number of built-in simple event handler functions; this one runs automatically when a spreadsheet is opened – an ideal time to extend the menu.

The complete script

So we’re done with the final part! Let’s celebrate with the script in its entirety. And a beer. Cheers!

// Constants

APIKEY = "AIz...drBs"; // don't forget to get your own and record it here
ACTIVITYLISTURL = "https://www.googleapis.com/plus/v1/people/{userId}/activities/{collection}";
READINGLISTCELL = "D1";
USERIDCELL = "B1";
USERID = "106413090159067280619"; // Fallback default


// update()
// Pulls in article links into sheet and synchronises with task list

function update() {
  // First, check that we have a tasklist id already; it's stored in
  // the comment section of the 'readinglistcell'
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var taskListId = sh.getRange(READINGLISTCELL).getComment();
  // If we don't have an id, tell the user to choose a tasklist
  if(taskListId === "") {
    SpreadsheetApp.getActiveSpreadsheet().toast(
      "Use Articles -> Select Task List to choose a task list",
      "No Task List", 
      5
    );
  // Otherwise, we know which task list to synchronise with, so
  // go and update the reading list with URLs from the Google+ activity
  // list, and then sync that with the task list items
  } else {
    retrieveActivityUrls_();
    synchronise_(taskListId); 
  }
}


// taskListUi()
// Displays a Ui to allow the user to select a tasklist to manage
// the reading tasks. Can select an existing task list or create a new one

function taskListUi() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication();
  app.setTitle('Task Lists');

  // We'll have a grid and a button in this
  // vertical panel
  var panel = app.createVerticalPanel();

  // Use a listbox to display a choice of existing tasklists
  var lb = app.createListBox(false);
  lb.setName('existingList');
  var tasklists = getTasklists_();
  for (var tl in tasklists) {
    lb.addItem(tasklists[tl].getTitle());
  }  

  // Use the grid to layout the listbox, a textbox for a new list,
  // and some corresponding labels
  var grid = app.createGrid(2, 2);
  grid.setWidget(0,0, app.createLabel("Existing:"));
  grid.setWidget(0,1, lb);
  grid.setWidget(1,0, app.createLabel("Or new:"));
  grid.setWidget(1,1, app.createTextBox().setName('newList'));

  // The only button; handler will be linked to this button click event
  // Remember to add the grid contents to the callback context
  var button = app.createButton("Choose");  
  var chooseHandler = app.createServerClickHandler('handleChooseButton_');
  chooseHandler.addCallbackElement(grid);
  button.addClickHandler(chooseHandler);
  
  // Put it all together and show it
  panel.add(app.createLabel("Select existing or create new list"));
  panel.add(grid);
  panel.add(button);
  app.add(panel);
  doc.show(app);
}  


// handleChooseButton_(e)
// Handler for 'Choose' button on taskListUi Ui; creates a new task list
// if a new one has been specified; grabs the ID of the chosen task list
// and stores the task list name and id in the TASKLISTCELL

function handleChooseButton_(e) {
  
  // Assume an existing list was chosen
  var selectedList = e.parameter.existingList;

  // But check for a new list being specified; if it as, create
  // a new task list
  if(e.parameter.newList != '') {
    selectedList = e.parameter.newList;
    var newTaskList = Tasks.newTaskList().setTitle(selectedList);
    Tasks.Tasklists.insert(newTaskList);
  }

  // Grab the list of tasklists, because we'll need the id
  var taskLists = getTasklists_();
  var taskListId = -1;
  for(tl in taskLists){
    if(taskLists[tl].getTitle() === selectedList) { 
      taskListId = taskLists[tl].getId();
      break;
    }
  }

  // Record the list name and id
  var sh = SpreadsheetApp.getActiveSheet();
  var cell = sh.getRange(READINGLISTCELL);
  cell.setValue(selectedList);
  cell.setComment(taskListId);

  // Close the Ui popup and display the name of the chosen list
  var app = UiApp.getActiveApplication();
  app.close();
  SpreadsheetApp.getActiveSpreadsheet().toast(selectedList, "Selected List", 3);
  return app;
}


// onOpen()
// Event based function called when the spreadsheet is opened; adds items
// to the menu

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ 
    {name: "Update", functionName: "update"},
    {name: "Select Task List", functionName: "taskListUi"}
  ];
  ss.addMenu("Articles", menuEntries);
}


// getTasklists()
// Retrieve a list of the user's tasklists (uses the APIs Services)
// Note that the Tasks Services docu is not accurate here; we would
// expect to be able to use the TasklistsCollection class.

function getTasklists_() {
  var tasklistsList = Tasks.Tasklists.list();
  return tasklistsList.getItems();
}


// retrieveActivityUrls_()
// Use UrlFetch to retrieve a Google+ API resource: activities for a person
// Use Javascript data structures; restrict the number of API calls

function retrieveActivityUrls_() {

  // Grab existing list of URLs
  var sh = SpreadsheetApp.getActiveSheet();
  var lastRow = sh.getLastRow();
  var urlList = sh.getRange(2, 1, lastRow - 1 || 1) .getValues();
  var list = {'old': {}, 'new': []};
  for (var i in urlList){
    list['old'][urlList[i]] = 1;
  }

  // Use the userid in the sheet, fallback to a favourite
  var userid = sh.getRange(USERIDCELL).getValue() || USERID;

  // Build Google+ API resource and retrieve it; parse JSON content
  var actListUrl = buildActivityListUrl_(userid, 'public', APIKEY);
  var jsonString = UrlFetchApp.fetch(actListUrl).getContentText();
  var activities = Utilities.jsonParse(jsonString);

  // We're looking for the item object attachments, where the 
  // attachment's objectType is 'article'. We want the url and displayName
  for (var i in activities.items) {
    var attachments = activities.items[i].object.attachments;
    for (var a in attachments) {
      var attachment = attachments[a];
      // We've got a URL and title; store it as new if it doesn't 
      // already exist. Store it as list of lists, ready for 
      // a setValues([][]) insert
      if (attachment.objectType == 'article') {
        if (! (attachment.url in list['old'])) {
          list['new'].push([attachment.url, attachment.displayName]);
        }
      }
    }    
  }

  // Blammo!
  if (list['new'].length) {
    sh.getRange(lastRow + 1, 1, list['new'].length, 2).setValues(list['new']);
  }

}


// synchronise(taskListId)
// Synchronise the URLs in the spreadsheet with items in the chosen tasklist
// The task list item id for a URL is stored in the comment for that URL cell

function synchronise_(taskListId) {

  // Grab list of all URLs, and associated comments
  var sh = SpreadsheetApp.getActiveSheet();
  var urlRange = sh.getRange(2, 1, sh.getLastRow() - 1, 1);
  var urls = urlRange.getValues();
  var comments = urlRange.getComments();
  
  // For each URL, check the status of the associated task.
  // If there isn't an associated task, create one.
  for (var i = 0, j = urls.length; i < j; i++) {
    if (comments[i] == "") {
      Logger.log("New task");
      var task = Tasks.newTask(); 
      task.setTitle(urls[i]);
      var newTask = Tasks.Tasks.insert(task, taskListId);
      sh.getRange(i + 2, 1).setComment(newTask.getId());
    } else {
      Logger.log("Existing task");
      var existingTask = Tasks.Tasks.get(taskListId, comments[i][0]);
      if (existingTask.getStatus() === "completed") {
        sh.getRange(i + 2, 1, 1, 2).setFontLine("line-through");
      }
    }
  }
}


// buildActivityListUrl_(userId, collection, apiKey)
// Creates a specific resource address (URL) for the public activities
// for a given person in Google+
// See https://developers.google.com/+/api/latest/activities/list
// This will be obsolete when there are direct Google+ Services for 
// Apps Script

function buildActivityListUrl_(userId, collection, apiKey) {

  var actListUrl = ACTIVITYLISTURL;
  actListUrl = actListUrl.replace(/{userId}/, userId);
  actListUrl = actListUrl.replace(/{collection}/, collection);
  actListUrl = actListUrl + '?key=' + apiKey;

  return actListUrl;
}    

 

Reading List Mark 2 – Part 4

This is Part 4 in a series about an example app that I put together to demonstrate and describe the use of various Google Apps Script features. See Part 1 for an introduction. This part is “Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist“.

Parts Overview

  1. Introduction to the app, and a short screencast showing the features
  2. Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component
  3. Using the UrlFetch Services to interact with the Google+ API and grab info on articles pointed to by users in their activity stream
  4. Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist <– You Are Here
  5. Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page

Putting this into context: the Update request

We’ve covered a lot of ground in the previous three parts in this series. Now we’re at the stage where we have the functions for

  • creating a Ui for choosing an existing / creating a new tasklist
  • handling the button event on the Ui
  • getting a list of tasklists
  • retrieving URLs from a Google+ activity stream

So the one main piece of work outstanding is synchronising the retrieved URLs as tasks on the chosen tasklist.

If you watch the screencast shown in Part 1 you’ll see that the synchronisation is part of a more general ‘update’ request, that includes the fetching of new URLs from Google+ and synchronising them with the tasklist. So let’s have a look at the function that binds those two things together.

Here’s the update() function, which we’ll allow the user to call from a menu item (we’ll cover this in the next instalment).

READINGLISTCELL = 'D1';

function update() {
  // First, check that we have a tasklist id already; it's stored in
  // the comment section of the 'readinglistcell'
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var taskListId = sh.getRange(READINGLISTCELL).getComment();
  // If we don't have an id, tell the user to choose a tasklist
  if(taskListId === '') {
    SpreadsheetApp.getActiveSpreadsheet().toast(
      "Use Articles -> Select Task List to choose a task list",
      "No Task List",
      5
    );
  // Otherwise, we know which task list to synchronise with, so
  // go and update the reading list with URLs from the Google+ activity
  // list, and then sync that with the task list items
  } else {
    retrieveActivityUrls_();
    synchronise_(taskListId);
  }
}

This function grabs a reference to the active sheet, and pulls the comment from the cell that we’ve designated as where the reading list tasklist info is stored: READINGLISTCELL. The name is stored in the cell, and the ID is stored in the cell’s comment. If there isn’t an ID, then we’ll ask the user to choose a tasklist using the Ui we built in Part 2. The Browser class in Google Apps Script’s Base Services gives us a nice dialog box that looks like this:

Message Box

But there’s also a nice visual message feature that’s available in the Spreadsheet Services, specific to a spreadsheet: toast(). Calling this causes a popup to appear in the lower right of the screen, which stays visible for a short while. This is what it looks like:

Toast message

Because the ‘toast’ name is so evocative, we’ll use it in our function to prompt the user to choose a tasklist.

If there’s already a tasklist chosen, then we go straight into retrieving the URLs (see Part 3) and then call the synchronise_() function, passing the ID of the tasklist.

Synchronising URLs and Tasks

Ok, so what do we need to do to synchronise the URLs? It’s similar to the technique described in the great article “Integrating with Google APIs – Creating a simple reading list“. There are a couple of differences: I’m not going to use the UrlShortener Services, and I’m going to try and reduce the number of API calls by bulk-grabbing the cell data.

First, we get a range reference on the active sheet, which equates to the list of URLs already there. We get all of the URLs (urlRange.getValues()) and all of the corresponding comments (urlRange.getComments()).

function synchronise_(taskListId) {

  // Grab list of all URLs, and associated comments
  var sh = SpreadsheetApp.getActiveSheet();
  var urlRange = sh.getRange(2, 1, sh.getLastRow() - 1, 1);
  var urls = urlRange.getValues();
  var comments = urlRange.getComments();

We go through each of the URLs, and create a new task in the tasklist if there isn’t already something in the comment for that URL:

  • instantiate a new task object: Tasks.newTask()
  • add the title: task.setTitle()
  • add the task to the tasklist: Tasks.Tasks.insert()
  • insert the new task’s ID into the comment for the URL: setComment()

Otherwise we’ve already created a task for the URL, so we grab the task to get the status, and if it’s marked as completed, we format the URL and corresponding description (in the next column) to set strike-through text.

  // For each URL, check the status of the associated task.
  // If there isn't an associated task, create one.
  for (var i = 0, j = urls.length; i < j; i++) {
    if (comments[i] == "") {
      Logger.log("New task");
      var task = Tasks.newTask();
      task.setTitle(urls[i]);
      var newTask = Tasks.Tasks.insert(task, taskListId);
      sh.getRange(i + 2, 1).setComment(newTask.getId());
    } else {
      Logger.log("Existing task");
      var existingTask = Tasks.Tasks.get(taskListId, comments[i][0]);
      if (existingTask.getStatus() === "completed") {
        sh.getRange(i + 2, 1, 1, 2).setFontLine('line-through');
      }
    }
  }
}

That’s it. Stop by next time for the last part in this series, where we put everything together and insert a 2-item menu entry to tie it all together. Thanks for reading!

 

Reading List Mark 2 – Part 3

This is Part 3 in a series about an example app that I put together to demonstrate and describe the use of various Google Apps Script features. See Part 1 for an introduction. This part is “Using the UrlFetch Services to interact with the Google+ API (after all, it’s REST-based!) and grab info on articles pointed to by users in their activity stream“.

Parts Overview

  1. Introduction to the app, and a short screencast showing the features
  2. Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component
  3. Using the UrlFetch Services to interact with the Google+ API and grab info on articles pointed to by users in their activity stream <– You Are Here
  4. Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist
  5. Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page

UrlFetch Services

If you’ve ever used an HTTP client library in other contexts, you’ll be completely at home with the base classes available in the UrlFetch Services. Following the simplest thing that could possibly work philosophy, all we need to do to fetch a resource and grab the payload is to use the UrlFetchApp class, specifically the fetch() method. It returns an HTTPResponse object, which has everything you need: content, headers and response code.

Here’s an example of getting the signature from the server that serves this site:

var response = UrlFetchApp.fetch('http://www.pipetree.com/');
Logger.log(response.getHeaders()['Server']);
--> Apache/2.2.14 (Ubuntu)

The Google+ API largely follows a RESTful design, which means that we can use the UrlFetch Services to interact with it.

The Google+ API

The Google+ API is relatively new, and at the moment, read-only. This is fine for what we want to use it for in this example. There are two aspects of the API that are relevant for us:

  • The REST-based nature means that we see the Google+ objects such as People, Activities and Comments as resources that we retrieve with HTTP
  • To use the API we need either an OAuth 2.0 token or an API key

The UrlFetch Services provides us with a facility in the form of the OAuthConfig class for configuring and managing OAuth in a client context. But we’ll go for the simpler approach and use an API key, which we can obtain by using the Google API Console – see the previous instalment of this series for more details about this: Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component.

The idea for this example app is to capture a list of URLs that a person on Google+ has posted, and perhaps commented on. We can get this info from the Activities part of the API.

To get the activity stream for a given person, we need to retrieve the following resource:

https://www.googleapis.com/plus/v1/people/{userId}/activities/{collection}

The {userId} is the Google+ ID of the person, and {collection} in this case is “public”, the only collection available right now. In addition we need to specify our API key on a ‘key’ parameter in the query string. The default representation is JSON. This is what we get back as a result (heavily elided for brevity):

{
  "kind": "plus#activityFeed",
  "title": "Plus Public Activity Feed for Martin Hawksey",
  "id": "tag:google.com,2010:/plus/people/1146628[...]/activities/public",
  "items": [
  {
    "kind": "plus#activity",
    "title": "Latest post from me. Elevator pitch: [...]",
    "id": "z12cxlppixzwjbqzi04cdnvg1wbyflbz3r00k",
    "url": "https://plus.google.com/1146628[...]",
    "verb": "post",
    "object": {
      "objectType": "note",
      "content": "Latest post from me. Elevator pitch: Service [...]",
      "originalContent": "",
      "url": "https://plus.google.com/1146628[...]",
      "attachments": [
      {
        "objectType": "article",
        "displayName": "SpreadEmbed: Turning a Google Spreadsheet [...]",
        "url": "http://mashe.hawksey.info/2011/10/spreadembed/"
      },
      {
        "objectType": "photo",
        "image": {
        "url": "http://images0-focus-opensocial.google[...]",
        "type": "image/jpeg"
      },
      "fullImage": {
        "url": "http://mcdn.hawksey.info/wp-content/uploads/[...]",
        "type": "image/jpeg",
        "height": 204,
        "width": 350
      }
      [...]

Even after heavy eliding for this blog post, that’s still an awful lot of JSON, but we’re only actually interested in the URLs that the person links to. We can spot these in the “plus#activity” items array, as attachments with objectType “article” – they have url and displayName attributes:

{
  "items": [
  {
    "kind": "plus#activity",
    "object": {
      "attachments": [
      {
        "objectType": "article",
        "displayName": "SpreadEmbed: Turning a Google Spreadsheet [...]",
        "url": "http://mashe.hawksey.info/2011/10/spreadembed/"
      },
      [...]

Partial Responses

And it just so happens that in the interests of efficiency, Google offers partial responses, in the form of a fields parameter. So we can add this parameter to the query string, with an XPath-style value like this:

fields=items/object/attachments(url,displayName)
So the resulting JSON representation is a lot lighter, like this:
{
  "items": [
    {
      "object": {
        "attachments": [
          {
            "displayName": "SpreadEmbed: Turning a Google Spreadsheet[...]",
            "url": "http://mashe.hawksey.info/2011/10/spreadembed/"
          }
        ]
      }
    },
  ]
}

Much better!

Retrieving the Activity Data

So now it’s time to have a look at the code that will retrieve the activity info and insert the URLs into the spreadsheet. We’ll do this in a single function retrieveActivityUrls_(), which will

  • grab any existing URLs listed in the sheet, so we can work out whether each new one retrieved with the API call is already there or not
  • Determine the ID of the person on Google+ we want to follow
  • Build the name of the Google+ activity resource (the Google+ API URL), fetch it and parse the content
  • Look through the parsed content and note any new URLs that the person has linked to on Google+
  • Insert those new URLs into the sheet

Let’s go!

First, some constants.

APIKEY = 'AIza[...]drBs'; // (get your own!)
ACTIVITYLISTURL = 'https://www.googleapis.com/plus/v1/people/{userId}/activities/{collection}';
USERIDCELL = 'B1';
USERID = '106413090159067280619'; // Fallback: Mahemoff!

Now for the function. We get a handle on the active sheet, note the last row (which denotes where the list of URLs currently ends), and gets those URLs. We’re assuming that the list starts at row 2, i.e. there’s a header line in row 1. The resulting urlList array is two dimensional, although as we’ve specified we only want 1 column width of values, the data will look something like this:

[[http://cloud9ide.com], [http://jsconf.eu], [...]]

We create an object to hold the existing (‘old’) URLs, and the eventual ‘new’ URLs about to be retrieved. We’re using an object ‘old’ for the existing URLs so we can easily check whether a new one is in the list or not. We just need to use an array for the ‘new’ URLs.

function retrieveActivityUrls_() {

  // Grab existing list of URLs
  var sh = SpreadsheetApp.getActiveSheet();
  var lastRow = sh.getLastRow();
  var urlList = sh.getRange(2, 1, lastRow - 1 || 1) .getValues();
  var list = {'old': {}, 'new': []};
  for (var i in urlList){
    list['old'][urlList[i]] = 1;
  }

We’re going to retrieve the activity for a Google+ person, and the person is identified by an ID either in a cell in the sheet identified by the range in constant USERIDCELL, (see the screencast in Part 1) or a default specified in constant USERID.

  // Use the userid in the sheet, fallback to a favourite
  var userid = sh.getRange(USERIDCELL).getValue() || USERID;

Now we have enough information to build the Google+ API resource URL, so we call a helper function buildActivityListUrl_() passing it the user ID, the collection (‘public’), and our API key. (We’ll look at buildActivityListUrl_() shortly.) We use the UrlFetchApp fetch() method to grab the resource, calling getContentText() to obtain the JSON content. And with a JSON parser available in the Utilities Services, we quickly have all we need to retrieve those URLs posted in the activity list in the ‘activities’ object.

  // Build Google+ API resource and retrieve it; parse JSON content
  var actListUrl = buildActivityListUrl_(userid, 'public', APIKEY);
  var jsonString = UrlFetchApp.fetch(actListUrl).getContentText();
  var activities = Utilities.jsonParse(jsonString);

From examining the JSON representation of the activities earlier in this post, we know we’ll be expecting items, and within each item an object member, and within that object member a number of attachments. We’re only interested in those attachments of type ‘article’, and if we find one, we want the url and the displayName.

If we’ve got an article attachment, we then need to determine whether it’s a new URL or one we have already. That’s where the list object comes in. Unless we can find the URL in the ‘old’ object, it’s a new one so we need to add it to the ‘new’ list.

  // We're looking for the item object attachments, where the
  // attachment's objectType is 'article'. We want the url and displayName
  for (var i in activities.items) {
    var attachments = activities.items[i].object.attachments;
    for (var a in attachments) {
      var attachment = attachments[a];
      // We've got a URL and title; store it as new if it doesn't
      // already exist. Store it as list of lists, ready for
      // a setValues([][]) insert
      if (attachment.objectType == 'article') {
        if (! (attachment.url in list['old'])) {
          list['new'].push([attachment.url, attachment.displayName]);
        }
      }
    }
  }

At this stage, we’re ready to add any new URLs to the list in the sheet. Note that when we pushed onto the ‘new’ list, we pushed an array of the url and displayName. This is the ideal two dimensional array ([[a, b], [c, d], [...]) to specify as the value in the setValues() call on a two dimensional cell Range. And useful if we want to follow the sage advice in “Common Programming Tasks” on using batch operations where possible: we can add all the new URL info to the sheet in a single getRange() and setValues() call pair:

  // Blammo!
  if (list['new'].length) {
    sh.getRange(lastRow + 1, 1, list['new'].length, 2).setValues(list['new']);
  }

}

Now that’s the retrieveActivityUrls_()  function out of the way, let’s just have a look at the helper function buildActivityListUrl_() that we called earlier. It takes three parameters: the ID of the person on Google+, the collection we want to retrieve (‘public’ in this case), and the API key. It uses a URL template in the ACTIVITYLISTURL constant and replaces the placeholders. It also adds the API key, and the XPath fields statement.

function buildActivityListUrl_(userId, collection, apiKey) {

  var actListUrl = ACTIVITYLISTURL;
  actListUrl = actListUrl.replace(/{userId}/, userId);
  actListUrl = actListUrl.replace(/{collection}/, collection);
  actListUrl += '?key=' + apiKey;
  actListUrl += '&fields=items/object/attachments(url,displayName)';

  return actListUrl;
}

That brings us to the end of this part in the series. At this stage we have covered the tasklist determination using a user interface and pulled the URLs posted on a Google+ activity stream, storing them in the sheet.

In the next part, we’ll look at synchronising the URLs in the sheet with tasks on the chosen tasklist.

Stay tuned!

Reading List Mark 2 – Part 2

This is Part 2 in a series about an example app that I put together to demonstrate and describe the use of various Google Apps Script features. See Part 1 for an introduction. This part is “Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component“.

Parts Overview

  1. Introduction to the app, and a short screencast showing the features
  2. Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component <– You Are Here
  3. Using the UrlFetch Services to interact with the Google+ API and grab info on articles pointed to by users in their activity stream
  4. Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist
  5. Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page

Tasks API

The availability of the Tasks API within the Google Apps Script context was announced recently on the Google Code blog. Using the Google APIs Discovery Service makes it easier to build client libraries for the myriad APIs available; this is what Google have done to make the BigQuery, UrlShortener, Prediction and Tasks APIs available for us in Google Apps Script. Collectively they’re known as Google APIs Services.

Unlike the other services already available — such as those pertaining directly to the Google Apps platform like Spreadsheet, Gmail, DocsList and Calendar — you need to use the Google API Console to turn on these new APIs within the context of a project, agree to the terms & conditions, and note the courtesy call limits available to you.

Tasks API selected for use in the API Console

You can see here a shot of the Tasks API selected for use within a project I created in the Google API Console, and a courtesy limit of 5000 calls per day. Check out a previous blog post “Automated Email-to-Task Mechanism with Google Apps Script” for more background on this Tasks API and the Google article “Integrating with Google APIs – Creating a simple reading list“ for a step-by-step account of enabling the API itself  (called Tasks Services in Google Apps Script).

Working with Tasklists and Tasks

So, what do we need to do with the Tasks Services? As you can gather from watching the screencast in the Part 1, we need to retrieve a list of existing tasklists, we might need to create a new tasklist, and we need to be able to add tasks to a specific tasklist. We also need to build a Ui component to present the list of the user’s tasklists, so a tasklist can be chosen, plus an option to create a new tasklist.

Retrieving the Tasklists

Let’s start with retrieving a list of tasklists.  While this is pretty simple, we’ll encapsulate it in a function as we’ll be calling it a couple of times within this example.

function getTasklists_() {
  var tasklistsList = Tasks.Tasklists.list();
  return tasklistsList.getItems();
}

We use the Tasklists member of the Tasks class which gives us a TasklistsCollection class. We call call the list() method to retrieve a Tasklists object – which represents a list of all the authenticated user’s tasklists. Calling getItems() on this object gives us an array of Tasklist objects – which is the list of tasklists that we need.

Building the Ui

We’ll need the list of tasklists to show in the Ui component. So let’s look at building that Ui component next. Building user interfaces in Google Apps Script can appear somewhat daunting at first glance, but don’t worry – it’s actually very straightforward. You have the choice between building the Ui in code (by using Ui Services calls) or using a visual editor much like you might in other IDEs. This latter approach was announced and described in detail on the Google Apps Developer blog, following this year’s Google I/O.

We’ll build our Ui in code. If you need an intro to this, have a look at the Google Apps Script “Building a User Interface” documentation.

We want to be able to display to the user a list of their existing tasklists so they can choose one, and also give them a chance to enter the name of a new tasklist instead. So we need a dropdown list (otherwise known as a listbox), a textbox, some text labels, and a button. This is what the end result should look like:

Task Lists Ui

It’s showing the Ui title (“Task Lists”), some labels, a dropdown list with the two existing tasklists that the authenticated user has already, an empty textbox (behind the dropdown) where a new tasklist name can be entered and a button to which we can attach an event handler.

Layout is achieved using Panels and Grids, both containers for elements. Here, we’ll use a VerticalPanel, where the elements are arranged vertically, and a Grid, where we can arrange elements in a 2-dimensional way.

Schematically, this is what we’re going to do:

Task Lists Ui Layout Schematic

So, let’s look at the code that builds this Ui. We start by getting a handle on the active spreadsheet (doc), and creating a new Ui application (app), giving it a title. At the end of this function we’ll be passing the Ui application to the active spreadsheet to show.

function taskListUi() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication();
  app.setTitle('Task Lists');

Next, we create a vertical panel (panel), and a listbox (lb), both of which exist independently. We set a name for the listbox (‘existingList’) so we can refer to it later in the callback context. After using the getTasklists_() function described earlier, we fill the listbox with those tasklist names (or ‘titles’) retrieved.

  // We'll have a grid and a button in this
  // vertical panel
  var panel = app.createVerticalPanel();

  // Use a listbox to display a choice of existing tasklists
  var lb = app.createListBox(false);
  lb.setName("existingList");
  var tasklists = getTasklists_();
  for (var tl in tasklists) {
    lb.addItem(tasklists[tl].getTitle());
  }

Once we’ve got the listbox populated, it’s time to create the grid (a 2 x 2 layout) and fill the cells with labels, the listbox, and a textbox. We give a name to the textbox (‘newList’) so we can refer to it later in the callback context, in the same way as for the listbox.

  // Use the grid to layout the listbox, a textbox for a new list,
  // and some corresponding labels
  var grid = app.createGrid(2, 2);
  grid.setWidget(0,0, app.createLabel("Existing:"));
  grid.setWidget(0,1, lb);
  grid.setWidget(1,0, app.createLabel("Or new:"));
  grid.setWidget(1,1, app.createTextBox().setName("newList"));

Finally we have the button element. Simple enough, but we also need to add a click handler to it in the form of a serverClickHandler. This handler exists as a function in this same script: handleChooseButton_() which is defined after this. The important thing to notice here is that we create an independent serverClickHandler, give it some element context (in this case the grid element we created earlier) so that the element values are available in the context of the handling function, and then assign that handler as a click handler to the button element.

  // The only button; handler will be linked to this button click event
  // Remember to add the grid contents to the callback context
  var button = app.createButton("Choose");
  var chooseHandler = app.createServerClickHandler("handleChooseButton_");
  chooseHandler.addCallbackElement(grid);
  button.addClickHandler(chooseHandler);

Once we’ve created the button element and sorted out how the click event will be handled, it’s time to put the Ui together. We add the elements one by one to the vertical panel: a label, the 2 x 2 grid, then the button. Then we add the actual panel to the app, hand it over to the active spreadsheet to be displayed, and let go!

  // Put it all together and show it
  panel.add(app.createLabel("Select existing or create new list"));
  panel.add(grid);
  panel.add(button);
  app.add(panel);
  doc.show(app);
}

Handling the Button Click

The handling of the click is performed by handleChooseButton_(), as determined by the call to createServerClickHandler() earlier. Let’s examine handleChooseButton_() step by step.

We start by assuming that the user has chosen an existing tasklist – we get the value from the listbox via its name within the parameter attribute of the event object passed to the function, i.e. e.parameter.existingList. Then again, if we’ve got a value in the textbox representing the option to create a new tasklist, we create a new tasklist using the Tasks.newTaskList() method of the Tasks Services, and give that new tasklist the title that was specified in the textbox.

Note that setTitle() was called directly in a ‘chain’ from newTaskList(), and the result assigned to the newTaskList variable. This is possible due to the way the Tasks API has been designed, with most TaskList methods returning the TaskList object itself; this is known as the ‘bean’ object.

function handleChooseButton_(e) {

  // Assume an existing list was chosen
  var selectedList = e.parameter.existingList;

  // But check for a new list being specified; if it as, create
  // a new task list
  if(e.parameter.newList != '') {
    selectedList = e.parameter.newList;
    var newTaskList = Tasks.newTaskList().setTitle(selectedList);
    Tasks.Tasklists.insert(newTaskList);
  }

Now we’ve determined the chosen tasklist (either an existing one or a newly created one) we grab the complete list with getTasklists_() and have a look through them to find the corresponding tasklist id, which we’ll need when we want to insert new tasks into that tasklist.

  // Grab the list of tasklists, because we'll need the id
  var taskLists = getTasklists_();
  var taskListId = -1;
  for(tl in taskLists){
    if(taskLists[tl].getTitle() === selectedList) {
      taskListId = taskLists[tl].getId();
      break;
    }
  }

Ok, we’ve determined and retrieved the id for the chosen tasklist, so now it’s time to save that info. We’ll do that by writing both the tasklist name and id into a cell; the tasklist name into the cell itself, and the id into the cell’s comment. This is a common idiom and is quite useful – you can store related information in a single cell, and don’t use up too much cell ‘real estate’. The cell we’re going to use is stored as a constant: READINGLISTCELL; in my spreadsheet that’s cell D1.

  // Record the list name and id
  var sh = SpreadsheetApp.getActiveSheet();
  var cell = sh.getRange(READINGLISTCELL);
  cell.setValue(selectedList);
  cell.setComment(taskListId);

Once we’ve stored the information, it’s time for the handler to make sure the Ui is closed, and to acknowledge to the user that a selected list has been recognised. We do this by closing the active Ui application, and using the Spreadsheet’s generic ‘toast‘ mechanism to pop up a message.

  // Close the Ui popup and display the name of the chosen list
  var app = UiApp.getActiveApplication();
  app.close();
  SpreadsheetApp.getActiveSpreadsheet().toast(selectedList, "Selected List", 3);
  return app;
}

Hurray – that’s the Ui component and the handler all taken care of!

Tune in next time when in Part 3 we look at retrieving information from the Google+ activity stream via the Google+ API, using nothing more than our trusty Google Apps Script HTTP client, UrlFetchApp.

Reading List Mark 2 – Part 1

Over on the Google Apps Scripts articles section of the Google Code site there’s a great article “Integrating with Google APIs – Creating a simple reading list” which takes the reader through a nice example of using a couple of APIs that have recently been made available to Google Apps Script via the Google APIs Services – the UrlShortener API and the Tasks API. Inspired by the article, I decided to take the idea a little bit further and build something I could demo and then explain at our Manchester GTUG meetup.

I used the same idea of a reading list, but added a Ui component to allow the user to select a task list interactively, and instead of using the UrlShortener API, I explored the relatively young Google+ API, in that I pulled in articles to read automatically from URLs posted by people on Google+.

Also, in revisiting some of the original reading list features, I tried to approach the coding differently, to be mindful of the advice in the “Optimising Scripts for Better Performance” section of the “Common Programming Tasks” guidelines.

Here’s a short screencast that shows the ‘Reading List Mark 2′ in action:

 

I’ll describe how everything is put together over the next few blog posts:

  1. This introduction
  2. Using the Tasks API to retrieve and insert tasklists, and the Ui Services to build the tasklist chooser component
  3. Using the UrlFetch Setvices to interact with the Google+ API and grab info on articles pointed to by users in their activity stream
  4. Synchronising the URL list in the spreadsheet with corresponding tasks in the chosen tasklist
  5. Putting it all together and using the OnOpen event to insert a new 2-item menu entry on the spreadsheet’s page

Stay tuned!

Automated email-to-task mechanism with Google Apps Script

Last night at the Manchester Google Technology User Group (GTUG) meetup we looked in detail at an example script that used various Google Apps Script services. More on that another time.

At the end of the meetup, I suggested an example of something that would be really easy to put together using Google Apps Script, and very useful: a mechanism to convert incoming emails automatically into tasks.

You can of course convert an email into a task manually using the Gmail UI like this:

Manually adding a task from an email

But rather than have to open Gmail, find the task email, select it and then choose More Actions -> Add to Tasks, I wanted a hands-off facility where I could, say from my work email, fire off a quick one-liner task that would be added to my list of tasks automatically, silently and without fuss.

With effective use of Gmail’s filter facility, labels and a little bit of Apps Script using the Gmail Services, I was able to create a mechanism in the time it took to enjoy my morning coffee.

 

Building the Automated Email-to-Task Mechanism

Here’s how I saw it working:

  1. create a couple of new labels: ‘newtask’ and ‘newtaskdone’
  2. specify a Gmail filter to assign the label ‘newtask’ to emails coming from my work email address, and with the recipient being ‘qmacro+task@gmail.com’
  3. write a script to process messages in threads assigned to the ‘newtask’ label by parsing the subject out, creating a new task, and inserting that task into my tasklist
  4. schedule this script to run hourly

Then I could fire off an email to qmacro+task@gmail.com from work, with the task 1-liner in the Subject, and have that task automatically show up on my task list. Ideal!

 

The Filter

Once you have the labels, create the filter. This is what the action part of my filter looks like:

Specifying the filter actions

I’m specifying that the email be assigned to the label ‘newtask’, that it should marked as read immediately, and not appear in the inbox. That way, I don’t get distracted by the noise of task emails in my inbox. If you’re wondering about the ‘newtaskdone’ label, we’ll get to that in a minute.

 

The Script Context

Now we’re all set up – we can write the script to process the relevant emails, i.e. all those assigned the label ‘newtask’.

Start by creating a new Spreadsheet  – the script can live attached to that. Add the text ‘Processed tasks’ to cell A1. We’ll use this to show how many tasks the script has processed. Use the menu option Tools -> Script editor to get to the Google Apps Script editor.

Mail Management sheet

 

You can call your project ‘Mail Management’, or whatever you want:

Mail Management script

 

The Script Code

Ok, let’s run through the script step by step.

We start with a few constants: the name of the tasklist into which we want our new tasks inserted, and the two labels.

TASKLIST = "DJ's list";
LABEL_PENDING = "newtask";
LABEL_DONE = "newtaskdone";

Next we have a helper function getTasklistId_ which uses the Tasks Services from the new Google APIs Services in Apps Script. You’ll need to explicitly state you want to use the Google APIs Services from the File menu, which will lead you to a popup where you can switch on the Tasks API and use the Google API Console to create a project and generate an API key which you’ll need. All of this is described in ample detail in a great article “Integrating with Google APIs – Creating a simple reading list“.

This getTasklistId_ function returns a tasklist ID for a given tasklist name — in this case we’ll be asking for the ID of the tasklist called “DJ’s list”. It’s early days for the Tasks API and there are a few oddities: In theory we should be able to use the simple API call :

Tasks.Tasklists.get(tasklistName)

but this is currently resulting in an error. So instead we’ll grab a list of all the tasklists, and iterate over them looking for our tasklist name. I’ve suffixed the name of this function, and others in this script, with an underscore; this prevents them from showing up in the dropdown list of runnable functions at the top of the editor.

function getTasklistId_(tasklistName) {
  var tasklistsList = Tasks.Tasklists.list();
  var taskLists = tasklistsList.getItems();
  for (tl in taskLists) {
    var title = taskLists[tl].getTitle();
    if (title == tasklistName) {
      return taskLists[tl].getId();
    }
  }
}

Next we have another helper function addTask_ which will create a new task, given a string, and add that new task to a tasklist, given a tasklist ID. Note the separation of concerns – a task is created independently of a tasklist, then inserted into that tasklist.

function addTask_(title, tasklistId) {
  var newTask = Tasks.newTask().setTitle(title);
  Tasks.Tasks.insert(newTask, getTasklistId_(tasklistId));
}

We then come to the definition of processPending_, which does the bulk of the mechanism’s work. This function gets a handle on each of the two labels we mentioned earlier (labels in the Gmail Services are one of three main classes, along with threads and messages). The idea is that we will process ‘pending’ emails assigned to the ‘newtask’ label, and then switch the thread to the ‘newtaskdone’ label so it won’t get processed a second time. With a call to the getThreads() method of the pending label object, we get a list of threads. We’re assuming that there’s only one email in each thread (task emails are separate and different each time), and so we grab the subject from the first message in each thread to use as the 1-liner task title, and use the addTask_ helper function to insert a new task into the tasklist.

Once this is done we remove the ‘newtask’ label and assign the ‘newtaskdone’ label to the thread.

Finally, we increment the ‘Processed tasks’ counter in the sheet, for a quick indication of how many email-to-task conversions have taken place.

function processPending_(sheet) {

  var label_pending = GmailApp.getUserLabelByName(LABEL_PENDING);
  var label_done = GmailApp.getUserLabelByName(LABEL_DONE);

  // The threads currently assigned to the 'pending' label
  var threads = label_pending.getThreads();

  // Process each one in turn, assuming there's only a single
  // message in each thread
  for (var t in threads) {
    var thread = threads[t];

    // Grab the task data
    var taskTitle = thread.getFirstMessageSubject();

    // Insert the task
    addTask_(taskTitle, TASKLIST);

    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }

  // Increment the processed tasks count
  var processedRange = sheet.getRange("B1");
  processedRange.setValue(processedRange.getValue() + threads.length)
}

This last function, main_taskconverter, is more a matter of personal style rather than necessity – it’s the main function that we will start the whole mechanism off with, and the function that we’ll specify in the trigger so this script will run on a regular basis. We get a reference to the active spreadsheet, set the first sheet to be the active one (it usually is anyway) and call the processPending_ function.

function main_taskconverter() {

  // Get the active spreadsheet and make sure the first
  // sheet is the active one
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.setActiveSheet(ss.getSheets()[0]);

  // Process the pending task emails
  processPending_(sh);
}

And that’s all there is to it!

 

Scheduling Regular Execution

We want this mechanism to run regularly in the background, so that it converts all incoming task emails to tasks without our intervention. So we’ll use a trigger – we can set up a time-driven event trigger so that the script – via the main_taskconverter function, runs every hour.

Current Project's Triggers

 

With a coffee (and biscuit) down, I now have a very slick way of remembering things I have to do. Nice!

Here’s the script in its entirety, with comments.

 

// -----------------------------------------------------
// Globals, contants
// -----------------------------------------------------
TASKLIST = "DJ's list";
LABEL_PENDING = "newtask";
LABEL_DONE = "newtaskdone";

// -----------------------------------------------------
// getTasklistId_(tasklistName)
// Returns the id of the tasklist specified
// Oddly, we should be able to use:
// Tasks.Tasklists.get(tasklistName)
// but it always gives an error "Invalid Value". 
// -----------------------------------------------------
function getTasklistId_(tasklistName) {
  var tasklistsList = Tasks.Tasklists.list();
  var taskLists = tasklistsList.getItems();
  for (tl in taskLists) {
    var title = taskLists[tl].getTitle();
    if (title == tasklistName) {
      return taskLists[tl].getId();
    }
  }
}

// -----------------------------------------------------
// processPending(sheet)
// Process any pending emails and then move them to done
// -----------------------------------------------------
function processPending_(sheet) {

  var label_pending = GmailApp.getUserLabelByName(LABEL_PENDING);
  var label_done = GmailApp.getUserLabelByName(LABEL_DONE);

  // The threads currently assigned to the 'pending' label
  var threads = label_pending.getThreads();

  // Process each one in turn, assuming there's only a single
  // message in each thread
  for (var t in threads) {
    var thread = threads[t];

    // Grab the task data
    var taskTitle = thread.getFirstMessageSubject();
    
    // Insert the task
    addTask_(taskTitle, TASKLIST);

    // Set to 'done' by exchanging labels
    thread.removeLabel(label_pending);
    thread.addLabel(label_done);
  }
 
  // Increment the processed tasks count
  var processedRange = sheet.getRange("B1");
  processedRange.setValue(processedRange.getValue() + threads.length)
}

// -----------------------------------------------------
// addTask_(title, taskListId)
// Create new task and insert into given tasklist
// -----------------------------------------------------
function addTask_(title, tasklistId) {
  var newTask = Tasks.newTask().setTitle(title);
  Tasks.Tasks.insert(newTask, getTasklistId_(tasklistId));
}

// -----------------------------------------------------
// main()
// Starter function; to be scheduled regularly
// -----------------------------------------------------
function main_taskconverter() {
  
  // Get the active spreadsheet and make sure the first
  // sheet is the active one
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.setActiveSheet(ss.getSheets()[0]);

  // Process the pending task emails
  processPending_(sh);
  
}