#JP45 CRUD , TABEL, DOWNLOAD EXCEL Menggunakan Apps Script

CRUD , TABEL, DOWNLOAD EXCEL Menggunakan Apps Script




*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

1. Copy Spreadsheet (Klik di sini)

2. Buatlah lembar kerja Apps Script dengan cara klik Ekstnesi - pilih Apps Script


3. Terdapat beberapa file default yaitu
  • serverSideFunc.gs
  • main.html
  • css.html
  • addForm.html
  • editForm.html


4. Copy dan pastekan script di bawah ke serverSideFunc.gs

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

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

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getData(){
  const jsData = MyIMCLibrary.createMyJSONdata('Customers','A2:G2','A5:G')
  console.log(jsData)
  return jsData
}

function editCustomerById(id,customerInfo){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Customers");
  const custIds = ws.getRange(5,1,ws.getLastRow()-4,1).getDisplayValues().map(r => r[0].toString().toLowerCase());
  const posIndex = custIds.indexOf(id.toString().toLowerCase());
  const rowNumber = posIndex === -1 ? 0 : posIndex + 5
   
  ws.getRange(rowNumber,2,1,6).setValues([[
                                        customerInfo.firstName,
                                        customerInfo.lastName,
                                        customerInfo.diagGroup,
                                        customerInfo.admitDate,
                                        customerInfo.biRegist,
                                        customerInfo.biResult
                                        ]])
  return true;

};

