Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function test_doGet() {
- var e = {};
- e.parameter = {};
- e.parameters = {};
- e.parameters.callback = 'callback';
- e.parameter.url = "";
- e.parameter.sheet = 'Sheet1';
- e.parameter.range = 'A1:H';
- 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);
- Logger.log(sps.getSheets()[0].getName());
- var sheet = sps.getSheetByName(sheetName);
- Logger.log(sheet);
- 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;
- else if ((data[i][j] != "") && (data[i][j] >= 0) && (data[i][j] <= 1)) {
- Logger.log(data[i][j]);
- dt.cols[j].isNumber = false;
- data[i][j] = (data[i][j]*100).toString() + "%";
- }
- 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;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement