Just another Ubuntu weblog

Posts Tagged ‘snippets

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:


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

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 , , , ,

Wading upstream

with 3 comments

So I want to start making an effort to share more of the little tips, tricks, and scripts that I use in the course of Ubuntu development. Hopefully someone will find this stuff useful. On the other hand, I also hope someone will come along, look at what I use, and point out just how wrong it is and show me something simpler.

Potomac Wayside Falls Upstream

To start off with, here’s a function from my ~/.bashrc file. Gmail has an unfortunate insistence on forcing line breaks. This can be an issue when working with the Debian BTS as you need to interact with the control server through one line commands sent via email. The place where this is always the most painful for me has been in marking bugs as forwarded upstream. So I figured I’d make my life easier by just doing it on the command line:

function bts-forward () {
    if [[ "$1" == ${1//[^0-9]/} && "$2" == http* ]]; then
        echo "forwarded $1 $2" | \
        sendmail -f"$DEBEMAIL";
        echo "Usage: bts-forward DEBIAN_BUG UPSTREAM_URL"

Written by andrewsomething

November 12, 2011 at 4:08 pm

Posted in Ubuntu

Tagged with , , ,