Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Monday, February 1, 2016

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

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){
           array[rowno].unshift(rowno);
           output.push(array[rowno]);
          }/*if*/    
        }/*else*/
  }/*row*/
  return output;
}/*function findInArray*/
function testFindInArray(){
  var Sheet = SST.getSheetByName('persons');
  var array =Sheet.getRange(2, 1, Sheet.getLastRow()-1, Sheet.getLastColumn()).getValues();  
  found=findInArray(array,[0],"1499115840701");
  if (found.length>0){
      var tid=found[0][1];/*pickup the first item of the first row ie the array row num*/
    }  
  Logger.log(tid);  
}
/* ref: https://github.com/AishwaryT/Google-app-script-crud/blob/master */
/* add record */
function addRecord(recordsname, e) {
  var Sheet = SST.getSheetByName(recordsname);
  try {
    var headers = Sheet.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var rownum = Sheet.getLastRow() + 1; // get next row
    var row = [];
    var d = new Date();
    var tid=d.getTime();
    for (colno in headers) {
      if (headers[colno] == "tid") { // special case if you include a 'tid' column
        row.push(tid);
        } else if (headers[colno] == "timestamp") { // special case if you include a 'timestamp' column
          row.push(d);
          } else { // else use header name to get data
            row.push(e.parameter[headers[colno]]);
            }
      }
    Sheet.getRange(rownum, 1, 1, row.length).setValues([row]);
    return tid.toString();
    } catch (e) {
      // if error return this
      return e;
      }
}
/* test add record */
function testAddRecord(){
  var parameter={action:'addrecord',name:'sally',pid:'7474',age:'44'};
  var e={parameter:parameter};
  Logger.log(addRecord('persons', e));      
}
/* edit record */
function editRecord(recordsname, e) {
  var Sheet = SST.getSheetByName(recordsname);
  try {
    var headers = Sheet.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var tid=e.parameter['tid'];
    var arrorig =Sheet.getRange(2, 1, Sheet.getLastRow()-1).getValues();
    var arrfound=findInArray(arrorig,[0],tid);/*find tid in arrorig*/
    if (arrfound.length>0){
      /*pickup the first item of the first row ie the array row num*/
      /*change from zero-based to one-based and add the headRow*/
      var rownum=Number(arrfound[0][0])+2;
    }
    if (rownum == undefined||0) {
      //row not found
      return -1;
      }
    var row = [];
    var d = new Date();
    // loop through the header columns
    for (i in headers) {
      if (headers[i] == "tid") { // special case if you include a 'tid' column
        row.push(tid);
        } else if (headers[i] == "timestamp") { // special case if you include a 'timestamp' column
          row.push(d);
          } else { // else use header name to get data
            row.push(e.parameter[headers[i]]);
            }
      }
    Sheet.getRange(rownum, 1, 1, row.length).setValues([row]);
    return tid.toString();
    } catch (e) {
      // if error return this
      return e;
      }
}
/* test edit record */
function testEditRecord(){
  var parameter={action:'editrecord',tid:'1499741108111',name:'awani',pid:'7474',age:'43'};
  var e={parameter:parameter};
  Logger.log(editRecord('persons', e));      
}
/* delete record */
function deleteRecord(recordsname, e) {
  var Sheet = SST.getSheetByName(recordsname);
  var SheetSorted = SST.getSheetByName(recordsname+'(sorted)');
  try {
    var headers = Sheet.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var tid=e.parameter['tid'];
    var arrorig =Sheet.getRange(2, 1, Sheet.getLastRow()-1).getValues();
    var arrfound=findInArray(arrorig,[0],tid);/*find tid in arrorig*/
    if (arrfound.length>0){
      /*pickup the first item of the first row ie the array row num*/
      /*change from zero-based to one-based and add the headRow*/
      var rownum=Number(arrfound[0][0])+2;
      var rownumsorted=Number(findInArray(SheetSorted.getRange(2, 1, Sheet.getLastRow()-1).getValues(),[0],tid)[0][0])+2;
    }
    if (rownum == undefined||0) {
      //row not found
      return -1;
      }
    // delete row by rownum
    Sheet.deleteRow(rownum);
    var tidnext=0;
    if(rownumsorted>2){
      tidnext=SheetSorted.getRange(rownumsorted-1, 1,1,1).getValues()[0];
    }
    return tidnext.toString();
    //return rownum;
  } catch (e) {
    // if error return this
    return e;
  }
}
/* test delete record */
function testDeleteRecord(){
  var parameter={action:'deleterecord',tid:'1499730649349'};
  var e={parameter:parameter};
  Logger.log(deleteRecord('persons', e));      
}
/* select record */
function selectRecord(recordsname, e) {
  var Sheet = SST.getSheetByName(recordsname);
  try {
    var headers = Sheet.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var tid=e.parameter['tid'];
    var arrorig =Sheet.getRange(2, 1, Sheet.getLastRow()-1).getValues();
    var arrfound=findInArray(arrorig,[0],tid);/*find tid in arrorig*/
    if (arrfound.length>0){
      /*pickup the first item of the first row ie the array row num*/
      /*change from zero-based to one-based and add the headRow*/
      var rownum=Number(arrfound[0][0])+2;
    }
    if (rownum == undefined||0) {
      //row not found
      return -1;
      }
    var row = {};
    row['_row']=rownum;    
    var values = Sheet.getRange(rownum, 1, 1, Sheet.getLastColumn()).getValues()[0];
    for (i in headers) {
      row[headers[i]] = values[i].toString();
      }
    return row;
    } catch (e) {
      // if error return this
      return e;
      }
}
/* test select record */
function testSelectRecord(){
  var parameter={action:'selectrecord',tid:'1499730564474'};
  var e={parameter:parameter};
  Logger.log(selectRecord('persons', e));      
}
/* get all records */
function selectRecords(recordsname, e) {
  var Sheet = SST.getSheetByName(recordsname);
  try {  
  var headers = Sheet.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
  var rownum = Sheet.getLastRow()-1;
  //Logger.log(e.parameter['tid']);
  //Logger.log(rownum);
  if (rownum == undefined||rownum==1) {
    //row not found
    return -1
  }
  var rows = [];
  var values = Sheet.getRange(2, 1, Sheet.getLastRow(), Sheet.getLastColumn()).getValues();
  for (var i = 0; i < values.length - 1; i++) {
    var row = {};
    row['_row']=i+2;/*change from zero-based to one-based and add the headRow*/    
    for (j in headers) {
      row[headers[j]] = values[i][j];
      }
    rows.push(row);
    }
  return rows;
} catch (e) {
  return e;
  }
}
/* test select records */
function testSelectRecords(){
  var parameter={action:'selectrecords'};
  var e={parameter:parameter};
  Logger.log(selectRecords('persons', e));      
}
/* get all records, paginate*/
function selectRecordsPage(recordsname,e){
  try {
    var Sheet = SST.getSheetByName(recordsname);    
    var SheetSorted= SST.getSheetByName(recordsname+'(sorted)');    
    var itemcount=Sheet.getLastRow()-1; /*excluding header row*/
    var rowcount=parseInt(e.parameter.rowcount)||10; /*default is 10*/
   
    var rownum=0;
    var tid=e.parameter['tid']||0;
    if (tid==0){ /*tid=0 means starts from beginning*/
      tid=SheetSorted.getRange(1, 1,1,1).getValues()[0];
      rownum=1;
    }
    else
    {
      var array =SheetSorted.getRange(2, 1, Sheet.getLastRow()-1).getValues();
      var found=findInArray(array,[0],tid);/*found is array*/
      if (found.length>0){
        /*pickup the first item of the first row ie the array row num*/
        /*change from zero-based to one-based*/
        rownum=Number(found[0][0])+2;
      }
    }/*else*/
    if (rownum == undefined||0) {
      //row not found
      return -1;
      }
    var itemcurrent=parseInt(e.parameter.itemcurrent)||rownum; /*default is 1*/
    if (itemcurrent>itemcount){return -1;}
   
        var itemstart=itemcurrent+1; /*itemstart is the first item of a new page*/
        var itemstop=itemcurrent + rowcount; /*itemstop*/
        if (itemstop>itemcount+1){itemstop=itemcount+1;}
        var headRow = e.parameter.header_row || 1;
        var headers = SheetSorted.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
        var rows = [];
        var values = SheetSorted.getRange(itemstart, 1, itemstop-(itemstart-1), Sheet.getLastColumn()).getValues();
     
        for (var i = 0; i < values.length; i++) {
            var row = {};
            row['_row']=i+1;/*change from zero-based to one-based and add the headRow*/              
            for (j in headers) {
                row[headers[j]] = values[i][j];
            }
            rows.push(row);
        }
     
      result={
        itemcount:itemcount,
        rowcount:rowcount,
        itemcurrent:itemcurrent,
        itemstart:itemstart,
        itemstop:itemstop,
        length:values.length,
        rows:rows
      };
     
      return result
    } catch (e) {
        return e
    }}
