#JP48 Download PDF Sheet Tertentu dan Range Tertentu

Download PDF Sesuai Sheet dan Range



1. Copy Spreadsheet (Klik disini)

2. Bagikan Sheet (Akses untuk Umum dan Hanya Dapat Melihat)


3. Klik menu Extensions/Ekstensi dan pilih Apps Script.


4. Terdapat file deafult Code.gs dan index.html

 5. Copy script di bawah ini ke Code.gs


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

//Source : www.javabitpro.com
//Youtube : Javabitpro
function doGet() {
  return HtmlService.createTemplateFromFile("index").evaluate()
  .setTitle("WebApp Form Title")
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

//DOWNLOAD TABEL 1 (SHEET 1)
function createDataUrl(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/1_2bIWt6H6NKrbH9oQu5vkQlO4M8vE_YoIgwFdJ_hVMM/export?format=pdf&portrait=false&gridlines=false&size=folio&scale=4&gid=0&range=B1:E12`;
  } 
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };

}

//DOWNLOAD TABEL 2 (SHEET 1)
function createDataUrl1(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/1_2bIWt6H6NKrbH9oQu5vkQlO4M8vE_YoIgwFdJ_hVMM/export?format=pdf&portrait=false&gridlines=false&size=folio&scale=4&gid=0&range=G1:I12`;
  } 
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };

}


//DOWNLOAD (HANYA SHEET 2)
function createDataUrl2(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/1_2bIWt6H6NKrbH9oQu5vkQlO4M8vE_YoIgwFdJ_hVMM/export?format=pdf&portrait=false&gridlines=false&size=folio&scale=4&gid=1997245024&range=A1:I16`;
  } 
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };

}

//DOWNLOAD (HANYA SHEET 3 POTRAIT)
function createDataUrl3(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/1_2bIWt6H6NKrbH9oQu5vkQlO4M8vE_YoIgwFdJ_hVMM/export?format=pdf&portrait=true&gridlines=false&size=folio&scale=4&gid=2097590423&range=A1:E26`;
  } 
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };

}


6. Edit dan sesuiakan ID SPREADSHEET , ID SHEET dan RANGE area yang akan di jadikan PDF.



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

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.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
  </head>
  <body>
         <button type="button" class="btn btn-primary mt-4" onclick="someFunc1()">Download PDF Tabel 1 (Sheet 1)</button>
          <button type="button" class="btn btn-primary mt-4" onclick="someFunc2()">Download PDF Tabel 2 (Sheet 1)</button>
          <br>
          <button type="button" class="btn btn-primary mt-4" onclick="someFunc3()">Download PDF Tabel 1 (Hanya Sheet 2)</button>
          <br>
          <button type="button" class="btn btn-primary mt-4" onclick="someFunc4()">Download PDF (Hanya Sheet 3 POTRAIT)</button>
  </body>
</html>
<script>
//------------------------------------------//
  function someFunc1() {
  download('pdf');
   
}

function someFunc2() {
  download1('pdf');
  
}
function someFunc3() {
  download2('pdf');
  
}
function someFunc4() {
  download3('pdf');
  
}


//------------------------------------------//
//DOWNLOAD TABEL 1 (SHEET 1)
function download(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        if (data && filename) {
          const a = document.createElement("a");
          document.body.appendChild(a);
          a.download = filename;
          a.href = data;
          a.click();
        }
      })
      .createDataUrl(type);
  }


//DOWNLOAD TABEL 2 (SHEET 1)
function download1(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        if (data && filename) {
          const a = document.createElement("a");
          document.body.appendChild(a);
          a.download = filename;
          a.href = data;
          a.click();
        }
      })
      .createDataUrl1(type);
  }

//DOWNLOAD (HANYA SHEET 2)
function download2(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        if (data && filename) {
          const a = document.createElement("a");
          document.body.appendChild(a);
          a.download = filename;
          a.href = data;
          a.click();
        }
      })
      .createDataUrl2(type);
  }

  //DOWNLOAD (HANYA SHEET 3 POTRAIT)
function download3(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        if (data && filename) {
          const a = document.createElement("a");
          document.body.appendChild(a);
          a.download = filename;
          a.href = data;
          a.click();
        }
      })
      .createDataUrl3(type);
  }
  </script>
  <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>

8. Klik ikon Save.


9. Klik tombol Terapkan/Deploy dan pilih Deployment baru/New Deployment.


10. Pastikan jenisnya ada (1)Aplikasi Web , Hak Akses (2) Siapa saja/Anyone, lalu pilih (3) Terapkan.


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

Hasilnya.

SELESAI !!!


Previous Post Next Post

Promo