เว็บแอป CRUD อัปโหลดโค้ดวันแม่ 2564

code.gs

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate()
      .addMetaTag('viewport','width=device-width , initial-scale=1')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

function globalVariables(){ 
  var varArray = {
    spreadsheetId   : 'xxx', //** CHANGE !!!
    dataRage        : 'ข้อมูล!A2:G',                                    //** CHANGE !!!
    idRange         : 'ข้อมูล!A2:A',                                    //** CHANGE !!!
    lastCol         : 'G',                                            //** CHANGE !!!
    insertRange     : 'ข้อมูล!A1:G1',                                   //** CHANGE !!!
    sheetID         : '0'                                             //** CHANGE !!! 
  };
  return varArray;
}

/* PROCESS FORM */
function processForm(formObject){  
  if(formObject.RecId && checkID(formObject.RecId)){//Execute if form passes an ID and if is an existing ID
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); // Update Data
  }else{ //Execute if form does not pass an ID
    appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); //Append Form Data
  }
  return getLastTenRows();//Return last 10 rows
}


/* GET FORM VALUES AS AN ARRAY */
function getFormValues(formObject){
/* ADD OR REMOVE VARIABLES ACCORDING TO YOUR FORM*/
  if(formObject.RecId && checkID(formObject.RecId)){
    var values = [[formObject.RecId.toString(),
                  formObject.name,
                  formObject.gender,
                  formObject.dateOfBirth,
                  formObject.email,
                  formObject.phone,
                  formObject.country]];
  }else{
    var values = [[new Date().getTime().toString(),//https://webapps.stackexchange.com/a/51012/244121
                  formObject.name,
                  formObject.gender,
                  formObject.dateOfBirth,
                  formObject.email,
                  formObject.phone,
                  formObject.country]];
  }
  return values;
}


/*
## CURD FUNCTIONS ----------------------------------------------------------------------------------------
*/


/* CREATE/ APPEND DATA */
function appendData(values, spreadsheetId,range){
  var valueRange = Sheets.newRowData();
  valueRange.values = values;
  var appendRequest = Sheets.newAppendCellsRequest();
  appendRequest.sheetID = spreadsheetId;
  appendRequest.rows = valueRange;
  var results = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range,{valueInputOption: "RAW"});
}


/* READ DATA */
function readData(spreadsheetId,range){
  var result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
  return result.values;
}


/* UPDATE DATA */
function updateData(values,spreadsheetId,range){
  var valueRange = Sheets.newValueRange();
  valueRange.values = values;
  var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {
  valueInputOption: "RAW"});
}


/*DELETE DATA*/
function deleteData(ID){ 
  var startIndex = getRowIndexByID(ID);
  
  var deleteRange = {
                      "sheetId"     : globalVariables().sheetID,
                      "dimension"   : "ROWS",
                      "startIndex"  : startIndex,
                      "endIndex"    : startIndex+1
                    }
  
  var deleteRequest= [{"deleteDimension":{"range":deleteRange}}];
  Sheets.Spreadsheets.batchUpdate({"requests": deleteRequest}, globalVariables().spreadsheetId);
  
  return getLastTenRows();//Return last 10 rows
}



/* 
## HELPER FUNCTIONS FOR CRUD OPERATIONS --------------------------------------------------------------
*/ 


/* CHECK FOR EXISTING ID, RETURN BOOLEAN */
function checkID(ID){
  var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange,).reduce(function(a,b){return a.concat(b);});
  return idList.includes(ID);
}


/* GET DATA RANGE A1 NOTATION FOR GIVEN ID */
function getRangeByID(id){
  if(id){
    var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
    for(var i=0;i<idList.length;i++){
      if(id==idList[i][0]){
        return 'ข้อมูล!A'+(i+2)+':'+globalVariables().lastCol+(i+2);
      }
    }
  }
}


/* GET RECORD BY ID */
function getRecordById(id){
  if(id && checkID(id)){
    var result = readData(globalVariables().spreadsheetId,getRangeByID(id));
    return result;
  }
}


/* GET ROW NUMBER FOR GIVEN ID */
function getRowIndexByID(id){
  if(id){
    var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
    for(var i=0;i<idList.length;i++){
      if(id==idList[i][0]){
        var rowIndex = parseInt(i+1);
        return rowIndex;
      }
    }
  }
}


