CRUD Bpwebs DataTable Style

CRUD credit Bpwebs code.gs

//code.gs
/**
 * Creating a Google Sheets Data Entry Form for CRUD Operations
 * By: bpwebs.com
 * Post URL: https://www.bpwebs.com/crud-operations-on-google-sheets-with-online-forms
 * Dev Examblog : https://examblog64.krooluang.com
 */

//CONSTANTS
const SPREADSHEETID = "xxx";
const DATARANGE = "Data!A2:I";
const DATASHEET = "Data";
const DATASHEETID = "0";
const LASTCOL = "I";
const IDRANGE = "Data!A2:A";
const DROPDOWNRANGE = "Helpers!A1:A195"; //COUNTRY LIST

//Display HTML page
function doGet() {
 return HtmlService.createTemplateFromFile('Index').evaluate()
 .setTitle('CRUD')
 .setFaviconUrl('https://sv1.picz.in.th/images/2023/04/18/mmBsC9.png')
 .addMetaTag('viewport', 'width=device-width, initial-scale=1')
 .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

//PROCESS SUBMITTED FORM DATA
function processForm(formObject) {
  if (formObject.recId && checkId(formObject.recId)) {
    const values = [[
      ,
      formObject.name,
      formObject.description,
      formObject.category,
      formObject.countryOfOrigin,
      formObject.condition,
      formObject.price,
      formObject.quantity,
      new Date().toLocaleString()
    ]];
    const updateRange = getRangeById(formObject.recId);
    //Update the record
    updateRecord(values, updateRange);
  } else {
    //Prepare new row of data
    let values = [[
      ,
      formObject.name,
      formObject.description,
      formObject.category,
      formObject.countryOfOrigin,
      formObject.condition,
      formObject.price,
      formObject.quantity,
      new Date().toLocaleString()
    ]];

    //Create new record
    createRecord(values);
  }

  //Return the last 10 records
  return getLastTenRecords();
}


/**
 * CREATE RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#append_values
 */
function createRecord(values) {
  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = SPREADSHEETID;
    appendRequest.rows = valueRange;

    Sheets.Spreadsheets.Values.append(valueRange, SPREADSHEETID, DATARANGE, { valueInputOption: "RAW" });
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

/**
 * READ RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#read
 */
function readRecord(range) {
  try {
    let result = Sheets.Spreadsheets.Values.get(SPREADSHEETID, range);
    return result.values;
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

/**
 * UPDATE RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#write_to_a_single_range
 */
function updateRecord(values, updateRange) {
  try {
    let valueRange = Sheets.newValueRange();
    valueRange.values = values;
    Sheets.Spreadsheets.Values.update(valueRange, SPREADSHEETID, updateRange, { valueInputOption: "RAW" });
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}


/**
 * DELETE RECORD
 * Ref:
 * https://developers.google.com/sheets/api/guides/batchupdate
 * https://developers.google.com/sheets/api/samples/rowcolumn#delete_rows_or_columns
*/
function deleteRecord(id) {
  const rowToDelete = getRowIndexById(id);
  const deleteRequest = {
    "deleteDimension": {
      "range": {
        "sheetId": DATASHEETID,
        "dimension": "ROWS",
        "startIndex": rowToDelete,
        "endIndex": rowToDelete + 1
      }
    }
  };
  Sheets.Spreadsheets.batchUpdate({ "requests": [deleteRequest] }, SPREADSHEETID);
  return getLastTenRecords();
}


/**
 * RETURN LAST 10 RECORDS IN THE SHEET
 */
function getLastTenRecords() {
  let lastRow = readRecord(DATARANGE).length + 1;
  let startRow = lastRow - 9;
  if (startRow < 2) { //If less than 10 records, eleminate the header row and start from second row
    startRow = 2;
  }
  let range = DATASHEET + "!A" + startRow + ":" + LASTCOL + lastRow;
  let lastTenRecords = readRecord(range);
  Logger.log(lastTenRecords);
  return lastTenRecords;
}


//GET ALL RECORDS
function getAllRecords() {
  const allRecords = readRecord(DATARANGE);
  return allRecords;
}

//GET RECORD FOR THE GIVEN ID
function getRecordById(id) {
  if (!id || !checkId(id)) {
    return null;
  }
  const range = getRangeById(id);
  if (!range) {
    return null;
  }
  const result = readRecord(range);
  return result;
}

function getRowIndexById(id) {
  if (!id) {
    throw new Error('Invalid ID');
  }

  const idList = readRecord(IDRANGE);
  for (var i = 0; i < idList.length; i++) {
    if (id == idList[i][0]) {
      var rowIndex = parseInt(i + 1);
      return rowIndex;
    }
  }
}


//VALIDATE ID
function checkId(id) {
  const idList = readRecord(IDRANGE).flat();
  return idList.includes(id);
}


//GET DATA RANGE IN A1 NOTATION FOR GIVEN ID
function getRangeById(id) {
  if (!id) {
    return null;
  }
  const idList = readRecord(IDRANGE);
  const rowIndex = idList.findIndex(item => item[0] === id);
  if (rowIndex === -1) {
    return null;
  }
  const range = `Data!A${rowIndex + 2}:${LASTCOL}${rowIndex + 2}`;
  return range;
}


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

//GENERATE UNIQUE ID
function generateUniqueId() {
  let id = Utilities.getUuid();
  return id;
}

function getCountryList() {
  countryList = readRecord(DROPDOWNRANGE);
  return countryList;
}

//SEARCH RECORDS
function searchRecords(formObject) {
  let result = [];
  try {
    if (formObject.searchText) {//Execute if form passes search text
      const data = readRecord(DATARANGE);
      const searchText = formObject.searchText;

      // Loop through each row and column to search for matches
      for (let i = 0; i < data.length; i++) {
        for (let j = 0; j < data[i].length; j++) {
          const cellValue = data[i][j];
          if (cellValue.toLowerCase().includes(searchText.toLowerCase())) {
            result.push(data[i]);
            break; // Stop searching for other matches in this row
          }
        }
      }
    }
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
  return result;
}

FormProductDetails

<form id="ProductDetails" onsubmit="handleFormSubmit(this)">
  <div id="message"></div>
  <input type="text" id="recId" name="recId" value="" style="display: none">
  <div class="mb-1">
    <label for="name" class="form-label">Product Name:</label>
    <input type="text" id="name" name="name" class="form-control form-control-sm" required>
  </div>

  <div class="mb-1">
    <label for="description" class="form-label">Product Description:</label>
    <textarea id="description" name="description" class="form-control form-control-sm" rows="2"></textarea>
  </div>

  <div class="mb-1">
    <label for="category" class="form-label">Product Category:</label>
    <select id="category" name="category" class="form-select form-select-sm" required>
            <option value="">--Select a category--</option>
            <option value="Electronics">Electronics</option>
            <option value="Clothing">Clothing</option>
            <option value="Home and Garden">Home and Garden</option>
            <option value="Sports and Outdoors">Sports and Outdoors</option>
          </select>
  </div>

  <div class="mb-1">
    <div class="form-group col">
      <label for="countryOfOrigin" class="form-label">Country</label>
      <select class="form-select form-select-sm" id="countryOfOrigin" name="countryOfOrigin" required>
          <option>--Select Country--</option>
      </select>
    </div>
  </div>
  <div class="mb-1">
    <label class="form-label">Product Condition:</label><br>
    <div class="form-check form-check-inline">
      <input type="radio" id="new" name="condition" class="form-check-input" value="new" required>
      <label for="new" class="form-check-label">New</label>
    </div>
    <div class="form-check form-check-inline">
      <input type="radio" id="used" name="condition" class="form-check-input" value="used" required>
      <label for="used" class="form-check-label">Used</label>
    </div>
  </div>

  <div class="mb-1">
    <label for="price" class="form-label">Price:</label>
    <input type="number" id="price" name="price" class="form-control" step="0.01" required>
  </div>

  <div class="mb-1">
    <label for="quantity" class="form-label">Quantity:</label>
    <input type="number" id="quantity" name="quantity" class="form-control" required>
  </div>

  <button type="submit" class="btn btn-primary">Submit</button>
</form>

Index

<!DOCTYPE html>
<html>

<head>
  <title>Product Details</title>
  <?!= include('JavaScript'); ?>
  <!-- See JavaScript.html file -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.3.0/css/all.min.css" />
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet">
  <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css" />
  <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.4.1/css/responsive.dataTables.min.css" />
  <style>
    .btn-custom {
      font-size: 0.5rem;
      padding: 0.25rem 0.5rem;
    }

    a {
      text-decoration: none;
    }

    @import url('https://fonts.googleapis.com/css2?family=Noto+Sans+Thai&display=swap');

    * {
      font-family: 'Noto Sans Thai', sans-serif;
    }

    body{
      font-size:0.875rem;
    }
  </style>
</head>

<body>
  <div class="container-fluid">
    <div class="col-md-12">
      <nav class="navbar navbar-expand-lg bg-primary">
        <div class="container-fluid">
          <a class="navbar-brand text-white">Product</a>
          <button type="button" class="btn btn-warning btn-sm" onclick="btnaddData()">
  AddData
</button>
          <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
          <div class="collapse navbar-collapse" id="navbarSupportedContent">
            <ul class="navbar-nav me-auto mb-2 mb-lg-0">
            </ul>
            <form id="search-form" class="d-flex" role="search" onsubmit="handleSearchForm(this)">
              <input class="form-control form-control-sm me-1" type="search" name="searchText" placeholder="Search" required>
              <button class="btn btn-warning btn-sm" type="submit">Search</button>
            </form>
          </div>
        </div>
      </nav>

        <table id="dataTable" class="display compact responsive nowrap" style="width:100%"></table>

      <button type="button" class="btn btn-success btn-sm mb-4" onclick="getAllRecords()">Get ALL Data</button>

      <div class="container text-center">
        <a href="https://www.bpwebs.com/crud-operations-on-google-sheets-with-online-forms" target="_blank">Credit
          Bpwebs</a>
        <div class="copyright"></div>
      </div>
    </div>
  </div>

  <!-- Modal -->
  <div class="modal fade" id="myModal" data-bs-backdrop="static" data-bs-keyboard="false" tabindex="-1"
    aria-labelledby="staticBackdropLabel" aria-hidden="true">
    <div class="modal-dialog">
      <div class="modal-content">
        <div class="modal-header">
          <h1 class="modal-title fs-5" id="staticBackdropLabel">Product Details</h1>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
        </div>
        <div class="modal-body">
          <?!= include('FormProductDetails'); ?>
        </div>
      </div>
    </div>
  </div>
  <?!= include('SpinnerModal'); ?>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/responsive/2.4.1/js/dataTables.responsive.min.js"></script>
</body>

</html>

JavaScript

<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/gh/examblog/web/js/FtExamblog.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"
  integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous"></script>

<script>
  function btnaddData(){
     $('#myModal').modal('show');
     document.getElementById("ProductDetails").reset();
     document.getElementById("message").innerHTML = ""
  }
  // 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(){
    //$('#spinnerModal').modal('show');  
    preventFormSubmit();
    getLastTenRows();
    setFooter();
    createCountryDropdown();
  };  

//RETRIVE DATA FROM GOOGLE SHEET FOR COUNTRY DROPDOWN
  function createCountryDropdown() {
      google.script.run.withSuccessHandler(countryDropDown).getCountryList();
  }
  
//POPULATE COUNTRY DROPDOWNS
  function countryDropDown(values) { //Ref: https://stackoverflow.com/a/53771955/2391195
    var list = document.getElementById('countryOfOrigin');   
    for (var i = 0; i < values.length; i++) {
      var option = document.createElement("option");
      option.value = values[i];
      option.text = values[i];
      list.appendChild(option);
    }
  }    
  
  //HANDLE FORM SUBMISSION
  function handleFormSubmit(formObject) {
    $('#spinnerModal').modal('show');
    $('#myModal').modal('hide');
    google.script.run.withSuccessHandler(createTable).processForm(formObject);
    document.getElementById("ProductDetails").reset();
  }
  
  function deleteRecord(el) {
     Swal.fire({
  title: 'Are you sure?',
  text: "You won't be able to revert this!",
  icon: 'warning',
  showCancelButton: true,
  confirmButtonColor: '#3085d6',
  cancelButtonColor: '#d33',
  confirmButtonText: 'Yes, delete it!'
}).then((result) => {
  if (result.isConfirmed) {
    $('#spinnerModal').modal('show');
      var recordId = el.parentNode.parentNode.cells[2].innerHTML;
      google.script.run.withSuccessHandler(createTable).deleteRecord(recordId);
      document.getElementById("ProductDetails").reset();
  }
})
  }

  
  //GET LAST 10 ROWS
  function getLastTenRows (){
   google.script.run.withSuccessHandler(createTable).getLastTenRecords();
  }

  function editRecord(el){
    $('#spinnerModal').modal('show');
    let id = el.parentNode.parentNode.cells[2].innerHTML;
    google.script.run.withSuccessHandler(populateForm).getRecordById(id);
  }

  function populateForm(data){
    $('#spinnerModal').modal('hide');
    $('#myModal').modal('show');
    document.getElementById('recId').value = data[0][0];
    document.getElementById('name').value = data[0][1];
    document.getElementById('description').value = data[0][2];
    document.getElementById('category').value = data[0][3];
    document.getElementById('countryOfOrigin').value = data[0][4];
    document.getElementById(data[0][5]).checked = true;
    document.getElementById('price').value = data[0][6];
    document.getElementById('quantity').value = data[0][7];
    document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update Record [ID: "+data[0][1]+"]</div>";
  }

  //CREATE THE DATA TABLE
  function createTable(dataArray) {
    $('#spinnerModal').modal('hide');
    $('#myModal').modal('hide');
  if (dataArray && dataArray.length) {
    var result =
      "<table class='table table-sm' style='font-size:0.8em'>" +
      "<thead style='white-space: nowrap'>" +
      "<tr>" +
      "<th scope='col'>Delete</th>" +
      "<th scope='col'>Edit</th>" +
      "<th scope='col' style='display:none;'>ID</th>" + // Hide the ID column header
      "<th scope='col'>Product Name</th>" +
      "<th scope='col' style='display:none;'>Description</th>" +
      "<th scope='col'>Category</th>" +
      "<th scope='col'>Country of Origin</th>" +
      "<th scope='col'>Condition</th>" +
      "<th scope='col'>Price</th>" +
      "<th scope='col'>Quantity</th>" +
      "<th scope='col'>Last Update</th>" +
      "</tr>" +
      "</thead>";
    for (var i = 0; i < dataArray.length; i++) {
      result += "<tr>";
      result +=
        "<td><button type='button' class='btn btn-danger btn-custom deleteBtn' onclick='deleteRecord(this);'>Delete</button></td>";
      result +=
        "<td><button type='button' class='btn btn-warning btn-custom editBtn' onclick='editRecord(this);'>Edit</button></td>";
      for (var j = 0; j < dataArray[i].length; j++) {
        if (j === 0) {
          result +=
            "<td style='display:none;'>" + dataArray[i][j] + "</td>"; // Hide the ID column data
        }else if(j === 2){
          result +=
            "<td style='display:none;'>" + dataArray[i][j] + "</td>"; // Hide the Description column data
        } else {
          result += "<td>" + dataArray[i][j] + "</td>";
        }
      }
      result += "</tr>";
    }
    result += "</table>";
    var div = document.getElementById("dataTable");
    div.innerHTML = result;
    document.getElementById("message").innerHTML = "";
    $(document).ready(function() {
    $('#dataTable').DataTable({
        
       columnDefs: [
            {
                target: 4,
                visible: false,
                searchable: false,
            },
        ],
      destroy:true,
      order: [[2, 'desc']],
      searching:false,
    });
} );
  } else {
    var div = document.getElementById("dataTable");
    div.innerHTML = "Data not found!";
  }
}

//SEARCH RECORDS
function handleSearchForm(formObject) {
  $('#spinnerModal').modal('show');
  google.script.run.withSuccessHandler(createTable).searchRecords(formObject);
  document.getElementById("search-form").reset();
}


function getAllRecords(){
    $('#spinnerModal').modal('show');
    google.script.run.withSuccessHandler(createTable).getAllRecords();
  }
</script>

SpinnerModal

<div class="modal fade" id="spinnerModal" tabindex="-1" role="dialog" aria-labelledby="spinnerModalLabel"
  aria-hidden="true">
  <div class="modal-dialog modal-dialog-centered" role="document">
    <div class="modal-content">
      <div class="modal-body text-center">
        <div class="spinner-border mt-3" role="status">
          <span class="visually-hidden">Loading...</span>
        </div>
        <p class="mt-3">Loading...</p>
      </div>
    </div>
  </div>
</div>

const values = [[
formObject.recId,
formObject.name,
let values = [[
generateUniqueId(),
formObject.name,