/* test select records */
function testSelectRecordsPage(){
  var parameter={action:'selectrecordspage',tid:'0',rowcount:3};
  var e={parameter:parameter};
  Logger.log(JSON.stringify(selectRecordsPage('persons', e)));      
}

3) Edit Html Code file

Eg user.html

<!DOCTYPE html>
<html>
<head>
        <base target="_top">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body>
        <form id="formAddEditDeletePerson" style="display:none;background-color:WhiteSmoke;padding:4px;">
                <input type="hidden" name="action" value="">
                <input type="hidden" name="tid" value="">
                <h3>...</h3>
                <p>
                        <label for="name">Name:</label>
                        <input name="name" type="text" value="" />
                </p>
                <p>
                        <label for="pid">Pid:</label>
                        <input name="pid" type="text" value="" />
                </p>
                <p>
                        <label for="age">Age:</label>
                        <input name="age" type="text" value="" />
                </p>
                <p id="formAddEditDeletePersonMessage" style="background-color:yellow;"></p>
                <input name="submit" type="submit" value="ok" />
                <button type="button" onclick="cancelForm(this.form)" value="cancel">cancel</button>
                <hr/>
        </form>
        <form id="formSelectPersons" style="display:block;padding:4px;">
                <h3>Persons Table</h3>
                <button type="button" onclick="prepareAddPerson()" value="cancel">add person</button>
        <br/>
        <br/>
                <!--<input type="submit" value="refresh table" />-->
                <input type="hidden" name="action" value="selectpersons">
                <table id="tblPerson" border=1>
                </table>
                <!--<input type="submit" value="refresh table" />-->
                <p id="formSelectPersonsMessage" style="background-color:yellow;"></p>        
    </form>
        <button type="button" onclick="requestSelectPersonsPage()" value="cancel">more</button>
        <input name="rowcount" type="text" value="" size="4"/>&nbsp;rows
