#JP49 CRUD dan Print Template by ID

Create, Edit, Update, Delete dan Print Template by ID pada Tabel

1. Copy Spreadsheet (Klik disini)

2. Copy Template (Klik disini)

3. Masuk ke akun Google Drive, lalu buat folder "PDF" untuk menyimpan hasil PDF.

4. Bagikan folder PDF.

5. Kembali pada Spreadsheet. Pada Sheet Data terdapat beberapa kolom data.

6. Pada sheet Provinsi terdapat 1 kolom yang berisi nama-nama provinsi 

7. Pilih menu Ekstensi/Extensions lalu pilih Apps script.

8. Pada lembar kerja Apps script terdapat 6 file yang harus di isi dengan script.

9. Pada Layanan, pastikan terdapat 3 API yang aktif yaitu :

1. Sheet API

2. Slide API

3. Drive API

10. Copy dan pastekan script di bawah ini ke Code.gs

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

/*URL: https://javabitpro.com*/

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

function globalVariables(){ 
  var varArray = {
    spreadsheetId   : '10yf9yf6YHEXL7_f8o-Nswk9zcwdN0EdB0XNG4WY9Kf4', //** CHANGE !!!
    dataRage        : 'Data!A2:H',                                    //** CHANGE !!!
    idRange         : 'Data!A2:A',                                    //** CHANGE !!!
    lastCol         : 'H',                                            //** CHANGE !!!
    insertRange     : 'Data!A1:H1',                                   //** CHANGE !!!
    sheetID         : '0'                                             //** CHANGE !!! 
  };
  return varArray;
}

/* PROCESS FORM */
function processForm(formObject){  
  if(formObject.RecId && checkID(formObject.RecId)){
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId));
  }else{ 
    appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); 
  }
  return getLastTenRows()
}


/* GET FORM VALUES AS AN ARRAY */
function getFormValues(formObject){
  if(formObject.RecId && checkID(formObject.RecId)){
    let slidefile = DriveApp.getFileById('1qast8mdM4SOYleoWy-OjAY4wtdWhKZEdGnvci1mn2FU') //ID Google Slide(Template)
    let pdffolder = DriveApp.getFolderById('18tLp1BlGbWO-V54wDdkZtRFIjXmiSNVh'); // ID Folder PDF
    let copyFile = slidefile.makeCopy(formObject.name);
    let copyId = copyFile.getId()
    let copyDoc = SlidesApp.openById(copyId); 
     copyDoc.replaceAllText('{RecId}',formObject.RecId)
     copyDoc.replaceAllText('{Nama}',formObject.name)
     copyDoc.replaceAllText('{JenisKelamin}',formObject.gender)
     copyDoc.replaceAllText('{TanggalLahir}',formObject.dateOfBirth)
     copyDoc.replaceAllText('{Email}',formObject.email)
     copyDoc.saveAndClose()
      let pdffile = pdffolder.createFile(copyFile.getAs("application/pdf"));   
      let pdfurl = pdffile.getUrl()
      copyFile.setTrashed(true);
    
    var values = [[formObject.RecId.toString(),
                  formObject.name,
                  formObject.gender,
                  formObject.dateOfBirth,
                  formObject.email,
                  formObject.phone,
                  formObject.country,
                  pdfurl]];
  }else{
    var values = [[new Date().getTime().toString(),
                  formObject.name,
                  formObject.gender,
                  formObject.dateOfBirth,
                  formObject.email,
                  formObject.phone,
                  formObject.country]];
  }
  return values;
}

