.
1005 apps script XMLHttpRequest
How to write Google App Script to provide HTML page with XMLHttpRequest
1) Prepare the startup file
Make a copy of the 1003 tutorial file https://docs.google.com/spreadsheets/d/1rJ5IsjPr5HFQFCIZrajxd6nNU9fZGbxhO6aEm8gpFSQ/edit#gid=0
Rename it as XMLHttpRequest
Follow the 1004 tutorial to allow valid users to access the user page (refer APPENDIX below)
2) Edit the user page
user.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="formAddPerson" name="formAddPerson">
<input type="hidden" name="action" value="addperson">
<h3>Add Person</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 class="sendrequest resetaftersubmit"></p>
<input type="submit" value="add person" />
</form>
<hr/>
<form id="formSelectPersons">
<input type="hidden" name="action" value="selectpersons">
<h3>Select Persons</h3>
<p class="sendrequest"></p>
<input type="submit" value="select persons" />
</form>
<hr/>
<form id="formSelectPerson">
<input type="hidden" name="action" value="selectperson">
<h3>Select Person</h3>
<p>
<label for="tid">Tid:</label>
<input name="tid" type="text" value="" />
</p>
<p class="sendrequest resetaftersubmit"></p>
<input type="submit" value="select person" />
</form>
<hr/>
<form id="formEditPerson">
<input type="hidden" name="action" value="editperson">
<input type="hidden" name="tid" value="">
<h3>Edit Person</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 class="sendrequest resetaftersubmit"></p>
<input type="submit" value="edit person" />
</form>
<hr/>
<form id="formDeletePerson">
<input type="hidden" name="action" value="deleteperson">
<h3>Delete Person</h3>
<p>
<label for="tid">Tid:</label>
<input name="tid" type="text" value="" />
</p>
<p class="sendrequest resetaftersubmit"></p>
<input type="submit" value="delete person" />
</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('&');}
var url='https://script.google.com/macros/s/AKfycbxOCzbmKN3J8k3ubrn_Y-CNWCcGWJorAdCJljZABaOJ_md-FceB/exec?';
/*DOMContentLoaded*/
document.addEventListener( "DOMContentLoaded", function() {
var formsCollection = document.getElementsByTagName("form");
for(var i=0;i<formsCollection.length;i++)
{
var formPerson = formsCollection[i];
formPerson.addEventListener( "submit", function( e ) {
e.preventDefault(); /*prevent default submit */
if (formPerson.id=="formSelectPersons"){
requestSelectPersons(this); /*GET Request*/
}
else{
requestSelectAddEditDeletePerson(this); /*POST Request*/
}
}, false); /*addEventListener*/
}/*for*/
})/*DOMContentLoaded*/
/* uses GET Request*/
function requestSelectPersons(frmPerson){
var urlparameters=jsonToUrlParameters(formToJson(frmPerson));
//console.log(urlparameters);
resetForms();
elMessage=document.getElementById(frmPerson.id).getElementsByClassName("sendrequest")[0];
elMessage.innerHTML = "sending request to server...";
var xhr = new XMLHttpRequest();
xhr.open('GET', url+urlparameters, true);
xhr.onload = function () {
//console.log(xhr.responseText);
elMessage.innerHTML = xhr.responseText;
};
xhr.send();
}
/* uses POST Request*/
function requestSelectAddEditDeletePerson(frmPerson){
var sendparameters=jsonToUrlParameters(formToJson(frmPerson));
//console.log(sendparameters);
resetForms();
elMessage=document.getElementById(frmPerson.id).getElementsByClassName("sendrequest")[0];
elMessage.innerHTML = "sending request to server...";
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);
if(frmPerson.id=="formSelectPerson"){
elMessage.innerHTML = xhr.responseText;
var objperson=JSON.parse(xhr.responseText);
var formEditPerson = document.getElementById( "formEditPerson" );
formEditPerson.elements["tid"].value=objperson.tid;
formEditPerson.elements["name"].value=objperson.name;
formEditPerson.elements["pid"].value=objperson.pid;
formEditPerson.elements["age"].value=objperson.age;
var formDeletePerson = document.getElementById( "formDeletePerson" );
formDeletePerson.elements["tid"].value=objperson.tid;
}
else{
elMessage.innerHTML = '';
requestSelectPersons(document.getElementById("formSelectPersons"));
}
}
}
xhr.send(sendparameters);
}
/*reset forms and form messages*/
function resetForms(){
document.querySelectorAll('form').forEach(function(form) {
form.reset();
});
document.querySelectorAll('.resetaftersubmit').forEach(function(p) {
p.innerHTML="";
});
}
</script>
</body>
</html>
|
3) Publish and Test
COPY:
https://docs.google.com/spreadsheets/d/1tTplyZ2bxXde2NiejD3VAL6LBHRc1qvqIqMgzCWdLtQ/edit?usp=sharing
REFERENCES:
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