GAS Script to setup a change log Google Docs spreadsheet

changelog-gas-script.js

function onInstall()
{
  /*
   * Setup the Change log spreadsheet
   * (c) 2011,2012 Julio Flores <http://juliorfa.me>
   * MIT license
   *
   * with attitude & love by @JulioRFA (Web Designer & Developer)
   */
  var auto_increment_id_formula = '=ARRAYFORMULA( IF( B2:B = ""; ""; ROW(B2:B) - 1 ) )';
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  //Column Header Font Styles
  var headers = sheet.getRange("A1:C1");
  headers.setFontSize(14);
  headers.setFontWeight("bold");
  //Set Each Column Header
  //Column A1
  var A1 = sheet.getRange(1,1,1,1);
  A1.setValue("id");
  //Column B
  var B1 = sheet.getRange(1,2,1,1);
  B1.setValue("Changelog");
  //Column C
  var C1 = sheet.getRange(1,3,1,1);
  C1.setValue("Date");
  
  //Set the Auto Increment Id Formula
  A1.offset(1,0,1,1).setFormula(auto_increment_id_formula);
  //Ready! Go!
  Browser.msgBox("Changelog setup complete!. Adjust the column widths as desired. Then just edit <Changelog> column to add entries");
}

function onEdit(e)
{
  /**
   * Insert automatically a date in a column-cell if another column-cell is added
   * If Col A is created then add a date in Col B
   */
  var editedColumn = 2; //If column B is modified
  var dateColumn   = 3; //Column to set the date
  var overwrite    = false; // overwrite dates?
  /*REFERENCE:*/
  /*http://www.google.com/support/forum/p/apps-script/thread?tid=627e3761c53e3ac3&hl=en*/
  
  // to reverse the order of the cell writing you have to modify this code here:
  var rowIndex = e.source.getActiveRange().getRowIndex();
  var colIndex = e.source.getActiveRange().getColumnIndex();
  
  // don't do anything if it wasn't column <editedColumn> that was modified
  if (colIndex == editedColumn)
  {
    var C = e.source.getActiveSheet().getRange(rowIndex ,dateColumn,1,1);
   
    //sobreescribir? result = overwrite + empty_cell
    if ( overwrite || ( C.getValues() == "" ) )
    {
      var now = new Date();
      var today = dateFormat(now, "dddd, mmm d, yyyy, h:MM TT");
      C.setValue( today );
    }
    
  }
  
}
/* http://blog.stevenlevithan.com/archives/date-time-format */
/*
 * Date Format 1.2.3
 * (c) 2007-2009 Steven Levithan <stevenlevithan.com>
 * MIT license
 *
 * Includes enhancements by Scott Trenda <scott.trenda.net>
 * and Kris Kowal <cixar.com/~kris.kowal/>
 *
 * Accepts a date, a mask, or a date and a mask.
 * Returns a formatted version of the given date.
 * The date defaults to the current date/time.
 * The mask defaults to dateFormat.masks.default.
 */

var dateFormat = function () {
	var	token = /d{1,4}|m{1,4}|yy(?:yy)?|([HhMsTt])\1?|[LloSZ]|"[^"]*"|'[^']*'/g,
		timezone = /\b(?:[PMCEA][SDP]T|(?:Pacific|Mountain|Central|Eastern|Atlantic) (?:Standard|Daylight|Prevailing) Time|(?:GMT|UTC)(?:[-+]\d{4})?)\b/g,
		timezoneClip = /[^-+\dA-Z]/g,
		pad = function (val, len) {
			val = String(val);
			len = len || 2;
			while (val.length < len) val = "0" + val;
			return val;
		};

	// Regexes and supporting functions are cached through closure
	return function (date, mask, utc) {
		var dF = dateFormat;

		// You can't provide utc if you skip other args (use the "UTC:" mask prefix)
		if (arguments.length == 1 && Object.prototype.toString.call(date) == "[object String]" && !/\d/.test(date)) {
			mask = date;
			date = undefined;
		}

		// Passing date through Date applies Date.parse, if necessary
		date = date ? new Date(date) : new Date;
		if (isNaN(date)) throw SyntaxError("invalid date");

		mask = String(dF.masks[mask] || mask || dF.masks["default"]);

		// Allow setting the utc argument via the mask
		if (mask.slice(0, 4) == "UTC:") {
			mask = mask.slice(4);
			utc = true;
		}

		var	_ = utc ? "getUTC" : "get",
			d = date[_ + "Date"](),
			D = date[_ + "Day"](),
			m = date[_ + "Month"](),
			y = date[_ + "FullYear"](),
			H = date[_ + "Hours"](),
			M = date[_ + "Minutes"](),
			s = date[_ + "Seconds"](),
			L = date[_ + "Milliseconds"](),
			o = utc ? 0 : date.getTimezoneOffset(),
			flags = {
				d:    d,
				dd:   pad(d),
				ddd:  dF.i18n.dayNames[D],
				dddd: dF.i18n.dayNames[D + 7],
				m:    m + 1,
				mm:   pad(m + 1),
				mmm:  dF.i18n.monthNames[m],
				mmmm: dF.i18n.monthNames[m + 12],
				yy:   String(y).slice(2),
				yyyy: y,
				h:    H % 12 || 12,
				hh:   pad(H % 12 || 12),
				H:    H,
				HH:   pad(H),
				M:    M,
				MM:   pad(M),
				s:    s,
				ss:   pad(s),
				l:    pad(L, 3),
				L:    pad(L > 99 ? Math.round(L / 10) : L),
				t:    H < 12 ? "a"  : "p",
				tt:   H < 12 ? "am" : "pm",
				T:    H < 12 ? "A"  : "P",
				TT:   H < 12 ? "AM" : "PM",
				Z:    utc ? "UTC" : (String(date).match(timezone) || [""]).pop().replace(timezoneClip, ""),
				o:    (o > 0 ? "-" : "+") + pad(Math.floor(Math.abs(o) / 60) * 100 + Math.abs(o) % 60, 4),
				S:    ["th", "st", "nd", "rd"][d % 10 > 3 ? 0 : (d % 100 - d % 10 != 10) * d % 10]
			};

		return mask.replace(token, function ($0) {
			return $0 in flags ? flags[$0] : $0.slice(1, $0.length - 1);
		});
	};
}();

// Some common format strings
dateFormat.masks = {
	"default":      "ddd mmm dd yyyy HH:MM:ss",
	shortDate:      "m/d/yy",
	mediumDate:     "mmm d, yyyy",
	longDate:       "mmmm d, yyyy",
	fullDate:       "dddd, mmmm d, yyyy",
	shortTime:      "h:MM TT",
	mediumTime:     "h:MM:ss TT",
	longTime:       "h:MM:ss TT Z",
	isoDate:        "yyyy-mm-dd",
	isoTime:        "HH:MM:ss",
	isoDateTime:    "yyyy-mm-dd'T'HH:MM:ss",
	isoUtcDateTime: "UTC:yyyy-mm-dd'T'HH:MM:ss'Z'"
};

// Internationalization strings
dateFormat.i18n = {
	dayNames: [
		"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat",
		"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"
	],
	monthNames: [
		"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
		"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
	]
};

// For convenience...
Date.prototype.format = function (mask, utc) {
	return dateFormat(this, mask, utc);
};