.
1006 Interactive Data Table
How to write Google App Script to provide interactive data table with XMLHttpRequest
Objective:
The data output is similar to Tutorial 1005. No changes to code.gs.
This tutorial focus on the HTML improvement:
1. To provide better user-experience (UX)
2. To use the same form(code) for Add/Edit/Delete task (to promote code reuse)
CAUTION: When the user clicks the buttons, some of the codes will run asynchronously. If the user subsequently clicks the buttons without waiting for the current asynchronous task to accomplish, the app may not behave as expected.
1) Prepare the startup file
Make a copy of the 1005 tutorial file
https://docs.google.com/spreadsheets/d/1tTplyZ2bxXde2NiejD3VAL6LBHRc1qvqIqMgzCWdLtQ/edit?usp=sharing
2) Edit the user page
Eg user.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</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 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>
<input type="submit" value="refresh table" />
<p id="formSelectPersonsMessage" style="background-color:yellow;"></p>
<input type="hidden" name="action" value="selectpersons">
<table id="tblPerson" border=1>
<thead id="thead"></thead>
<tbody id="tbody"></tbody>
</table>
<br/>
<button type="button" onclick="prepareAddPerson()" value="cancel">add person</button>
<input type="submit" value="refresh table" />
</form>
<hr/>
<script>
/*utility functions*/
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('&');}
/* PLEASE TEST THIS URL IN WEB BROWSER FIRST TO ENSURE IT REACHES THE CORRECT SITE */
var url='https://script.google.com/macros/s/AKfycbzrnwhAxNesdr9EISFNO2iVEy9U4kG-oDjsWBAinyUbkf8NkCw/exec';
formjson={'action':'selectpersons'};
requestSelectPersons(formjson);
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);
/* GET Request*/
function requestSelectPersons(formjson){
document.getElementById("formAddEditDeletePerson").style.display = "none";
var thead=document.getElementById("thead");
thead.innerHTML="";
var tbody=document.getElementById("tbody");
tbody.innerHTML="";
var elMessage= document.getElementById("formSelectPersonsMessage");
elMessage.innerHTML = "getting data from server...";
var urlparameters=url + "?" +jsonToUrlParameters(formjson);
//console.log(urlparameters);
var xhr = new XMLHttpRequest();
xhr.open('GET', urlparameters, true);
xhr.onload = function () {
console.log(xhr.responseText);
var arrperson=JSON.parse(xhr.responseText);
tr = document.createElement('tr');
tr.innerHTML="<th>----</th><th>------</th><th>Name</th><th>Pid</th><th>Age</th>";
thead.append(tr);
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);
}
elMessage.innerHTML = "";
};
xhr.send();
}
/* POST Request*/
function requestAddEditDeletePerson(formjson){
var elMessage= document.getElementById("formAddEditDeletePersonMessage");
elMessage.innerHTML = "sending data to server...";
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) {
//console.log(xhr.responseText);
elMessage.innerHTML = "";
document.getElementById("formAddEditDeletePerson").reset();
formjson={'action':'selectpersons'};
requestSelectPersons(formjson);
}
}
xhr.send(sendparameters);
}
/*prepare forms*/
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["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["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.scrollIntoView();
}
function cancelForm(form){
form.reset();
form.style.display = "none";
}
</script>
</body>
</html>
|
3) Deploy and Test
Copy:
https://docs.google.com/spreadsheets/d/11yy72GQTnbq765PkSdvBDBPnjtE6hRebGazdty9W5rs/edit?usp=sharing
APPENDIX:
Check that your Code.gs contains the following:
/*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);
}
}
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;
}
/* 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;
}
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 array=[['dadu','0','badi'],['didi','1','bada'],['dida','2','bidi'],['didu','3','bidu']];
//output=findInArray(array,null,"da");
//Logger.log(output);
//output=findInArray(array,[2],"da");
//Logger.log(output);
output=findInArray(array,[0,2],"da");
Logger.log(output);
}
/* ref: https://github.com/AishwaryT/Google-app-script-crud/blob/master */
/* add record */
function addRecord(recordsname, e) {
var Sheet = SST.getSheetByName(recordsname);
try {
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
var rownum = Sheet.getLastRow() + 1; // get next row
var row = [];
var d = new Date();
// loop through the header columns
for (colno in headers) {
if (headers[colno] == "tid") { // special case if you include a 'timestamp' column
row.push(d.getTime());
} 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]]);
}
}
// more efficient to set values as [][] array than individually
Sheet.getRange(rownum, 1, 1, row.length).setValues([row]);
// return json success results with the row number of the newly added record
return rownum
} catch (e) {
// if error return this
return 0
}
}
/* edit record */
function editRecord(recordsname, e) {
var Sheet = SST.getSheetByName(recordsname);
try {
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
var array =Sheet.getRange(2, 1, Sheet.getLastRow()-1, Sheet.getLastColumn()).getValues();
var found=findInArray(array,[0],e.parameter['tid']);/*found is array*/
if (found.length>0){
var rownum=Number(found[0][0]);/*pickup the first item of the first row ie the array row num*/
rownum+=2;/*change from zero-based to one-based and add the headRow*/
}
//Logger.log(e.parameter['tid']);
//Logger.log(rownum);
if (rownum == undefined) {
//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(e.parameter['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 json success results with the row number of the newly added record
return rownum;
} catch (e) {
// if error return this
return 0
}
}
/* test edit record */
function testEditRecord(){
var parameter={action:'editrecord',tid:'1499613316279',name:'vovoi',pid:'7474',age:'44'};
var e={parameter:parameter};
Logger.log(editRecord('persons', e));
}
/* delete record */
function deleteRecord(recordsname, e) {
var Sheet = SST.getSheetByName(recordsname);
try {
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
var array =Sheet.getRange(2, 1, Sheet.getLastRow()-1, Sheet.getLastColumn()).getValues();
var found=findInArray(array,[0],e.parameter['tid']);/*found is array*/
if (found.length>0){
var rownum=Number(found[0][0]);/*pickup the first item of the first row ie the array row num*/
rownum+=2;/*change from zero-based to one-based and add the headRow*/
}
//Logger.log(e.parameter['tid']);
//Logger.log(rownum);
if (rownum == undefined) {
//row not found
return -1
}
// delete row by rownum
Sheet.deleteRow(rownum);
return rownum
} catch (e) {
// if error return this
return 0
}
}
/* test delete record */
function testDeleteRecord(){
var parameter={action:'deleterecord',tid:'1499613316279'};
var e={parameter:parameter};
Logger.log(deleteRecord('persons', e));
}
/* select record */
function selectRecord(recordsname, e) {
var Sheet = SST.getSheetByName(recordsname);
try {
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
var array =Sheet.getRange(2, 1, Sheet.getLastRow()-1, Sheet.getLastColumn()).getValues();
var found=findInArray(array,[0],e.parameter['tid']);/*found is array*/
if (found.length>0){
var rownum=Number(found[0][0]);/*pickup the first item of the first row ie the array row num*/
rownum+=2;/*change from zero-based to one-based and add the headRow*/
}
//Logger.log(e.parameter['tid']);
//Logger.log(rownum);
if (rownum == undefined) {
//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];
}
// return json success results with the row
return row;
} catch (e) {
// if error return this
return e;
}
}
/* test select record */
function testSelectRecord(){
var parameter={action:'selectrecord',tid:'1498794221687'};
var e={parameter:parameter};
Logger.log(selectRecord('persons', e));
}
/* get all records */
function selectRecords(recordsname, e) {
var Sheet = SST.getSheetByName(recordsname);
try {
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = Sheet.getRange(headRow, 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));
}
|
-
.
No comments:
Post a Comment