Membuat / men-generate file pdf dari semua data di google spreadsheet tanpa add-on / autocrat

tool

google spreadsheet : https://docs.google.com/spreadsheets/

google apps script : https://script.google.com/

copy spreadsheet

https://docs.google.com/spreadsheets/d/1iGn-it6FtzFEwwfmtN3SuIZhhFSN3tCSG1gU9Mv84GA/copy

copy template

https://docs.google.com/document/d/1phCyvNHwA2L5fFBgMMKiUwTT28Yksa19EBk_aj6BA9g/copy

folder hasil file pdf

https://drive.google.com/drive/folders/12eNgg6Op1cfRXTzrsurGgvn_dvo4B3OM?usp=sharing

script :

https://script.google.com/home/projects/1cIqJdQtHR-ibNqkAmG13XF5J4QFu7HKrjVynKxuGb3SzwVJfsy1no2xl/edit

function createPDFFromTemplate() {
  var spreadsheetId = 'xxx'; // ID dari Google Spreadsheet Anda
  var sheetName = 'Sheet1'; // Nama sheet di Google Spreadsheet
  var templateId = 'xxx'; // ID dari Template Google Documents
  
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  
  var headerRow = data[0];
  
  for (var i = 1; i < data.length; i++) {
    var rowData = data[i];
    
    var dataObject = {};
    
    for (var j = 0; j < headerRow.length; j++) {
      var columnHeader = headerRow[j];
      dataObject[columnHeader] = rowData[j];
    }
    
    var template = DriveApp.getFileById(templateId);
    var newFile = template.makeCopy();
    var newDoc = DocumentApp.openById(newFile.getId());
    var body = newDoc.getBody();
    
      body.replaceText('{nama}', dataObject['Nama']);
      body.replaceText('{email}', dataObject['Email']);
      body.replaceText('{no hp}', dataObject['No HP']);
      body.replaceText('{bulan}', dataObject['Bulan']);
      body.replaceText('{tahun}', dataObject['Tahun']);
      body.replaceText('{nominal}', dataObject['Nominal']);
      body.replaceText('{terbilang}', terbilang(dataObject['Nominal']));
      newDoc.saveAndClose();
      
    var newName = dataObject['Nama'] + '-' + dataObject['Bulan'] + '-' + dataObject['Tahun'] + '.pdf';
    newFile.setName(newName);
    
    var pdf = DriveApp.getFileById(newFile.getId()).getAs('application/pdf');
    
    var folder = DriveApp.getFolderById('xxx'); // ID dari folder tujuan penyimpanan file PDF
   
    var savedFile = folder.createFile(pdf);
    
    var savedFileId = savedFile.getId();
    var urlFileId = savedFile.getUrl();
    var targetCell = sheet.getRange(i + 1, 8); // Menggunakan kolom untuk menyimpan ID File
    var targetCellURL = sheet.getRange(i + 1, 9); // Menggunakan kolom untuk menyimpan URL FILE
    targetCell.setValue(savedFileId);
    targetCellURL.setValue(urlFileId);
    
    DriveApp.getFileById(newFile.getId()).setTrashed(true);

  }
     SpreadsheetApp.getUi().alert("Proses selesai!");


}


function terbilang(Nilai){
    var bilangan=Nilai.toString();
    var kalimat="";
    var angka   = new Array('0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');
    var kata    = new Array('','Satu','Dua','Tiga','Empat','Lima','Enam','Tujuh','Delapan','Sembilan');
    var tingkat = new Array('','Ribu','Juta','Milyar','Triliun');
    var panjang_bilangan = bilangan.length;
     
    /* pengujian panjang bilangan */
    if(panjang_bilangan > 15){
        kalimat = "Diluar Batas";
    }else{
        for(i = 1; i <= panjang_bilangan; i++) {
            angka[i] = bilangan.substr(-(i),1);
        }
         
        var i = 1;
        var j = 0;
         
        /* mulai proses iterasi terhadap array angka */
        while(i <= panjang_bilangan){
            subkalimat = "";
            kata1 = "";
            kata2 = "";
            kata3 = "";
             
            if(angka[i+2] != "0"){
                if(angka[i+2] == "1"){
                    kata1 = "Seratus";
                }else{
                    kata1 = kata[angka[i+2]] + " Ratus";
                }
            }
             
            if(angka[i+1] != "0"){
                if(angka[i+1] == "1"){
                    if(angka[i] == "0"){
                        kata2 = "Sepuluh";
                    }else if(angka[i] == "1"){
                        kata2 = "Sebelas";
                    }else{
                        kata2 = kata[angka[i]] + " Belas";
                    }
                }else{
                    kata2 = kata[angka[i+1]] + " Puluh";
                }
            }
             
            if (angka[i] != "0"){
                if (angka[i+1] != "1"){
                    kata3 = kata[angka[i]];
                }
            }
             
            /* mengecek angka tidak nol semua, lalu ditambahkan tingkat */
            if ((angka[i] != "0") || (angka[i+1] != "0") || (angka[i+2] != "0")){
                subkalimat = kata1+" "+kata2+" "+kata3+" "+tingkat[j]+" ";
            }
             
            kalimat = subkalimat + kalimat;
            i = i + 3;
            j = j + 1;
        }
         
        /* mengganti Satu Ribu jadi Seribu jika diperlukan */
        if ((angka[5] == "0") && (angka[6] == "0")){
            kalimat = kalimat.replace("Satu Ribu","Seribu");
        }
    }
    return kalimat;
}