Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Monday, February 1, 2016

1005 Apps Script XMLHttpRequest


.

1005 apps script XMLHttpRequest
How to write Google App Script to provide HTML page with XMLHttpRequest

1) Prepare the startup file

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:

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