andrewsomething@ubuntu:~$

Just another Ubuntu weblog

Automation with Google Apps Script

with one comment

I maintain a GCal of the Ubuntu release schedule, and I just updated it to contain the proposed schedule for the Q-series. As you can imagine, adding all those events by hand can be annoying. Luckily you can use Google App Script, which is more or less JavaScript for the Google Cloud, to automate tasks like this.

I could have probably come up with something to automate the entire process start to finish, scrapping the wiki every few days and pushing out updates. But this isn’t something that I have to do all that often, so I just wanted to write something quick and simple. Here’s what I ended up with:


var EVENT_ADDED = "EVENT_ADDED";

function sheet2cal() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.openByName("Ubuntu Release Schedule");
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var tstart = row[0]; 
    var title = row[1];
    var desc = row[2];
    var tstop = "";
    var eventAdded = row[4];
    if (eventAdded != EVENT_ADDED) {  // Prevents sending duplicates
      cal.createAllDayEvent(title, tstart, {description:desc});
      sheet.getRange(startRow + i, 5).setValue(EVENT_ADDED);
      SpreadsheetApp.flush();
    }
 }
}

This iterates through a spreadsheet where the first column is the event’s date, the second one is the title, and the third is the description. It also checks a fourth column to make sure the event hasn’t already been added, marking events added as it goes. It is closely based on the example of how to send emails from a spreadsheet.

To add a script like this, go to Tools > Script editor… in your spreadsheet. This will open an IDE where you can write, run, and debug your script. If you want, you can add a custom menu on your spreadsheet’s tool bar to trigger the script with something like:


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Add to calendar", functionName: "sheet2cal"} ];
  ss.addMenu("Scripts", menuEntries);
}

You can also set scripts that will be triggered at specific time intervals, communicate with other services, and do things like parse JSON. This opens up a lot of possibilities.

For instance, I’m on a team that is using Trello for internal organization and task tracking. Kevin Pelgrims has a great example of integrating Trello and Google Docs to track project progress over time that I’ve started using.

Written by andrewsomething

March 11, 2012 at 7:56 pm

Posted in Ubuntu

Tagged with , , , ,

One Response

Subscribe to comments with RSS.

  1. ok, let me try this 🙂
    thank you

    arif

    April 3, 2012 at 1:04 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: