.
1009 Apps Script Search Records
How to write Google App Script to search and edit Google Sheet Database
Objective
1. List records.
2. Search records.
3. Edit record.
4. Update list records.
1) Prepare the startup file
Make a copy of the 1008 tutorial file
https://docs.google.com/spreadsheets/d/1EiLthgQHQz4tMti9j4VZxN7yPdqa6q7N5ZHBDWzr08o/edit?usp=sharing
Rename it as SearchEditDelete
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);
}else if (action == 'searchpersons') {
return manageRecords("searchpersons",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;
case "searchpersons":
output=searchRecords("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 values = SheetSorted.getRange(itemstart, 1, itemstop-(itemstart-1), Sheet.getLastColumn()).getValues();
var headers = SheetSorted.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
var rows = [];
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);
}
output={
itemcount:itemcount,
rowcount:rowcount,
itemcurrent:itemcurrent,
itemstart:itemstart,
itemstop:itemstop,
length:values.length,
rows:rows
};
return output
} 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)));
}
/*search in records*/
function searchRecords(recordsname,e){
try{
var Sheet= SST.getSheetByName(recordsname);
var SheetSorted= SST.getSheetByName(recordsname+'(sorted)');
var searchtexts=e.parameter['searchtexts']||'';
if (searchtexts==''){return -1;}
var arrdata =SheetSorted.getRange(2, 1, Sheet.getLastRow()-1,Sheet.getLastColumn()).getValues();
var arrfound=findInArray(arrdata,[2,3,4],searchtexts);
if (arrfound.length>0){
var headers = SheetSorted.getRange(1, 1, 1, Sheet.getLastColumn()).getValues()[0];
var rows = [];
for (var i = 0; i < arrfound.length; i++) {
var row = {};
row['_row']=i+1;/*change from zero-based to one-based and add the headRow*/
for (var j = 0; j < arrfound[i].length; j++) {
row[headers[j]] = arrfound[i][j+1];
}
rows.push(row);
}
output={
rows:rows
};
return output
}
else{
return -1;
}
}catch (e) {
return e;
}
}
/* test search records */
function testSearchRecords(){
var parameter={action:'searchpersons',searchtexts:'ac'};
var e={parameter:parameter};
Logger.log(JSON.stringify(searchRecords('persons', e)));
}
|
3) Edit the user page
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="formSearchPersons" style="display:none;background-color:WhiteSmoke;padding:4px;">
<input type="hidden" name="action" value="searchpersons">
<h3>Search Persons</h3>
<p>
<label for="searchtexts">Search Texts:</label>
<input name="searchtexts" type="text" value="" />
</p>
<input name="submit" type="submit" value="ok" />
<button type="button" onclick="cancelForm(this.form)" value="cancel">cancel</button>
<p id="formSearchPersonsMessage" style="background-color:yellow;"></p>
<table id="tblSearchResults" border=1>
</table>
<hr/>
</form>
<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>
<input name="submit" type="submit" value="ok" />
<button type="button" onclick="cancelForm(this.form)" value="cancel">cancel</button>
<p id="formAddEditDeletePersonMessage" style="background-color:yellow;"></p>
<hr/>
</form>
<form id="formSelectPersons" style="display:block;padding:4px;">
<h3>Persons Table</h3>
<button type="button" onclick="prepareSearchPersons()" value="cancel">search persons</button>
<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();
document.addEventListener( "DOMContentLoaded", function() {
var formSearchPersons = document.getElementById( "formSearchPersons" ).addEventListener( "submit", function( e ) {
e.preventDefault(); /*prevent default submit */
requestSearchPersons(formToJson(this));
}, false);
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);
})/*DOMContentLoaded*/
function refreshPage(){
resetSearchPersons();
resetSelectPersons();
requestSelectPersonsPage();
}
function resetSelectPersons(){
var tblPerson=document.getElementById("tblPerson");
tblPerson.innerHTML = "";
}
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 requestSearchPersons(formjson){
var elMessage= document.getElementById("formSearchPersonsMessage");
//elMessage.innerHTML = "sending data to server...";
elMessage.innerHTML= "<i class='fa fa-spinner fa-spin' style='font-size:24px'></i>"
var tblSearchResults=document.getElementById('tblSearchResults');
tblSearchResults.innerHTML="";
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 = "";
if(xhr.responseText==-1){return true;}
objresponse=JSON.parse(xhr.responseText);
console.log(objresponse);
var arrperson=objresponse.rows;
if (!(tblSearchResults.getElementsByTagName('thead')[0])){
console.log('creating thead');
var thead=document.createElement('thead');
var tr = document.createElement('tr');
tr.innerHTML="<th>----</th><th>------</th><th>Name</th><th>Pid</th><th>Age</th>";
thead.append(tr);
tblSearchResults.append(thead);
}
if (!(tblSearchResults.getElementsByTagName('tbody')[0])){
console.log('creating tbody');
var tbody=document.createElement('tbody');
tblSearchResults.append(tbody);
}
for (var i = 0; i < arrperson.length; i++) {
var 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);
}
document.getElementById("formSearchPersons").reset();
}
}
xhr.send(sendparameters);
}
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 resetSearchPersons(){
var tblSearchResults=document.getElementById("tblSearchResults");
tblSearchResults.innerHTML = "";
var formSearchPersons = document.getElementById( "formSearchPersons" );
formSearchPersons.style.display = "none";
}
function prepareSearchPersons(){
var formSearchPersons = document.getElementById( "formSearchPersons" );
formSearchPersons.style.display = "block";
var tblSearchResults=document.getElementById("tblSearchResults");
tblSearchResults.innerHTML = "";
}
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
Test
Copy:
https://docs.google.com/spreadsheets/d/1aPsURZ08AXviFKW8kEYGy2-fG-LZZUXxA-RFhwYWmBc/edit?usp=sharing
.