/* 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();
}

/* 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 'Data!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 = 'Data!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;
}

/*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();
}

Keterangan :

Pada line 12 : Silahkan ganti ID dengan ID Spreadsheet anda.

Pada line 36 : Silahkan ganti ID dengan ID Google Slide (Template) yang sudah di copy (pada langkah nomor 2 di atas)

Pada line 37 : Silahkan ganti ID dengan ID Folder PDF yang sudah di buat (pada langkah nomor 3 di atas)

11. Copy dan pastekan script di bawah ini ke index.html

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
        <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
        <?!= include('CSS'); ?> <!-- See CSS.html file -->
    </head>
    <body onload="createCountryDropdown()">
        <div class="container-fluid">
                <div class="col-lg-12 mb-4">
                  <?!= include('DataTable'); ?> <!-- See DataTable.html File -->    
        </div>
<!-- Modal -->
<div class="modal fade" id="staticBackdrop" 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">
        <h5 class="modal-title" id="staticBackdropLabel">CRUD</h5>
        <button type="button" id="btnclose" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <?!= include('Form'); ?> 
      </div>
    </div>
  </div>
</div>
    </body>
</html>

12. Copy dan pastekan script di bawah ini ke JavaScript.html

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js" integrity="sha384-7+zCNj/IqJ95wo16oMtfsKbZ9ccEh31eOz1HGyDuCQ6wgnyJNSYdrPa03rtR1zdB" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.min.js" integrity="sha384-QJHtvGhmr9XOIpI6YVutG+2QOK9T+ZnN4kzFN1RtK3zEFEIsxhlmWl5/YESvpZ13" crossorigin="anonymous"></script>
<script>
  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); 
  
  function functionInit(){  
    preventFormSubmit();
    getLastTenRows();
  };      

  function handleFormSubmit(formObject) {
    document.getElementById('btn1').style.display="none"
    document.getElementById('btn2').style.display="block"
    google.script.run.withSuccessHandler(createTable).processForm(formObject);
    
  }
  
   function getLastTenRows (){
   google.script.run.withSuccessHandler(createTable).getLastTenRows();
  }
  
  
  function getAllData(){
    google.script.run.withSuccessHandler(createTable).getAllData();
  }
  

  function createTable(dataArray) {
    if(dataArray){
      document.getElementById("myForm").reset();
      document.getElementById('btn2').style.display="none"
      document.getElementById('btn1').style.display="block"
      document.getElementById('btnclose').click()
      var result = "<table class='table table-bordered border-primary table-striped border border-dark ' style='font-size:0.875rem'>"+
                   "<thead style='blue-space: nowrap'>"+
                     "<tr>"+  
                      "<th scope='col'>Delete</th>"+
                      "<th scope='col'>Chek</th>"+
                      "<th scope='col'>ID</th>"+
                      "<th scope='col'>Nama</th>"+
                      "<th scope='col'>Jenis Kelamin</th>"+
                      "<th scope='col'>Tanggal Lahir</th>"+
                      "<th scope='col'>Email</th>"+
                      "<th scope='col'>No Telp</th>"+
                      "<th scope='col'>Provinsi</th>"+
                      "<th scope='col'>PDF</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);' data-bs-toggle='modal' data-bs-target='#staticBackdrop'>Chek</button></td>";
          for(var j=0; j<dataArray[i].length; j++){
             // result += "<td>"+dataArray[i][j]+"</td>";
              result += '<td>'+ (dataArray[i][j]= /www |http/.test(dataArray[i][j]) ? '<a class="btn btn-primary text-white btn-xs" target="_blank" href='+dataArray[i][j] + '><i class="far fa-arrow-alt-circle-down"></i> PDF</a>': 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 = "Data not found!";
    }
  }

  //DELETE DATA
  function deleteData(el) {
    var result = confirm("Anda Yakin?");
    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; 
    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] =='Laki-Laki'? '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() {
      google.script.run.withSuccessHandler(countryDropDown).getDropdownList("Provinsi!A1:A195");
  }
  
  //POPULATE COUNTRY DROPDOWNS
  function countryDropDown(values) { 
    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>

13. Copy dan pastekan script di bawah ini ke DataTable.html

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

<p class="h4 my-4 text-center">Input Data Informasi</p>
        <!-- Button trigger modal -->
<button type="button" class="btn btn-primary btn-sm" data-bs-toggle="modal" data-bs-target="#staticBackdrop">
  Input Data
</button>
<div id="dataTable" class="table-responsive">
  <!-- The Data Table is inserted here by JavaScript -->
</div>
<button type="button" class="btn btn-success btn-sm" onclick="getAllData()">Lainnya</button>
<!--Footer -->
  <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>

14. Copy dan pastekan script di bawah ini ke Form.html

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

<!-- ## 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)">
  <div id="message"></div>
  <input type="text" id="RecId" name="RecId" value="" style="display: none">
  <div class="row">
    <div class="col-md-6 mb-2">
      <label for="name" >Nama</label>
      <input type="text" class="form-control" id="name" name="name" placeholder="Nama" required>
    </div>
    <div class="col-md-6 mb-2">
      <p>Jenis Kelamin</p>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" name="gender" id="male" value="Laki-Laki">
        <label class="form-check-label" for="male">Laki-Laki</label>
      </div>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" name="gender" id="female" value="Perempuan">
        <label class="form-check-label" for="female">Perempuan</label>
      </div>
    </div>
    <div class="col-md-6 mb-2">
      <label for="dateOfBirth">Tanggal Lahir</label>
      <input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
    </div>

    <div class="col-md-6 mb-2">
      <label for="email">Email</label>
      <input type="email" class="form-control" id="email" name="email" placeholder="Email">
    </div>

    <div class="col-md-6 mb-2">
      <label for="phone">No. Telp</label>
      <input type="tel" class="form-control" id="phone" name="phone" placeholder="No. Telp">
    </div>
    <div class="col-md-6 mb-2">
      <label for="exampleFormControlSelect1">Provinsi</label>
      <select class="form-control" id="country" name="country">
          <option selected disabled>Provinsi</option>
        </select>
    </div>
  </div>
  <button type="submit" id="btn1" class="btn btn-primary">Simpan</button>
  <!-- ##<input class="btn btn-secondary" type="reset" value="Reset"> -->
  <button class="btn btn-primary" id="btn2" style="display:none" type="button" disabled>
  <span class="spinner-grow spinner-grow-sm" role="status" aria-hidden="true"></span>
  Loading...
</button>
</form>

15. Copy dan pastekan script di bawah ini ke CSS.html

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css" integrity="sha512-MV7K8+y+gLIBoVD59lQIYicR65iaqukzvf/nwasF0nqhPay5w/9lJmVM2hMDcnK1OnMGCdVK+iQrJ7lzPJQd1w==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link href="https://fonts.googleapis.com/css2?family=Noto+Sans+Thai&display=swap" rel="stylesheet">
<style>
  body{
    font-family: 'Noto Sans Thai', sans-serif;
  }
.btn-group-xs > .btn, .btn-xs {
  padding: .25rem .4rem;
  font-size: .875rem;
  line-height: .5;
  border-radius: .2rem;
}
</style>

16. Tekan ikon Save.

17. Klik tombol berwarna biru Deploy/Terapkan lalu pilih Deployment baru/New Deployment.

18. Pastikan jenisnya adalah Aplikasi web dan hak akses Siapa saja/Anyone.

19. Klik Url atau salin Url yang sudah di Deploy.

SELESAI !!!