function deleteRecord(props){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName('Customers')
  const idCellMatched = ws.getRange("A5:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()
  if(idCellMatched === null) throw new Error("No matching record")
  const recordRowNumber = idCellMatched.getRow()
  ws.deleteRow(recordRowNumber)
  return true

}


function addRecord(testName,lastName,diagGroup,admitDate,biRegist,biResult){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName('Customers') 
  const newId = MyIMCLibrary.createNewId()
  ws.appendRow([
                newId,
                testName,
                lastName,
                diagGroup,
                admitDate,
                biRegist,
                biResult
                ])
  console.log(newId)
  return newId
}

5. Copy dan pastekan script di bawah ini ke main.html

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://unpkg.com/tabulator-tables@5.2.3/dist/css/tabulator.min.css" rel="stylesheet">
    <?!= include("css"); ?>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
  </head>
  <body>
  <!-- LOADING ********************************  -->
  <div id="loading" class="d-flex flex-column justify-content-center align-items-center invisible">
      <div class="spinner-grow text-primary" role="status" style="width: 5rem; height: 5rem;">
        <span class="visually-hidden">Loading...</span>
      </div>
  </div>
  <div class="container" id="app">
    <center><h1 class="mt-3">Program Pencatatan Pasien</h1></center>
    <div class="row"> 
      <div class="col mt-3">
        <label class="form-label">Cari dengan Nama</label>
        <input type="text" class="form-control" id="search-input" >
      </div>
      <div class="col mt-3">
        <label class="form-label">Diagnosis</label>
        <select class="form-select" id="filter-diagGroup-input">
          <option value="" selected>Semua</option>
          <option value="Sakit Kepala">Sakit Kepala</option>
          <option value="Sakit Perut">Sakit Perut</option>
          <option value="Greges">Greges</option>
          <option value="Sakit Panu">Sakit Panu</option>
        </select>
      </div>
    </div>
    <button type="button" class="btn btn-primary mt-4" id="open-form-record-button">Pendaftaran</button>
    <button type="button" class="btn btn-primary mt-4" id="download-excel-button">Download</button>
    <div class=" mt-2" id="data-table"></div>

    <?!= include("addForm"); ?>
    <?!= include("editForm"); ?>

  </div>
  
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js" integrity="sha384-pprn3073KE6tl6bjs2QrFaJGz5/SUsLqktiwsUTF55Jfv3qYSDhgCecCxMW52nD2" crossorigin="anonymous"></script>
    <script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.2.3/dist/js/tabulator.min.js"></script>
    <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
    <script type="text/javascript" src="https://oss.sheetjs.com/sheetjs/xlsx.full.min.js"></script>
    <script type="text/javascript" src="https://firebasestorage.googleapis.com/v0/b/free-program.appspot.com/o/CRUD%20%20BTS%20ver%2010%20-%20%E0%B9%81%E0%B8%88%E0%B8%81%E0%B8%9F%E0%B8%A3%E0%B8%B5.txt?alt=media&token=aca7f7fe-b122-4635-8ba0-53d33fada65a"></script> 

<script>
const elements = {}
function loadingStart(){
        document.getElementById("loading").classList.remove("invisible");
      };

      function loadingEnd(){
          document.getElementById("loading").classList.add("invisible");
      };
       
      function pageLoad(){
        loadingStart()
        
        loadData()

      };

      function loadData(){
          google.script.run.withSuccessHandler((jsData)=>{
          elements.table = new Tabulator("#data-table", {
            height:"100%",
            data: jsData,  
            reactiveData:true,  
            layout:"fitColumns",  
            pagination:"local",
            paginationSize:10,
            paginationSizeSelector:[ 10, 15, 20],
            movableColumns:true,
            paginationCounter:"rows",
            index:"customerId",   

            columns:[  
              {title:"Pasien ID", field:"customerId",visible:true, download:true},  // ตรง field ตัวเลขต้องตรงกัน
              {title:"Nama", field:"firstName"},
              {title:"Nama Keluarga", field:"lastName"},
              {title:"Jenis Keluhan", field:"diagGroup", headerFilter:true },
              {title:"Tanggal Lahir", field:"admitDate",headerFilter:true},
              {title:"Golongan Darah", field:"biRegist"},
              {title:"Gejala", field:"biResult"},
               

              ],
          })


          // DELETE RECORD 
          elements.table.on("rowDeleted", function(row){
              //row - row component
              console.log(row)
              console.log(row._row.data.customerId) // id ที่ต้องการเข้าถึง

              const id = row._row.data.customerId

              google.script.run
                .withSuccessHandler(()=>{
                  loadingEnd()
                  deleteSuccessAlert()
                  })
                .withFailureHandler((er)=>{
                  })
                .deleteRecord({id:id })
          })

          
          elements.table.on("rowClick", function(e, row){
            const custId = row.getData().customerId
            const firstName = row.getData().firstName
            const lastName = row.getData().lastName
            const diagGroup = row.getData().diagGroup
            const admitDate = row.getData().admitDate
            const biRegist = row.getData().biRegist
            const biResult = row.getData().biResult

            showModalEditForm(custId,firstName,lastName,diagGroup,admitDate,biRegist,biResult)

          });

          loadingEnd()

        }).withFailureHandler((er)=>{}).getData()

      };

    function setHeaderFilterDiagGroup(e){
        elements.table.setHeaderFilterValue("diagGroup", e.target.value)

    };

    function showModalEditForm(custId,firstName,lastName,diagGroup,admitDate,biRegist,biResult){
      const myModalEditForm = new bootstrap.Modal(document.getElementById('myModal-edit-form'), {keyboard: false })

      document.getElementById('customer_id_edit').value = custId
      document.getElementById('first_name_edit').value = firstName
      document.getElementById('last_name_edit').value = lastName
      document.getElementById('diag_group_edit').value = diagGroup
      document.getElementById('admit_date_edit').value = admitDate
      document.getElementById('bi_regist_edit').value = biRegist
      document.getElementById('bi_result_edit').value = biResult

      myModalEditForm.show()

    };

    function openFormRecord(){
      const myModalRegist = new bootstrap.Modal(document.getElementById('myModal-add-form'), {keyboard: false })
      myModalRegist.show()

    };

    function downloadExcel(){
      // loadingStart()
      elements.table.download("xlsx", "data.xlsx", {sheetName:"Daftar pasien"},{
          documentProcessing:function(workbook){
              workbook.Props = {
                  Title: "SheetJS Tutorial",
                  Subject: "Test",
                  CreatedDate: new Date(2017,12,19)
              };
              return workbook;
          }
          
      })
      
    };

    function searchData(e){
        elements.table.setFilter(
        [[
          {field:"firstName",type: "like",value: e.target.value},
          {field:"lastName",type: "like",value: e.target.value},
          
          ]]
        )       
    };

    // ADD RECORD **************************************
    function addRecord(){
      loadingStart()
      
      const firstName = document.getElementById('first_name_add').value  
      const lastName = document.getElementById('last_name_add').value 
      const diagGroup = document.getElementById('diag_group_add').value 
      const admitDate = document.getElementById('admit_date_add').value 
      const biRegist = document.getElementById('bi_regist_add').value 
      const biResult = document.getElementById('bi_result_add').value    

      google.script.run
        .withSuccessHandler((newId)=>{
            elements.table.addData([{customerId:newId, firstName:firstName , lastName:lastName , diagGroup:diagGroup, admitDate:admitDate , biRegist:biRegist, biResult:biResult } ], true);

            document.getElementById('first_name_add').value  = ""
            document.getElementById('last_name_add').value = ""
            document.getElementById('diag_group_add').value = ""
            document.getElementById('admit_date_add').value = ""
            document.getElementById('bi_regist_add').value = ""
            document.getElementById('bi_result_add').value = ""    

            loadingEnd()
            addCompleteAlert()
          })
        .withFailureHandler((er)=>{
            console.log("Error Adding")
          })
        .addRecord(firstName,lastName,diagGroup,admitDate,biRegist,biResult)

    };

    // EDIT RECORD  **********************************
    function editRecord(){
      loadingStart()
      const custId = document.getElementById('customer_id_edit').value
      const firstName = document.getElementById('first_name_edit').value  
      const lastName = document.getElementById('last_name_edit').value 
      const diagGroup = document.getElementById('diag_group_edit').value 
      const admitDate = document.getElementById('admit_date_edit').value 
      const biRegist = document.getElementById('bi_regist_edit').value 
      const biResult = document.getElementById('bi_result_edit').value    
      
      elements.table.updateData([{customerId:custId, firstName:firstName, lastName:lastName, diagGroup:diagGroup, admitDate:admitDate, biRegist:biRegist, biResult:biResult}]);

      const customerInfo = {};
          customerInfo.firstName = document.getElementById("first_name_edit").value;
          customerInfo.lastName = document.getElementById("last_name_edit").value;
          customerInfo.diagGroup = document.getElementById('diag_group_edit').value 
          customerInfo.admitDate = document.getElementById('admit_date_edit').value 
          customerInfo.biRegist = document.getElementById('bi_regist_edit').value 
          customerInfo.biResult = document.getElementById('bi_result_edit').value    
          
      const id = document.getElementById("customer_id_edit").value;

      google.script.run.withSuccessHandler(function(res){
          loadingEnd()
          editCompleteAlert()
      }).editCustomerById(id,customerInfo)

    };

    // DELETE RECORD  **********************************
    function deleteRecord(){
      loadingStart()
      
      const id = document.getElementById('customer_id_edit').value
      elements.table.deleteRow(id) 
      
    };

    // ALERT ***********************************
    function emptyAlert(){
      Swal.fire({
        icon: 'error',
        title: 'Oops...',
        text: 'Harap lengkapi semua kolom',
      })
    };

    function deleteSuccessAlert(){
      Swal.fire(
            'Deleted!',
            'success'
      )
    };

    function confirmDeleteAlert(){
      Swal.fire({
          title: 'Apa kamu yakin?',
          icon: 'warning',
          showCancelButton: true,
          confirmButtonText: 'Ya, Hapus data ini!'
        }).then((result) => {
          if (result.isConfirmed) {
            deleteRecord();
          }
        })
    };

    function addCompleteAlert(){
      Swal.fire(
            'Added!',
            'success'
      )
    };

    function editCompleteAlert(){
      Swal.fire(
            'Edited!',
            'success'
      )
    };

    function clickEventHandler(e) {
      if (e.target.matches("#open-form-record-button")){
        openFormRecord();
      }
      if (e.target.matches("#add-record-button")){
        if( document.getElementById("first_name_add").value == "" || document.getElementById("last_name_add").value == "" || document.getElementById("diag_group_add").value == "" || document.getElementById("admit_date_add").value == "" || document.getElementById("bi_regist_add").value == "" || document.getElementById("bi_result_add").value == ""){
          emptyAlert()
        } else {
          addRecord(e);
        }
      }
      if (e.target.matches("#edit-record-button")){
        editRecord(e);
      }
      if (e.target.matches("#delete-record-button")){
         confirmDeleteAlert()
      }
      if (e.target.matches("#download-excel-button")){
        downloadExcel(e);
      }   
    };

      document.getElementById("app").addEventListener("click",clickEventHandler);
      document.getElementById("app").addEventListener("input",inputEventHandler);
      document.addEventListener('DOMContentLoaded',pageLoad)
     </script>
  </body>
</html>
<style>.footer,.generic-footer{margin-bottom:98px}@media (min-width:52px){.footer,.generic-footer{margin-bottom:78px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:56px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:0}}.disclaimer{position:fixed;z-index:9999999;bottom:0;right:0;border-top:2px solid #ff5c62;text-align:center;font-size:14px;font-weight:400;background-color:#fff;padding:5px 10px 5px 10px}.disclaimer a:hover{text-decoration:underline}@media (min-width:52px){.disclaimer{text-align:right;border-left:2px solid red;border-top-left-radius:10px}}@media (min-width:1920px){.disclaimer{width:20%}}</style><div class="disclaimer">Version.01.05.22 @Copyright <a title="https://www.javabitpro.com/" target="_blank" href="https://www.javabitpro.com/" style="color: black;"><b>www.javabitpro.com</b></a></div>

6. Copy dan pastekan script di bawah ini ke css.html

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

<!-- CUSTOM CSS  -->
<style>
    body {
      font-family: "Sarabun"
    }
    .nav-link {
      cursor:pointer;
    }
    #loading{
      position: fixed;
        top:0 ;
        left:0;
        z-index:10000;
      width:100vw;
      height:100vh;
      background-color: rgba(255,255,255,0.9);
    }
    label {
        display: block;
        font: 1rem 'Fira Sans', sans-serif;
    }
    input,
    label {
        margin: .4rem 0;
    }

    .tabulator .tabulator-header .tabulator-col .tabulator-header-filter {
        position: relative;
        box-sizing: border-box;
        margin-top: 2px;
        width: 100%;
        text-align: center;
        display: none;
    }

</style>

7.Copy dan pastekan script di bawah ini ke addForm.html

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

<!-- MODAL FORM ADD RECORD ****************************************************  -->
<div class="modal" tabindex="-1" id="myModal-add-form" >
    <div class="modal-dialog modal-lg">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title">Daftar Pasien</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
        </div>
        <div class="modal-body">
            <div class="row mt-2">
                <div class="col">
                  <label class="form-label">Nama</label>
                  <input type="text" class="form-control" id="first_name_add">
                </div>
                <div class="col">
                  <label class="form-label">Nama Keluarga</label>
                  <input type="text" class="form-control" id="last_name_add">
                </div>
                <div class="col">
                  <label class="form-label">Jenis Keluhan</label>
                  <select class="form-select" id="diag_group_add">
                    <option selected></option>
                    <option value="Sakit Kepala">Sakit Kepala</option>
                    <option value="Sakit Perut">Sakit Perut</option>
                    <option value="Greges">Greges</option>
                    <option value="Sakit Panu">Sakit Panu</option>
                  </select>
                </div>
            </div>
            <div class="row mt-2">
                <div class="col">
                  <label class="form-label">Tanggal Lahir Pasien</label>
                  <input type="date" class="form-control" id="admit_date_add">
                </div>
                <div class="col">
                  <label class="form-label">Golongan Darah</label>
                  <input type="text" class="form-control" id="bi_regist_add">
                </div>
                <div class="col">
                  <label class="form-label">Gejala</label>
                  <select class="form-select"  id="bi_result_add">
                    <option selected></option>
                    <option value="Membaik">Membaik</option>
                    <option value="Tidak Ada Perubahan">Tidak Ada Perubahan</option>
                    <option value="Memburuk">Memburuk</option>
                  </select>
                </div>
            </div>
              
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-primary mt-4" id="add-record-button">Simpan</button>
          
        </div>
      </div>
    </div>
</div>
 

8. Copy dan pastekan script di bawah ini ke editForm.html

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

<!-- MODAL FORM EDIT  *********************************************************** -->
<div class="modal" tabindex="-1" id="myModal-edit-form" >
  <div class="modal-dialog modal-lg">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">Informasi Pasien</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <label class="form-label">ID</label>
        <input type="text" class="form-control" id="customer_id_edit" disabled>
        <div class="row mt-2">
            <div class="col">
              <label class="form-label">Nama</label>
              <input type="text" class="form-control" id="first_name_edit"> 
            </div>
            <div class="col">
              <label class="form-label">Nama Keluarga</label>
              <input type="text" class="form-control" id="last_name_edit">
            </div>
            <div class="col">
              <label class="form-label">Jenis Keluhan</label>
              <select class="form-select"  id="diag_group_edit">
                <option selected></option>
                <option value="Sakit Kepala">Sakit Kepala</option>
                <option value="Sakit Perut">Sakit Perut</option>
                <option value="Greges">Greges</option>
                <option value="Sakit Panu">Sakit Panu</option>
              </select>
            </div>
        </div>
        <div class="row mt-2">
            <div class="col">
              <label class="form-label">Tanggal Lahir Pasien</label>
              <input type="text" class="form-control" id="admit_date_edit">
            </div>
            <div class="col">
              <label class="form-label">Golongan Darah</label>
              <input type="text" class="form-control" id="bi_regist_edit">
            </div>
            <div class="col">
              <label class="form-label">Gejala</label>
              <select class="form-select"  id="bi_result_edit">
                <option selected></option>
                <option value="Membaik">Membaik</option>
                <option value="Tidak Ada Perubahan">Tidak Ada Perubahan</option>
                <option value="Memburuk">Memburuk</option>
              </select>
            </div>
        </div>

      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-primary mt-4" data-bs-dismiss="modal" style="--bs-btn-padding-y: 0.4rem; --bs-btn-padding-x: 2rem; --bs-btn-font-size: 1rem;" id="edit-record-button"> Edit </button>
        <button type="button" class="btn btn-danger mt-4" data-bs-dismiss="modal" id="delete-record-button">Hapus</button>
        
      </div>
    </div>
  </div>
</div>

9. Klik ikon Save


10. Klik tombol Deploy - New deployment


11. Pastikan typenya adalah Web app dan hak akses Anyone lalu klik Deploy


12. Copy atau klik url yang sudah di Deploy





SELESAI!!


Previous Post Next Post

Promo