6.การสร้างฟอร์ม เพิ่ม/ลบ/ค้นหา/แก้ไข/ล้างข้อมูล ด้วย Google sheet /Google Script

1.ไฟล์ Code.gs

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('WebApp');
}

function uuid() {
  var uuid_array = [];
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA");
  var getLastRow = dataSheet.getLastRow();
  if(getLastRow > 1) {
    var uuid_values = dataSheet.getRange(2, 1, getLastRow - 1, 1).getValues(); 
    for(i = 0; i < uuid_values.length; i++)
    {
      uuid_array.push(uuid_values[i][0]);
    }
    var x_count = 0;
    do {
    var y = 'false';
    var uuid_value = Utilities.getUuid(); 

    if(uuid_array.indexOf(uuid_value) == -1.0)
    {
      y = 'true';
      Logger.log(uuid_value);
      return uuid_value;   
    } 
    x_count++;
    } while (y == 'false' && x_count < 5);
  } else {
    return Utilities.getUuid();
  }
}

function UpdateRecord(record_id, firstname, lastname, street, city, state, zip, email) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 8).getValues();
  for(i = 0; i < table_values.length; i++)
  {
    if(table_values[i][0] == record_id)
    {
      dataSheet.getRange(i+2, 2).setValue(firstname);
      dataSheet.getRange(i+2, 3).setValue(lastname);
      dataSheet.getRange(i+2, 4).setValue(street);
      dataSheet.getRange(i+2, 5).setValue(city);
      dataSheet.getRange(i+2, 6).setValue(state);
      dataSheet.getRange(i+2, 7).setValue(zip);
      dataSheet.getRange(i+2, 8).setValue(email);
    }
    
  }
  return 'SUCCESS';
}

function DeleteRecord(record_id)
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 8).getValues();
  for(i = 0; i < table_values.length; i++)
  {
    if(table_values[i][0] == record_id)
    {
      var rowNumber = i+2;
      dataSheet.getRange('A' + rowNumber +':I' + rowNumber).clearContent();
      
    }   
  }
  return 'SUCCESS';
}

function AddRecord(firstname, lastname, street, city, state, zip, email) {
  var uniqueID = uuid();
  var found_record = false;
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA");
  var getLastRow = dataSheet.getLastRow();
  for(i = 2; i < getLastRow; i++)
  {
    if(dataSheet.getRange(i, 1).getValue() == '')
    {
      dataSheet.getRange('A' + i + ':I' + i).setValues([[uniqueID, firstname, lastname, street, city, state, zip, email, new Date()]]);
      found_record = true;
      break;
    }
  }
  if(found_record == false)
  { 
    dataSheet.appendRow([uniqueID, firstname, lastname, street, city, state, zip, email, new Date()]);
  }
  return 'SUCCESS';
  
}

