Google Apps Script

Integrating with Google APIs - Creating a simple reading list

Here at Google, we have a set of APIs that are built in a common way and exposed to users through our Discovery Service in a machine readable format. The Apps Script team in collaboration with the Google APIs team, has built a convenient way for you to integrate these services with your scripts and apps. This will allow you to build richer apps using Google Apps Script, and find new mashups and integrations that were not possible before. This tutorial will help you get started using these new services by building a fun little script that you can integrate with your daily life.

Often we find an article when perusing the internet that we would like to save for later reading.  You would prefer to manage your reading list in a spreadsheet for the convenience.  You already use Tasks for the excellent Gmail integration and easy to see completion status. Previous experience has shown that whenever you paste URLs in the Tasks UI they are too long and become truncated and unwieldy.  For that reason you want to create an app to synchronize a spreadsheet of URLs with a task list of shortened links to those URLs.  When you complete a task, you also want the completion status shown in both places.

Goal

In this tutorial you will learn how to use Google APIs from Google Apps Script by building a simple reading list application. The application will:

  1. Synchronize URLs stored in a spreadsheet with a Tasks list (accessible from Gmail)
  2. Shorten URLs in order to make them more readable in the Tasks list
  3. When the user marks the task as complete in their Tasks lists, mark the corresponding task as done on the spreadsheet

Time to Complete

Approximately 30 minutes.

Pre-requisites

  1. Familiarity with the Spreadsheet service
  2. Running functions from the script editor
  3. Familiarity with Google APIs access keys

Set-Up

First, create a spreadsheet with columns called URLs, Done, Reading List Name, and Developer Reading. In the first column, URLs, add some links with one link per cell in rows A2, A3...An.

Example spreadsheet with required starting data.

Click Tools > Script editor... to load the script editor and we will start working on our application.

Enabling Google APIs Services

For this application, we'll use the Url Shortener API and the Tasks API.  To use these APIs from scripts, we have to enable them from the Google APIs Services dialog box.  From the Script Editor, click Resources > Advanced Google services....

Screenshot of where to click to open Google APIs Services dialog.

When you open the dialog box, you are prompted to save your project with a name. Let’s call it “Reading List” - Type the name and click OK.

Rename Project dialog box.

After you click OK, the Google APIs Services dialog box will appear. Now enable the two APIs we’re going to use, Tasks and UrlShortener.  The default names for the chosen APIs are fine, so we will not change them. On the Google APIs Services dialog box, flip the toggle switch from off to on.

Screenshot of Google APIs Services dialog box.

We are using APIs that are controlled through the Google APIs Console.  In order for our APIs to work properly we must obtain a developer key.  To get a developer key we first create a project in the APIs console.  Click the Google APIs Console link on the dialog box.  Register your app.  Click the API Access tab and copy the API key from this page to the Google APIs Services dialog.

Screenshot of Google Developers Console and location of API key.

Paste the new API key into the API Key field on the Google APIs Services dialog box.

Screenshot of Google APIs Services dialog box with Tasks and
UrlShortener enabled.

After you have enabled both APIs, created your console project, and copied over the API key, you're ready to start writing your app!

Writing Your App

The first thing we will do is to write the code that creates a reading list from the title in the spreadsheet:

function synchronize() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Get the reading list name from the spreadsheet
  var readingListName = sheet.getRange(1, 4);

  // Create a new Tasks list to hold our reading list
  var listTitle = readingListName.getValue();
  Logger.log("Creating reading list with name: " + listTitle);
  var titleToInsert = Tasks.newTaskList().setTitle(listTitle);
  var readingListObj = Tasks.Tasklists.insert(titleToInsert);

  // Grab the ID assigned to the reading list, we’ll need it later
  readingListId = readingListObj.getId();

  Logger.log("Using reading list: " + readingListId);
}

If you were to run the code now, it would read the reading list title out of the spreadsheet and create a new reading list each time you run the function.  Of course, we want to use the same reading list each time we synchronize, not a new list each time.

Saving Our Work

To avoid creating a new reading list each time we run the synchronize function, we will save the reading list ID as a comment in the spreadsheet.  On each subsequent invocation of the function, we will read back the ID and use it.  We will use this technique to store metadata elsewhere in the application.

function synchronize() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Get the reading list name
  var readingListName = sheet.getRange(1, 4);

  // Read the id of the saved reading list (if any) from the comment
  var readingListId = readingListName.getComment();
  if (readingListId === "") {

    // Create a new Tasks list to hold our reading list
    var listTitle = readingListName.getValue();
    Logger.log("Creating reading list with name: " + listTitle);
    var titleToInsert = Tasks.newTaskList().setTitle(listTitle);
    var readingListObj = Tasks.Tasklists.insert(titleToInsert);

    // Grab the ID assigned to the reading list and save it as a comment
    readingListId = readingListObj.getId();
    readingListName.setComment(readingListId);
  }

  Logger.log("Using reading list: " + readingListId);
}

