proxyWebApp.gs

/*
There is a bug in the proxy provided by awesometables  
In a google domain, email addresses should case insensitive
*/

function doGet(e) {
  var currentUser = Session.getActiveUser().getEmail().toLowerCase();
  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]).toLowerCase().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] = "Date("+data[i][j].getTime()+")";
        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 = 'datetime';
    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;
}