Monday, March 19, 2018

1008 Apps Script dynamic expandable html table

1008 Apps Script dynamic expandable html table


.

1008 App Script dynamic expandable html table
How To Write Google App Script to display dynamic expandable HTML table

Objective:

1. Use pagination logic.
2. Append additional rows to existing table.

1) Prepare the startup file

Make a copy of the 1007 tutorial file
https://docs.google.com/spreadsheets/d/1BOCKU6HO9delVI6jsGHRO-z5RL2BPKW08Dvv9_tgHU8/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);
          if (n>=0){
   

visit 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)