I successfully created two functions which are to build data report in my template based on a certain data (invoice) and to autodownload the report as pdf file. But when i tried to loop those functions because i have many invoices to be downloaded as pdf files. this is my function :
function downloadNota() {
var ws = SpreadsheetApp.getActiveSpreadsheet();
var xs = ws.getSheetByName('Print Nota');
var lr = xs.getLastRow();
var nota = xs.getRange('B2:B' + lr).getValues();
var number = nota.map(function (r) { return r.toString().replace('\[ | \]', '') });
number.forEach(function (invoice) {
printInvoice(+invoice)
});
downloadPdf(noInvoice)
}
But when i run the function, data in the template become blank (image attached).
Here is my function to fill the data in my template :
function printInvoice(noInvoice) {
var ws = SpreadsheetApp.getActiveSpreadsheet();
var ss = ws.getSheetByName('NOTA INVOICE2');
var dd = ws.getSheetByName('GABUNGAN');
ss.getRange('B10').setValue(noInvoice);
ss.getRange('A14:G120').activate();
ss.getActiveRangeList().clear({ contentsOnly: true, skipFilteredRows: true })
.setBackground(null)
.setBorder(false, false, false, false, false, false)
.setFontWeight(null)
.setFontColor(null)
.breakApart();
var allData = dd.getRange('A5:S').getValues();
var dataString = allData.filter(String);
var listData = dataString.map(function (r) { return r; });
var neededData = listData.filter(listData => listData[2] === noInvoice);
var nData = neededData.length;
var data = [];
if (!neededData) return;
for (var i = 0; i < nData; i++) {
newData = [neededData[i][5], neededData[i][6], neededData[i][8], neededData[i][9], neededData[i][7], (neededData[i][8] - neededData[i][9]) * neededData[i][7]];
data.push(newData);
}
ss.getRange(14, 6, nData, 2).mergeAcross();
ss.getRange(14, 1, nData, 7).activate();
ss.getActiveRangeList()
.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID)
ss.getRange(14, 1, nData + 8, 7).activate();
ss.getActiveRangeList()
.setHorizontalAlignment("right");
ss.getRange(14, 1, nData, 2).activate();
ss.getActiveRangeList()
.setHorizontalAlignment("center");
ss.getRange(14, 5, nData, 1).activate();
ss.getActiveRangeList()
.setHorizontalAlignment("center");
ss.getRange(14 + nData + 1, 2, 4, 1).activate();
ss.getActiveRangeList()
.setHorizontalAlignment("left");
ss.getRange(14 + nData + 5, 1, 1, 7).activate();
ss.getActiveRangeList().setBorder(true, false, false, false, false, false, '#000000', SpreadsheetApp.BorderStyle.SOLID);
ss.getRange(14, 1, nData, 6).setValues(data);
ss.getRange(14 + nData + 2, 1).setValue('Status Pengiriman:');
ss.getRange(14 + nData + 2, 2).setValue('SUDAH DIKIRIM');
ss.getRange(14 + nData + 3, 1).setValue('Status Pembayaran:');
ss.getRange(14 + nData + 3, 2).setValue('PAID');
ss.getRange(14 + nData + 1, 6).setValue('SUBTOTAL :');
ss.getRange(14 + nData + 1, 7).setFormulaR1C1("=SUM(R[-2]C[-1]:R[-" + (nData + 1) + "]C[-1])");
ss.getRange(14 + nData + 2, 6).setValue('DPP :');
ss.getRange(14 + nData + 2, 7).setFormula('=VLOOKUP(B10,{GABUNGAN!C5:C,GABUNGAN!Q5:Q},2,false)');
ss.getRange(14 + nData + 3, 6).setValue('PPN (11%) :');
ss.getRange(14 + nData + 3, 7).setFormula('=VLOOKUP(B10,{GABUNGAN!C5:C,GABUNGAN!R5:R},2,false)');
ss.getRange(14 + nData + 4, 6).setValue('BIAYA PENGIRIMAN :');
ss.getRange(14 + nData + 4, 7).setFormula('=VLOOKUP(B10,{GABUNGAN!C5:C,GABUNGAN!N5:N},2,false)');
ss.getRange(14 + nData + 5, 6).setValue('GRAND TOTAL :');
ss.getRange(14 + nData + 5, 7).setFormulaR1C1("=SUM(R[-2]C[0]:R[-4]C[0])");
ss.getRange('B10').activate();
}
here is what i hope to get as a pdf file downloaded
Thank you for your time, may god bless us :).