Synchronizing the URLs

Next, we will iterate through the list of URLs, shortening and inserting the new ones and updating the existing ones.  Append the following code to your existing function:

// Fetch the list of URLs to keep synchronized
var articleUrls = SpreadsheetApp.getActiveSheet().getRange("A2:A");
for (var rowNum = 0; rowNum < articleUrls.getNumRows(); rowNum++) {

  // Limit our range to a single cell containing a URL
  var oneUrlCell = articleUrls.offset(rowNum, 0, 1, 1);
  if (oneUrlCell.getComment() === "") {
    // This is a new URL that needs to be shortened/inserted
    var urlText = oneUrlCell.getValue();
    if (urlText !== "") {
      // Shorten the URL
      Logger.log("Adding task for url: " + urlText);
      var toShorten = UrlShortener.newUrl().setLongUrl(urlText);
      var shortened = UrlShortener.Url.insert(toShorten);

      // Insert the shortened URL into our reading list
      var taskToInsert = Tasks.newTask().setTitle(shortened.getId());
taskToInsert.setNotes(urlText);
      var newTask = Tasks.Tasks.insert(taskToInsert, readingListId);

      // Save the new ID as our comment.
      oneUrlCell.setComment(newTask.getId());
    }
  } else {
    // This URL has already been inserted, update the status
    var existingTask = Tasks.Tasks.get(readingListId, oneUrlCell.getComment());
    if (existingTask.getStatus() === "completed") {
      var absRowNum = oneUrlCell.getRow();
      var completedCell = sheet.getRange(absRowNum, 2);
      completedCell.setValue("Yes");
    }
  }
}

Note: If a task has been deleted after some amount of time the ID will return an error when used to view the task.  If you plan to continue using this code you should modify it to remove deleted tasks from the list, or properly handle a “Not Found” error.

At this point we have completed our code.  The spreadsheet has a few sample URLs that you can test, or you can fill in some of your own.  Try some of the following:

  1. Run the synchronize() function and see how it affects your Tasks list.
  2. Add new URLs and see how they affect the existing list.
  3. Mark some tasks as complete, run synchronize(), and see how the spreadsheet changes.

Conclusion

Congratulations! You have built your first app using Google APIs services.  Feel free to explore the other APIs we have enabled, and stay tuned as we release more APIs in the near future!

Complete Code

function synchronize() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Get the reading list name from the spreadsheet
  var readingListName = sheet.getRange(1, 4);

  // Read the id of the saved reading list (if any) from the comment
  var readingListId = readingListName.getComment();
  if (readingListId === "") {

    // Create a new Tasks list to hold our reading list
    var listTitle = readingListName.getValue();
    Logger.log("Creating reading list with name: " + listTitle);
    var titleToInsert = Tasks.newTaskList().setTitle(listTitle);
    var readingListObj = Tasks.Tasklists.insert(titleToInsert);

    // Grab the ID assigned to the reading list and save it as a comment
    readingListId = readingListObj.getId();
    readingListName.setComment(readingListId);
  }

  Logger.log("Using reading list: " + readingListId);

  // Fetch the list of URLs to keep synchronized
  var articleUrls = SpreadsheetApp.getActiveSheet().getRange("A2:A");
  for (var rowNum = 0; rowNum < articleUrls.getNumRows(); rowNum++) {

    // Limit our range to a single cell containing a URL
    var oneUrlCell = articleUrls.offset(rowNum, 0, 1, 1);
    if (oneUrlCell.getComment() === "") {
      // This is a new URL that needs to be shortened/inserted
      var urlText = oneUrlCell.getValue();
      if (urlText !== "") {
        // Shorten the URL
        Logger.log("Adding task for url: " + urlText);
        var toShorten = UrlShortener.newUrl().setLongUrl(urlText);
        var shortened = UrlShortener.Url.insert(toShorten);

        // Insert the shortened URL into our reading list
        var taskToInsert = Tasks.newTask().setTitle(shortened.getId());
        taskToInsert.setNotes(urlText);
        var newTask = Tasks.Tasks.insert(taskToInsert, readingListId);

        // Save the new ID as our comment.
        oneUrlCell.setComment(newTask.getId());
      }
    } else {
      // This URL has already been inserted, update the status
      var existingTask = Tasks.Tasks.get(readingListId, oneUrlCell.getComment());
      if (existingTask.getStatus() === "completed") {
        var absRowNum = oneUrlCell.getRow();
        var completedCell = sheet.getRange(absRowNum, 2);
        completedCell.setValue("Yes");
      }
    }
  }
}

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.