Skip to content

Instantly share code, notes, and snippets.

@juliuzfan

juliuzfan/changelog-gas-script.js

Created November 28, 2011 08:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save juliuzfan/1399604 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save juliuzfan/1399604 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. juliuzfan created this gist Nov 28, 2011.
    192 changes: 192 additions & 0 deletions changelog-gas-script.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,192 @@
    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);
    };