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){
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"/> 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:
https://docs.google.com/spreadsheets/d/1EiLthgQHQz4tMti9j4VZxN7yPdqa6q7N5ZHBDWzr08o/edit?usp=sharing
Hi,Can you help me?
ReplyDeleteI find https://programming-steps.blogspot.com/2017/04/103-apps-script-google-sheet-add-record.html and resolve to test.I can't understand why the script not register special character like ã, and others.If i input "João" in html front end the script return "Joo". But if do the same in testAddRecord() everything is all right