Change time zone for column of time data, in Google Spreadsheets

This short function converts a column of time data from one time zone to another.

function convertTimeZone(sheet, timezone, firstRowPosition, timeCol) {
  /***
   * Converts a column of time data from one time zone to another.
   *
   * Arguments:
   * sheet -- worksheet with data
   * timezone -- string containing Olson ID (both main & alternates seem to work)
   * firstRowPosition -- number (starting at 1) of first row of data
   * timeCol -- number (starting at 1) of the column containing time data
   ***/
  var timeData = sheet.getRange(firstRowPosition, timeCol, sheet.getLastRow()-1, 1).getValues();
  var time = Array(timeData.length);
  for (i in timeData){
    // Time Zone List: http://unicode.org/repos/cldr/trunk/docs/design/formatting/zone_log.html
    var formattedTime = Utilities.formatDate(new Date(timeData[i]), timezone, "EEE, d MMM yyyy HH:mm:ss z");
    // This workaround is needed because otherwise time is a 1-D array, and setValues doesn't like 1-D arrays!
    time[i] = new Array(0);
    time[i].push(formattedTime);
  }
    sheet.getRange(firstRowPosition, timestampCol, time.length, 1).setValues(time); 
}

The workhorse method here is of course, Utilities.formatDate(date, timeZone, format), which actually turned out to be somewhat frustrating until I found that Olson time zone IDs seemed to work relatively consistently. I was particularly concerned about daylight time saving adjustment, but it seems like this method automatically takes care of it, if the right ID is used.

More frustrating actually, was the discovery that Range.setValues(values) won’t take 1-D arrays. Trying to give it a 1-D array causes an “Error: Cannot Convert To .” which is very unhelpful. Here, a workaround was used to convert the 1-D array to a 2-D one.

Note that Google Spreadsheets have three default time zone sources [1], all of which can be used to set the timezone argument above:

1. the spreadsheet’s time zone at creation (which I assume is derived from the browser);
2. the browser’s current time zone (accessed by Session.getTimeZone()), and;
3. the spreadsheet’s time zone setting (accessed by SpreadsheetApp.getSpreadsheetTimeZone()).

Follow-up Post (05 Sept 2012): Change time zone for date/time data, in Google Spreadsheets

References:

1. http://googleappsscript.blogspot.com/2011/03/demystifying-time-zones-in-apps-script_21.html