awesometableProxyFormatDates.gs

// Just a regular timezone string
var TMZ = 'GMT+8';   // 'GMT-10', etc etc

// Refer to here to change for the exact formatting http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
// Single quotes around the %s are for the ordinal, like "3rd" and "4th".
// The single quotes are required, avoids interpretation and are required for the call to formatString
// The '%s' can be deleted as well, without ill effects
var FORMATSTRING = "EE MMM d'%s' yyyy @ hh:mm a";   // Tue Apr 26th 2016 @ 08:43 AM

if (!Date.prototype.formatted) {
  Date.prototype.formatted = function(formatString, tmz) {
      var d = this.getDate();  // day of the month
      var ordinal = null;
      if(d>3 && d<21) ordinal = 'th';  // from 4 to 20, including teens
      if (!ordinal) {
        switch (d % 10) {
            case 1:  ordinal = "st";
            case 2:  ordinal = "nd";
            case 3:  ordinal = "rd";
            default: ordinal = "th";
            }
      }
      var ret = Utilities.formatDate(this, tmz, formatString);
      return Utilities.formatString(ret, ordinal);
  };
};

function test_date() {
  var now = new Date();
  var dateFormatted = now.formatted(FORMATSTRING, TMZ);
  Logger.log(dateFormatted);
}

function test_doGet() {
  var e = {};
  e.parameter = {};
  e.parameters = {};
  e.parameters.callback = 'callback';
  e.parameter.url = "";
  e.parameter.sheet = '';
  e.parameter.range = '';
  Logger.log(doGet(e).getContent());
}


function doGet(e) {
  var currentUser = Session.getActiveUser().getEmail();
  var ssUrl =  e.parameter.url;
  var sheetName = e.parameter.sheet;
  var a1Notation = e.parameter.range;
  var sps = SpreadsheetApp.openByUrl(ssUrl);
  var sheet = sps.getSheetByName(sheetName);
  var range = sheet.getRange(a1Notation);
  var data = range.getValues();
  var dt = {cols:[], rows:[]};
  var permissionsCol = null;
  var firstCol = range.getColumn();
  for(var i = 0; i < data[0].length; i++) {
    if(data[1][i].indexOf('Permissions') != -1) permissionsCol = i;
    dt.cols.push({id:numToA(firstCol+i), label:data[0][i] + ' ' + data[1][i].replace('Permissions', ''), type: 'string', isNumber:true, isDate:true, isEmpty:true});
  }
  for(var i = 2; i < data.length; i++) {
    if(permissionsCol == null || currentUser != '' && data[i][permissionsCol].indexOf(currentUser) != -1) {
      var row = [];
      for(var j = 0; j < data[i].length; j++) {
        if(isNaN(data[i][j])) dt.cols[j].isNumber = false;
        if(data[i][j] != '') dt.cols[j].isEmpty = false;
        if(data[i][j] instanceof Date == false) dt.cols[j].isDate = false;
        else if(data[i][j].getFullYear() == 1899) {
          dt.cols[j].isDate = false;
          data[i][j] = data[i][j].getHours()+':'+(data[i][j].getMinutes()<10?'0':'')+data[i][j].getMinutes();
        }
        else { 
          data[i][j] = data[i][j].formatted(FORMATSTRING, TMZ);  // is now a string
          dt.cols[j].isDate = false;
        }
        row.push({v:data[i][j]});
      }
      dt.rows.push({c:row});
    }
  }
  for(var i = 0; i < data[0].length; i++) {
    if(dt.cols[i].isEmpty) dt.cols[i].type = 'string';
    else if(dt.cols[i].isDate) dt.cols[i].type = 'string';
    else if(dt.cols[i].isNumber) dt.cols[i].type = 'number';
  }
  if ('templateSheet' in e.parameter && e.parameter.templateSheet.toLowerCase().indexOf('template') != -1){
    var templateSheet = sps.getSheetByName(e.parameter.templateSheet);
    var templateRange = templateSheet.getRange(e.parameter.templateRange);
    var templateData = templateRange.getValues();
    var tp = {cols:[], rows:[]};
    for(var i = 0; i < templateData[0].length; i++) tp.cols.push({id:i, label:templateData[0][i], type: 'string'});
    for(var i = 0; i < templateData.length; i++) {
      var row = [];
      for(var j = 0; j < templateData[i].length; j++) row.push({v:templateData[i][j]});
      tp.rows.push({c:row});
    }
    var output = e.parameters.callback + '(' + JSON.stringify({dataTable: dt,template: tp}) + ')';
  }else{
    var output = e.parameters.callback + '(' + JSON.stringify({dataTable: dt}) + ')';
  }
  
  return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function numToA(num){
  var a = '',modulo = 0;
  for (var i = 0; i < 6; i++){
    modulo = num % 26;
    if(modulo == 0) {a = 'Z' + a;num = num / 26 - 1;}
    else{a = String.fromCharCode(64 + modulo) + a;num = (num - modulo) / 26;}
    if (num <= 0) break;}
  return a;
}