function searchRecords(firstname, lastname, street, city, state, zip, email) 
{

  var returnRows = [];
  var allRecords = getRecords();

  allRecords.forEach(function(value, index) {

    var evalRows = [];
    if(firstname != '')
    {
      if(value[1].toUpperCase() == firstname.toUpperCase()) {
        evalRows.push('true');
      } else {
        evalRows.push('false');
      }
    }
    else
    {
       evalRows.push('true');
    }

    if(lastname != '')
    {
       if(value[2].toUpperCase() == lastname.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(street != '')
    {
       if(value[3].toUpperCase() == street.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(city != '')
    {
       if(value[4].toUpperCase() == city.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(state != '')
    {
       if(value[5].toUpperCase() == state.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(zip != '')
    {
       if(value[6] == zip) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(email != '')
    {
       if(value[7].toUpperCase() == email.toUpperCase()) {
         evalRows.push('true');
       } else {
         evalRows.push('false');
       }
    }
    else
    {
       evalRows.push('true');
    }

    if(evalRows.indexOf("false") == -1)
    {
      returnRows.push(value);    
    }

  });

  return returnRows;
}

function getRecords() { 
  var return_Array = [];
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("DATA"); 
  var getLastRow = dataSheet.getLastRow();
  for(i = 2; i <= getLastRow; i++)
  {
    if(dataSheet.getRange(i, 1).getValue() != '')
    {
      return_Array.push([dataSheet.getRange(i, 1).getValue(), 
      dataSheet.getRange(i, 2).getValue(),
      dataSheet.getRange(i, 3).getValue(),
      dataSheet.getRange(i, 4).getValue(),
      dataSheet.getRange(i, 5).getValue(),
      dataSheet.getRange(i, 6).getValue(), 
      dataSheet.getRange(i, 7).getValue(), 
      dataSheet.getRange(i, 8).getValue()]);
    }
  }  
  return return_Array;  
}

2.ไฟล์ WebApp.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" 
    integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
    <script>
    function AddRow()
    {
      document.getElementById("add_button").disabled = true;
      var firstname = document.getElementById("firstname").value;
      var lastname = document.getElementById("lastname").value;
      var street = document.getElementById("street").value;
      var city = document.getElementById("city").value;
      var state = document.getElementById("state").value;
      var zip = document.getElementById("zip").value;
      var email = document.getElementById("email").value;
      if(firstname != '' && lastname != '' && street != '' && city != '' && state != '' && zip != '' && email != '')
      {
        google.script.run.withSuccessHandler(function(return_string) 
        {
          SearchRecords();
          document.getElementById("add_button").disabled = false;
        }).AddRecord(firstname, lastname, street, city, state, zip, email);       
      }
      else
      {
        document.getElementById("display_error").innerHTML = "Please Enter All Information!";
        document.getElementById("add_button").disabled = false;
      }
    }

    function ClearRecord()
    {
        document.getElementById("firstname").value = '';
        document.getElementById("lastname").value = '';
        document.getElementById("street").value = '';
        document.getElementById("city").value = '';
        document.getElementById("state").value = '';
        document.getElementById("zip").value = '';
        document.getElementById("email").value = '';
        document.getElementById("display_error").innerHTML = "";
    }

    function UpdateRecord(row_number)
    {
      document.getElementById("update_button"+row_number).disabled = true;
      var record_id = document.getElementById("up_record_id"+row_number).value;
      var firstname = document.getElementById("up_firstname"+row_number).value;
      var lastname = document.getElementById("up_lastname"+row_number).value;
      var street = document.getElementById("up_street"+row_number).value;
      var city = document.getElementById("up_city"+row_number).value;
      var state = document.getElementById("up_state"+row_number).value;
      var zip = document.getElementById("up_zip"+row_number).value;
      var email = document.getElementById("up_email"+row_number).value;

      google.script.run.withSuccessHandler(function(return_string) 
      {
          document.getElementById("update_button"+row_number).disabled = false;
      }).UpdateRecord(record_id, firstname, lastname, street, city, state, zip, email);
    }

    function DeleteRecord(row_number)
    {

      var record_id = document.getElementById("up_record_id"+row_number).value;

      google.script.run.withSuccessHandler(function(return_string) 
      {
        if(return_string == 'SUCCESS')
        {
          document.getElementById('mainTable').deleteRow(document.getElementById('ROWNUMBER:'+row_number).rowIndex); 
        }
      }).DeleteRecord(record_id);
    }

    function SearchRecords() 
    {
      var firstname = document.getElementById("firstname").value;
      var lastname = document.getElementById("lastname").value;
      var street = document.getElementById("street").value;
      var city = document.getElementById("city").value;
      var state = document.getElementById("state").value;
      var zip = document.getElementById("zip").value;
      var email = document.getElementById("email").value;
      var row_number = 0;

      google.script.run.withSuccessHandler(function(ar) 
      {

        console.log(ar);
        var displayTable = '<form>';
        displayTable += '<table class=\"table\" id=\"mainTable\" >';
        displayTable += "<tr>";
        displayTable += "<th>Name</th>";
        displayTable += "<th>Address</th>";
        displayTable += "<th>Email</th>";
        displayTable += "<th></th>";
        displayTable += "</tr>";

        ar.forEach(function(item, index) 
        {
          displayTable += "<tr id=\"ROWNUMBER:"+ row_number +"\" >";
          displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-12\" ><label for=\"up_firstname\">First</label>  ";
          displayTable += "<input type=\"text\" id=\"up_firstname"+ row_number +"\" value=\""+item[1]+"\" class=\"form-control\" /> ";
          displayTable += "<input type=\"hidden\" id=\"up_record_id"+ row_number +"\" value=\""+item[0]+"\" /></div></div> ";
          displayTable += "<div class=\"form-row\"><div class=\"form-group col-md-12\"><label for=\"up_lastname\">Last</label> ";
          displayTable += "<input type=\"text\" id=\"up_lastname"+ row_number +"\" value=\""+item[2]+"\" class=\"form-control\" /></div></div></td> ";
          displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-12\"><label for=\"up_street\">Street</label> ";
          displayTable += "<input type=\"text\" id=\"up_street"+ row_number +"\" value=\""+item[3]+"\" class=\"form-control\" /></div></div> ";
          displayTable += "<div class=\"form-row\"><div class=\"form-group col-md-6\"><label for=\"up_city\">City</label> "; 
          displayTable += "<input type=\"text\" id=\"up_city"+ row_number +"\" value=\""+item[4]+"\" class=\"form-control\" /></div> ";
          displayTable += "<div class=\"form-group col-md-4\"><label for=\"up_state\">State</label> ";
          displayTable += "<input type=\"text\" id=\"up_state"+ row_number +"\" value=\""+item[5]+"\" class=\"form-control\" /></div> ";
          displayTable += "<div class=\"form-group col-md-2\"><label for=\"up_zip\">Zip</label> ";
          displayTable += "<input type=\"text\" id=\"up_zip"+ row_number +"\" value=\""+item[6]+"\" class=\"form-control\" /></div></div> ";
          displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-12\" ><label for=\"up_email\">Email</label> ";
          displayTable += "<input type=\"text\" id=\"up_email"+ row_number +"\" value=\""+item[7]+"\" class=\"form-control\" /></div></div></td> ";
          displayTable += "<td><div style=\"padding: 5px\"> ";
          displayTable += "<input type=\"button\" id=\"update_button"+ row_number +"\" value=\"Update\" class=\"btn btn-primary\" ";
          displayTable += "onclick=\"UpdateRecord("+row_number+")\" /></div> ";
          displayTable += "<div style=\"padding: 5px\" > ";
          displayTable += "<input type=\"button\" value=\"Delete\" id=\"delete_button"+ row_number +"\"  class=\"btn btn-primary\" ";
          displayTable += "onclick=\"DeleteRecord("+row_number+")\" /></div> ";
          displayTable += "</td>";
          displayTable += "</tr>";
          row_number++;    
        });

        displayTable += '</table></form>';     
        document.getElementById("rowdata").innerHTML = displayTable;

      }).searchRecords(firstname, lastname, street, city, state, zip, email);
      
    }
    </script>
  </head>
  <body>
  <div style="padding: 10px;" >
  <form>
  <div class="form-row">
  <div class="form-group col-md-3">
  <label for="firstname">First Name</label>
  <input type="text" id="firstname" class="form-control" />
  </div>
  <div class="form-group col-md-3">
  <label for="lastname">Last Name</label>
  <input type="text" id="lastname" class="form-control" />
  </div> 
  </div>
  <div class="form-row">
  <div class="form-group col-md-6">
  <label for="street">Street</label>
  <input type="text" id="street" class="form-control" />
  </div> 
  </div>
  <div class="form-row">
  <div class="form-group col-md-3">
  <label for="city">City</label>
  <input type="text" id="city" class="form-control" />
  </div> 
  <div class="form-group col-md-2">
  <label for="state">State</label>
  <input type="text" id="state" class="form-control" />
  </div> 
  <div class="form-group col-md-1">
  <label for="zip" >Zip</label>
  <input type="text" id="zip" class="form-control" />
  </div> 
  </div>
  <div class="form-row">
  <div class="form-group col-md-3">
  <label for="email" >Email</label>
  <input type="text" id="email" class="form-control "/>
  </div>
  </div>
  <div class="form-group col-md-6">
  <input type="button" value="Search" class="btn btn-primary" onclick="SearchRecords()" />
  <input type="button" value="Add Record" class="btn btn-primary" onclick="AddRow()" id="add_button" />
  <input type="button" value="Clear Record" class="btn btn-primary" onclick="ClearRecord()" />
  <div id="display_error" style="color: red" ></div>
  </div>
  </form> 
  <div id="rowdata"></div>
  </div>
  </body>
</html>