Tuesday, April 24, 2018

1007 Apps Script Templated HTML Sort Paginate

1007 Apps Script Templated HTML Sort Paginate



.

1007 Apps Script Templated HTML Sort Paginate
How to write Google App Script to provide HTML Template with sorted and paginated data

Objective:

1. Use HTML Template to share server script code with client script code (getting script url within Code.gs and sharing it with user.html)
2. Use Google Sheet sort formula to sort sheet data in ascending order.
3. Write pagination logic in Code.gs

1) Prepare the startup file

Make a copy of the 1006 tutorial file
https://docs.google.com/spreadsheets/d/11yy72GQTnbq765PkSdvBDBPnjtE6hRebGazdty9W5rs/edit?usp=sharing

2) Edit Script Code file

Eg Code.gs
/*global var*/
var SCPID=ScriptApp.getScriptId(); /*script id*/
var SST=SpreadsheetApp.getActiveSpreadsheet(); /*active spreadsheet */
var SSTID=SpreadsheetApp.getActiveSpreadsheet().getId(); /*spreadsheet id*/
var SSTFILE=DriveApp.getFileById(SSTID); /*get file*/
var Folders = SSTFILE.getParents(); /*get parent collection*/
 while (Folders.hasNext()) {
   var Folder = Folders.next();
   Logger.log(Folder.getName());
   var FOLID = Folder.getId(); /*get id for first parent item*/
 }
/*log the global var*/
function logGlobalVar(){
  Logger.log("script id:"+SCPID);
  Logger.log("sheet id:"+SSTID);
  Logger.log("folder id:"+FOLID);
  Logger.log("SST:"+SST.getName());  
}
/* web request listeners */
function doGet(e) {return handleResponse(e);}
function doPost(e) {return handleResponse(e);}
/* handle action request */
function handleResponse(e) {
 var lock = LockService.getPublicLock();
 lock.waitLock(30000); // wait 30 seconds before conceding defeat.
  try {
    var action = e.parameter.action;
    if (action == app) {
      return getApp(e);
    }else if (action == addperson) {
      return manageRecords("addperson",e); //records command,parameter
    }else if (action == editperson) {
      return manageRecords("editperson",e);
    }else if (action == deleteperson) {
      return manageRecords("deleteperson",e);
    }else if (action == selectperson) {
      return manageRecords("selectperson",e);
    }else if (action == selectpersons) {
      return manageRecords("selectpersons",e);
    }else if (action == selectpersonspage) {
      return manageRecords("selectpersonspage",e);
    }
  }
 catch (e) {/*if error return this*/return ContentService.createTextOutput(JSON.stringify({"result": "error","error": e})).setMimeType(ContentService.MimeType.JSON);}
 finally { /*release lock*/ lock.releaseLock();}
}
/* get app page */
function getApp(e){
  var CurrentUser=Session.getActiveUser().getEmail();  
  var sheet = SST.getSheetByName(users);
  /*start from row 2 and column3 until last row-1*/
  var arruser = sheet.getRange(2,3,sheet.getLastRow()-1).getValues();
  var ValidUser=findInArray(arruser,[0],CurrentUser);
  //if object is not empty open App, else open NoApp
  if (ValidUser.length>0) {
    htmlfile=user;
  }else {
    htmlfile=guest;
  }
    //var output = HtmlService.createHtmlOutputFromFile(htmlfile);
    //output setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
    //return output;

  var page = HtmlService.createTemplateFromFile(htmlfile);
  page.urldata=ScriptApp.getService().getUrl();
  return page.evaluate();
}
/* handle records management */
function manageRecords(command,e){
  var output;  
  switch (command){
    case "addperson":
      output=addRecord("persons",e);
      break;
    case "editperson":
      output=editRecord("persons",e);
      break;
    case "deleteperson":
      output=deleteRecord("persons",e);
      break;
    case "selectperson":
      output=selectRecord("persons",e);
      break;      
    case "selectpersons":
      output=selectRecords("persons",e);
      break;      
    case "selectpersonspage":
      output=selectRecordsPage("persons",e);
      break;            
  }
  return ContentService
  .createTextOutput(JSON.stringify(output))
  .setMimeType(ContentService.MimeType.JSON);
}
/* findInArray(array,colno,searchvalue){} */
/* find a string in array row and columns */
/* if colnos is null then concat all cols */
function findInArray(array,colnos,searchvalue){
  output=[];
  for (var rowno in array){
      if (colnos==null){ /*concat cols*/
        //Logger.log(array[rowno]);
        var strtest=JSON.stringify(array[rowno]);
        var n = strtest.search(searchvalue);
        if (n>=0){
          array[rowno].unshift(rowno);
          output.push(array[rowno]);
        }
      }/*if (colnos==null)*/
        else{
          var strtest=;
          for(var colno in colnos){
            strtest=strtest.concat((strtest==?:,),array[rowno][colnos[colno]]);
          }
          //Logger.log(strtest);          
          var n = strtest.search(searchvalue);
link download
Posted by at

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)