/*GET LAST 10 RECORDS */
function getLastTenRows(){
  var lastRow = readData(globalVariables().spreadsheetId,globalVariables().dataRage).length+1;
  if(lastRow<=11){
    var range = globalVariables().dataRage;
  }else{
    var range = 'ข้อมูล!A'+(lastRow-9)+':'+globalVariables().lastCol;
  }
  var lastTenRows = readData(globalVariables().spreadsheetId,range);
  return lastTenRows;
}


/* GET ALL RECORDS */
function getAllData(){
  var data = readData(globalVariables().spreadsheetId,globalVariables().dataRage);
  return data;
}


/*
## OTHER HELPERS FUNCTIONS ------------------------------------------------------------------------
*/


/*GET DROPDOWN LIST */
function getDropdownList(range){
  var list = readData(globalVariables().spreadsheetId,range);
  return list;
}


/* INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

CSS.html

<style>
.btn-group-xs > .btn, .btn-xs {
  padding: .25rem .4rem;
  font-size: .875rem;
  line-height: .5;
  border-radius: .2rem;
}
</style>

DataTable.html

<p class="h4 mb-4 text-center">ฐานข้อมูลรายชื่อทั้งหมด</p>

<div id="dataTable" class="table-responsive">
  <!-- The Data Table is inserted here by JavaScript -->
</div>
<br>
<button type="button" class="btn btn-success btn-sm" onclick="getAllData()">ดูข้อมูลทั้งหมด</button>

Form.html

<!-- ## The html code of the form goes here -->
<!-- ## This is included in the Index.html page using "include('Form')" function -->

<form id="myForm" class="p-2 border border-light rounded bg-light" onsubmit="handleFormSubmit(this)"> <!-- Call JavaScript function "handleFormSubmit" -->
    <p class="h4 mb-4 text-center">ฟอร์มกรอกข้อมูล</p>
    <div id="message"></div>
    <input type="text" id="RecId" name="RecId" value="" style="display: none">
	<div class="form-group">
		<label for="name" >ชื่อ สกุล</label>
		<input type="text" class="form-control" id="name" name="name" placeholder="ชื่อ สกุล" required>
	</div>
	<div class="form-row">
		<div class="form-group col-md-6">
			<p>เพศ</p>
			<div class="form-check form-check-inline">
				<input class="form-check-input" type="radio" name="gender" id="male" value="ชาย">
				<label class="form-check-label" for="male">ชาย</label>
			</div>
			<div class="form-check form-check-inline">
				<input class="form-check-input" type="radio" name="gender" id="female" value="หญิง">
				<label class="form-check-label" for="female">หญิง</label>
			</div>
		</div>
		<div class="form-group col-md-6">
			<label for="dateOfBirth">วันเกิด</label>
			<input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
		</div>
	</div>
	<div class="form-group">
		<label for="email">อีเมล</label>
		<input type="email" class="form-control" id="email" name="email" placeholder="อีเมล">
	</div>
    <div class="form-row">
      <div class="form-group col-md-6">
          <label for="phone">เบอร์โทร</label>
          <input type="tel" class="form-control" id="phone" name="phone" placeholder="เบอร์โทร">
      </div>
      <div class="form-group col-md-6">
        <label for="exampleFormControlSelect1">ระดับชั้น</label>
        <select class="form-control" id="country" name="country">
          <option>เลือกระดับชี่น</option>
        </select>
      </div>
    </div>
	<button type="submit" class="btn btn-primary">บันทึกข้อมูล</button>
    <input class="btn btn-secondary" type="reset" value="รีเซ็ต">
</form>

Index.html

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
        <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
        <?!= include('CSS'); ?> <!-- See CSS.html file -->
        <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Prompt">
	<style>
		body {
			font-family: "Prompt", sans-serif;
			font-size: 15px;
		}
	</style>
    </head>
    <body onload="createCountryDropdown()">
        <div class="container">
            <div class="row">
                <div class="col-lg-6">
                  <?!= include('Form'); ?> <!-- See Form.html file -->
                  <br><br>
                  <div id="output"></div>
                </div>
                <div class="col-lg-6">
                  <?!= include('DataTable'); ?> <!-- See DataTable.html File -->
                </div>
            </div>      
        </div>
    </body>
</html>

JavaScript.html

<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  }
  window.addEventListener("load", functionInit, true); 
  
  //INITIALIZE FUNCTIONS ONLOAD
  function functionInit(){  
    preventFormSubmit();
    getLastTenRows();
  };      
  
  //HANDLE FORM SUBMISSION
  function handleFormSubmit(formObject) {
    google.script.run.withSuccessHandler(createTable).processForm(formObject);
    document.getElementById("myForm").reset();
  }
  
  //GET LAST 10 ROWS
  function getLastTenRows (){
   google.script.run.withSuccessHandler(createTable).getLastTenRows();
  }
  
  
  //GET ALL DATA
  function getAllData(){
    //document.getElementById('dataTable').innerHTML = "";
    google.script.run.withSuccessHandler(createTable).getAllData();
  }
  
  
  //CREATE THE DATA TABLE
  function createTable(dataArray) {
    if(dataArray){
      var result = "<table class='table table-sm' style='font-size:0.8em'>"+
                   "<thead style='white-space: nowrap'>"+
                     "<tr>"+                               //Change table headings to match witht he Google Sheet
                      "<th scope='col'>ลบ</th>"+
                      "<th scope='col'>แก้ไข</th>"+
                      "<th scope='col'>ไอดี</th>"+
                      "<th scope='col'>ชื่อ สกุล</th>"+
                      "<th scope='col'>เพศ</th>"+
                      "<th scope='col'>วันเกิด</th>"+
                      "<th scope='col'>อีเมล</th>"+
                      "<th scope='col'>เบอร์โทร</th>"+
                      "<th scope='col'>ระดับชั้น</th>"+
                    "</tr>"+
                  "</thead>";
      for(var i=0; i<dataArray.length; i++) {
          result += "<tr>";
          result += "<td><button type='button' class='btn btn-danger btn-xs deleteBtn' onclick='deleteData(this);'>Delete</button></td>";
          result += "<td><button type='button' class='btn btn-warning btn-xs editBtn' onclick='editData(this);'>Edit</button></td>";
          for(var j=0; j<dataArray[i].length; j++){
              result += "<td>"+dataArray[i][j]+"</td>";
          }
          result += "</tr>";
      }
      result += "</table>";
      var div = document.getElementById('dataTable');
      div.innerHTML = result;
      document.getElementById("message").innerHTML = "";
    }else{
      var div = document.getElementById('dataTable');
      div.innerHTML = "ไม่พบข้อมูล!";
    }
  }

  //DELETE DATA
  function deleteData(el) {
    var result = confirm("คุณต้องการจะลบข้อมูล?");
    if (result) {
      var recordId = el.parentNode.parentNode.cells[2].innerHTML;
      google.script.run.withSuccessHandler(createTable).deleteData(recordId);
    }
  }
  
  
  //EDIT DATA
  function editData(el){
    var recordId = el.parentNode.parentNode.cells[2].innerHTML; //https://stackoverflow.com/a/32377357/2391195
    google.script.run.withSuccessHandler(populateForm).getRecordById(recordId);
  }

  //POPULATE FORM
  function populateForm(records){
    document.getElementById('RecId').value = records[0][0];
    document.getElementById('name').value = records[0][1];
    document.getElementById(records[0][2] =='ชาย'? 'male':'female').checked = true;
    document.getElementById('dateOfBirth').value = records[0][3];
    document.getElementById('email').value = records[0][4];
    document.getElementById('phone').value = records[0][5];
    document.getElementById("country").value = records[0][6];
    document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update Record [ID: "+records[0][0]+"]</div>";
  }
  
  //RETRIVE DATA FROM GOOGLE SHEET FOR COUNTRY DROPDOWN
  function createCountryDropdown() {
      //SUBMIT YOUR DATA RANGE FOR DROPDOWN AS THE PARAMETER
      google.script.run.withSuccessHandler(countryDropDown).getDropdownList("ระดับชั้น!A1:A30");
  }
  
  //POPULATE COUNTRY DROPDOWNS
  function countryDropDown(values) { //Ref: https://stackoverflow.com/a/53771955/2391195
    var list = document.getElementById('country');   
    for (var i = 0; i < values.length; i++) {
      var option = document.createElement("option");
      option.value = values[i];
      option.text = values[i];
      list.appendChild(option);
    }
  }
</script>