<hr/>
        <script>
                function formToJson(form){var obj={};var elements=form.querySelectorAll("input, select, textarea");for(var i=0;i<elements.length;++i){var element=elements[i];var name=element.name;var value=element.value;if(name){obj[name]=value;}} return obj;}
                function jsonToUrlParameters(json){return Object.keys(json).map((i)=>i+'='+json[i]).join('&');}
                
                /* Using Templated HTML and getting urldata from Code.gs */
        var url =<?= urldata ?> ;
                requestSelectPersonsPage();
                           
                var formSelectPersons = document.getElementById( "formSelectPersons" ).addEventListener( "submit", function( e ) {
                   e.preventDefault(); /*prevent default submit */
                   requestSelectPersons(formToJson(this));
                }, false);
                
                var formAddEditDeletePerson = document.getElementById( "formAddEditDeletePerson" );
                   formAddEditDeletePerson.addEventListener( "submit", function( e ) {
                   e.preventDefault(); /*prevent default submit */
                   requestAddEditDeletePerson(formToJson(this))
                }, false);
        function refreshPage(){
           var tblPerson=document.getElementById("tblPerson");
           tblPerson.innerHTML = "";
           requestSelectPersonsPage();          
        }  
                function requestSelectPersonsPage(){
           var tblPerson=document.getElementById("tblPerson");
           var tid=0;  
           if (!(document.getElementById("thead"))){
              thead=document.createElement('thead');
              thead.id="thead";
                      tr = document.createElement('tr');
                      tr.innerHTML="<th>----</th><th>------</th><th>Name</th><th>Pid</th><th>Age</th>";
                      thead.append(tr);
              tblPerson.append(thead);
           }
           if (!(document.getElementById("tbody"))){
              tbody=document.createElement('tbody');
              tbody.id="tbody";
              tblPerson.append(tbody);
           }
           else{
                      var tbody=document.getElementById("tbody");          
               var x = document.getElementById("tblPerson").rows.length;
               tid = tblPerson.rows[x-1].id;              
               console.log(tid);
           }
                   document.getElementById("formAddEditDeletePerson").style.display = "none";
           
                   var elMessage= document.getElementById("formSelectPersonsMessage");  
           elMessage.innerHTML= "<i class='fa fa-spinner fa-spin' style='font-size:24px'></i>"
           
           var formjson={'action':'selectpersonspage',rowcount:5,tid:tid};
                   var urlparameters=url + "?" +jsonToUrlParameters(formjson);
                   //console.log(urlparameters);
                   
           var xhr = new XMLHttpRequest();
                   xhr.open('GET', urlparameters, true);
                   
           xhr.onload = function () {
                      elMessage.innerHTML = "";
              if(xhr.responseText==-1){return true;}
                      objresponse=JSON.parse(xhr.responseText);
              //console.log(objresponse);
                      var arrperson=objresponse.rows;
                      for (var i = 0; i < arrperson.length; i++) {
                         tr = document.createElement('tr');
                         tr.id = arrperson[i].tid;
                         tr.innerHTML="<td><button type='button' onclick='prepareEditPerson("+ arrperson[i].tid +")' >edit</button></td><td>"
                         + "<button type='button'  onclick='prepareDeletePerson("+ arrperson[i].tid +")' >delete</button></td><td>"
                         + arrperson[i].name + "</td><td>"
                         + arrperson[i].pid + "</td><td>"
                         + arrperson[i].age + "</td></tr>";
                         tbody.append(tr);
                      }                  
           };
                   xhr.send();
                }
                function requestAddEditDeletePerson(formjson){
                   var elMessage= document.getElementById("formAddEditDeletePersonMessage");  
                   //elMessage.innerHTML = "sending data to server...";
           elMessage.innerHTML= "<i class='fa fa-spinner fa-spin' style='font-size:24px'></i>"
                
                   var sendparameters=jsonToUrlParameters(formjson);
                   //console.log(sendparameters);
                   var xhr = new XMLHttpRequest();
                   xhr.open("POST", url, true);
                   //Send the proper header information along with the request
                   xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                             
                   xhr.onreadystatechange = function() {//Call a function when the state changes.
                      if(xhr.readyState == XMLHttpRequest.DONE && xhr.status == 200) {
                         elMessage.innerHTML = "";      
                         //console.log(xhr.responseText);
                         document.getElementById("formAddEditDeletePerson").reset();
                         }
                 refreshPage();
                      }
                   xhr.send(sendparameters);
                }
                
                function prepareAddPerson(){
                
                   var formAddEditDeletePerson = document.getElementById( "formAddEditDeletePerson" );
                   formAddEditDeletePerson.style.display = "block";
                   formAddEditDeletePerson.getElementsByTagName("H3")[0].innerHTML="Add";
                   formAddEditDeletePerson.elements["tid"].value=null;
                   formAddEditDeletePerson.elements["action"].value="addperson";
                   formAddEditDeletePerson.elements["action"].disabled=false;  
                   formAddEditDeletePerson.elements["name"].value=null;
                   formAddEditDeletePerson.elements["name"].disabled=false;  
           formAddEditDeletePerson.elements["name"].focus();          
                   formAddEditDeletePerson.elements["pid"].value=null;
                   formAddEditDeletePerson.elements["pid"].disabled=false;      
                   formAddEditDeletePerson.elements["age"].value=null;
                   formAddEditDeletePerson.elements["age"].disabled=false;  
                
                   formAddEditDeletePerson.scrollIntoView();
                   }
                
                function prepareEditPerson(tid){
                   var Row = document.getElementById(tid);
                   var Cells = Row.getElementsByTagName("td");
                   var formAddEditDeletePerson = document.getElementById( "formAddEditDeletePerson" );
                   formAddEditDeletePerson.style.display = "block";
                   formAddEditDeletePerson.getElementsByTagName("H3")[0].innerHTML="Edit";
                   formAddEditDeletePerson.elements["tid"].value=tid;
                   formAddEditDeletePerson.elements["action"].value="editperson";
                   formAddEditDeletePerson.elements["action"].disabled=false;  
                   formAddEditDeletePerson.elements["name"].value=Cells[2].innerText;          
                   formAddEditDeletePerson.elements["name"].disabled=false;  
           formAddEditDeletePerson.elements["name"].focus();          
                   formAddEditDeletePerson.elements["pid"].value=Cells[3].innerText;
                   formAddEditDeletePerson.elements["pid"].disabled=false;      
                   formAddEditDeletePerson.elements["age"].value=Cells[4].innerText;
                   formAddEditDeletePerson.elements["age"].disabled=false;  
                
                   formAddEditDeletePerson.scrollIntoView();
                   }
                
                function prepareDeletePerson(tid){
                   var Row = document.getElementById(tid);
                   var Cells = Row.getElementsByTagName("td");
                   var formEditDeletePerson = document.getElementById( "formAddEditDeletePerson" );
                   formAddEditDeletePerson.style.display = "block";
                   formAddEditDeletePerson.getElementsByTagName("H3")[0].innerHTML="Delete";
                   formAddEditDeletePerson.elements["tid"].value=tid;
                   formAddEditDeletePerson.elements["action"].value="deleteperson";
                   formAddEditDeletePerson.elements["action"].disabled=true;
                   formAddEditDeletePerson.elements["name"].value=Cells[2].innerText;
                   formAddEditDeletePerson.elements["name"].disabled=true;
                   formAddEditDeletePerson.elements["pid"].value=Cells[3].innerText;
                   formAddEditDeletePerson.elements["pid"].disabled=true;  
                   formAddEditDeletePerson.elements["age"].value=Cells[4].innerText;
                   formAddEditDeletePerson.elements["age"].disabled=true;  
           formAddEditDeletePerson.elements["submit"].focus();
           
                   formAddEditDeletePerson.scrollIntoView();
                }
                
                function cancelForm(form){
                   form.reset();
                   form.style.display = "none";
                }
        </script>
</body>
</html>

4) Deploy and Test

Copy:


.

No comments:

Post a Comment