PERBAIKAN SCRIPT LEBIH LENGKAP DAN KEREN
PETUNJUK TEKNIS
1. Buat Folder Induk Dokumen
2. Buat Dokumen Google Spreadsheet
○ Pada header spreadsheet tambahkan kolom paling kanan
dengan nama ‘Status’
3. Buat Dokumen Google Doc sebagai template
○ Berikan Place holder {{ dan }} - Lihat pada video
4. Buat folder Output untuk menyimpan hasil Automation
(Nama Folder boleh apa saja)
Langkah-langkah
1. Buka Google spreadsheet yang sudah dibuat sebelumnya
2. Pilih menu Extensions, hapus semua file di dalamnya dan
ganti kode berikut
/***********************
* MENU
***********************/
function onOpen() {
[Link]()
.createMenu('Generate')
.addItem('Generate Document', 'openGenerateDialog')
.addToUi();
}
/***********************
* DIALOG
***********************/
function openGenerateDialog() {
const html = [Link]('GenerateDialog')
.setWidth(620)
.setHeight(700);
[Link]().showModalDialog(html, 'Generate Document');
}
/***********************
* SETTING
***********************/
function settingKey() {
return 'GEN_DOC_SETTING_' + [Link]().getId();
}
function saveSetting(data) {
[Link]()
.setProperty(settingKey(), [Link](data));
return true;
}
function loadSetting() {
const raw = [Link]()
.getProperty(settingKey());
return raw ? [Link](raw) : {};
}
/***********************
* SHEET
***********************/
function getSheetList() {
return [Link]()
.getSheets()
.map(s => [Link]());
}
/***********************
* DATA
***********************/
function getRows(sheetName, statusValue) {
const sheet = [Link]().getSheetByName(sheetName);
if (!sheet) throw new Error('Sheet tidak ditemukan');
const data = [Link]().getValues();
const headers = data[0];
const statusCol = [Link](
h => String(h).toLowerCase() === 'status'
);
if (statusCol === -1) {
throw new Error('Kolom Status tidak ditemukan');
}
const rows = [];
for (let i = 1; i < [Link]; i++) {
if (String(data[i][statusCol]).trim() === statusValue) {
[Link]({
rowNumber: i + 1,
headers,
values: data[i],
statusCol
});
}
}
return rows;
}
/***********************
* PROCESS
***********************/
function generateRow(row, setting) {
let fileName = [Link];
[Link]((h, i) => {
fileName = [Link](
new RegExp(`{{${h}}}`, 'g'),
[Link][i]
);
});
fileName = [Link](/[\\\/:*?"<>|]/g, '_');
const folder = [Link]([Link]);
const copy = [Link]([Link])
.makeCopy(fileName, folder);
const doc = [Link]([Link]());
const body = [Link]();
[Link]((h, i) => [Link](`{{${h}}}`, [Link][i]));
[Link]();
let finalUrl = [Link]();
if ([Link] === 'PDF') {
const pdf = [Link]([Link]([Link]))
.setName(fileName + '.pdf');
finalUrl = [Link]();
[Link](true);
}
const richText = [Link]()
.setText(fileName)
.setLinkUrl(finalUrl)
.build();
[Link]()
.getSheetByName([Link])
.getRange([Link], [Link] + 1)
.setRichTextValue(richText);
return fileName;
}
3. Buat Add a File HTML dan kasi nama “GenerateDialog”,
dan copy code berikut
4. Ikuti petunjuk lengkapnya pada Video
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link
href="[Link]
rel="stylesheet">
<style>
body { padding:20px; font-family: system-ui; }
#log { background:#f8f9fa; padding:10px; border-radius:6px; max-
height:220px; overflow:auto; font-size:13px; }
</style>
</head>
<body>
<h5>Generate Document</h5>
<p class="text-muted">
Baris diproses berdasarkan nilai kolom <b>Status</b>.
Setelah selesai, kolom Status akan berisi <b>link dokumen hasil</b>.
</p>
<div class="mb-2">
<label class="form-label">Template Docs ID</label>
<input id="templateId" class="form-control">
<div class="form-text">Masukkan Template ID</div>
</div>
<div class="mb-2">
<label class="form-label">Folder Output ID</label>
<input id="folderId" class="form-control">
<div class="form-text">Masukkan Output Folder ID</div>
</div>
<div class="mb-2">
<label class="form-label">Format Nama File</label>
<input id="fileName" class="form-control">
<div class="form-text">Contoh: {{Nama}} - {{Kelas}}</div>
</div>
<div class="mb-2">
<label class="form-label">Sheet yang Diproses</label>
<select id="sheetName" class="form-select"></select>
</div>
<div class="mb-3">
<label class="form-label">Nilai Status yang Diproses</label>
<input id="statusValue" class="form-control" placeholder="Contoh: Lengkap /
OK / Siap">
</div>
<div class="mb-3">
<label class="form-label">Simpan Sebagai</label>
<select id="saveAs" class="form-select">
<option value="DOC">Google Docs</option>
<option value="PDF">PDF</option>
</select>
</div>
<button class="btn btn-success me-2" onclick="save()">💾 Simpan Template
</button>
<button id="btnGenerate" class="btn btn-primary" onclick="start()">▶
Generate</button>
<div id="saveInfo" class="text-success mt-2" style="display:none;">
✅ Template berhasil disimpan
</div>
<div class="progress my-3">
<div id="bar" class="progress-bar progress-bar-striped progress-bar-
animated"
style="width:0%">0%</div>
</div>
<div id="log"></div>
<script>
let rows = [], idx = 0;
/* LOAD SHEET */
[Link](sheets => {
[Link](s => [Link](new Option(s, s)));
}).getSheetList();
/* LOAD SETTING */
[Link](saved => {
[Link] = [Link] || '';
[Link] = [Link] || '';
[Link] = [Link] || '';
[Link] = [Link] || 'DOC';
[Link] = [Link] || '';
if ([Link]) [Link] = [Link];
}).loadSetting();
/* SAVE */
function save(){
[Link](()=>{
[Link] = 'block';
setTimeout(()=>[Link]='none',3000);
}).saveSetting({
templateId: [Link],
folderId: [Link],
fileNameTemplate: [Link],
saveAs: [Link],
sheetName: [Link],
statusValue: [Link]()
});
}
/* GENERATE */
function start(){
const btn = [Link]('btnGenerate');
[Link]('btn-primary');
[Link]('btn-success');
[Link] = '⏳ Generating...';
[Link] = true;
[Link] = '';
updateProgress(0);
[Link](r=>{
rows = r;
idx = 0;
if([Link] === 0){
[Link] = '<span class="text-warning">Tidak ada data yang
cocok</span>';
[Link] = false;
[Link] = '▶ Generate';
return;
}
next();
}).getRows([Link], [Link]());
}
function next(){
if(idx >= [Link]){
updateProgress(100);
[Link] += '<br><b>✅ SEMUA SELESAI</b>';
return;
}
[Link](name=>{
[Link] += '✔ ' + name + '<br>';
idx++;
updateProgress([Link]((idx / [Link]) * 100));
[Link] = [Link];
next();
}).generateRow(rows[idx], {
templateId: [Link],
folderId: [Link],
fileNameTemplate: [Link],
saveAs: [Link],
sheetName: [Link]
});
}
function updateProgress(pct){
[Link] = pct + '%';
[Link] = pct + '%';
}
</script>
</body>
</html>