I am doing an automatic script from spreadsheet to docs with the code below, everything is running smoothly, but there is 1 problem where the footer in the template disappears.
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('AutoFill Docs');
menu.addItem('Create New Docs', 'createNewGoogleDocs');
menu.addToUi();
}
function createNewGoogleDocs() {
const googleDocTemplate = DriveApp.getFileById('193ukX8NfW64_hJNPzBabFa4FrCioMj2MNkyvSx1AvNQ'); // Template File ID
const destinationFolder = DriveApp.getFolderById('1H8pKv0bJU2tgT_wcgXj4GDby55UpABPg'); // Destination Folder ID
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data'); // Data Sheet Name
const rows = sheet.getDataRange().getValues();
// Skip header row
const data = rows.slice(1);
// Group data by PT
const groupedData = data.reduce((groups, row, index) => {
if (index === 0) return groups; // Skip the header row
if (row[13]) return groups; // Skip if column N (index 13) already has a link
const invoice = row[1]; // "PT" column (Column B)
if (!groups[invoice]) {
groups[invoice] = {
rows: [],
indices: []
};
}
groups[invoice].rows.push(row);
groups[invoice].indices.push(index + 2); // Store the row index (+2 to adjust for header and zero-index)
return groups;
}, {});
// Iterate over each PT group
Object.entries(groupedData).forEach(([invoice, group]) => {
try {
const copy = googleDocTemplate.makeCopy(`Invoice - ${invoice}`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
// Replace general placeholders using the first entry in the group
const firstEntry = group.rows[0];
body.replaceText('{{Tanggal}}', firstEntry[18]); // Column A
body.replaceText('{{PT}}', firstEntry[2]); // Column B
body.replaceText('{{Alamat PT}}', firstEntry[3]); // Column C
body.replaceText('{{TOTAL PPN}}', formatCurrency(firstEntry[14])); // Column C
body.replaceText('{{TOTAL INV}}', formatCurrency(firstEntry[15])); // Column C
body.replaceText('{{CHARGES}}', formatCurrency(firstEntry[16])); // Column C
body.replaceText('{{BILANGAN}}', firstEntry[17]); // Column C
// Target the first table in the document
const tables = body.getTables();
const firstTable = tables[0]; // Using the first table
// Clear any existing rows except the header row, the template row, and the footer row
const rowCount = firstTable.getNumRows();
for (let i = rowCount - 1; i > 1; i--) { // Start loop from second last row and end before the second row
firstTable.removeRow(i); // Remove all rows except the first two (header and template) and the last row (footer)
}
// Insert new rows below the header row
group.rows.forEach((row, index) => {
const newRow = firstTable.appendTableRow(); // Create a new row
newRow.appendTableCell(String(index + 1)); // NO column
newRow.appendTableCell(row[4]); // Placeholder for TANGGAL SURAT JALAN
newRow.appendTableCell(row[5]); // Placeholder for NO BL / NO DO
newRow.appendTableCell(formatDate(row[6])); // NO CONTAINER column (Column E)
newRow.appendTableCell(row[7]); // Placeholder for PARTY TRUCKING
newRow.appendTableCell(row[8]); // Placeholder for PPN
newRow.appendTableCell(row[9]); // Placeholder for TOTAL INV
newRow.appendTableCell(formatCurrency(row[10])); // Placeholder for TOTAL INV
newRow.appendTableCell(formatCurrency(row[11])); // Placeholder for TOTAL INV
newRow.appendTableCell(formatCurrency(row[12])); // AJU NO column (Column D)
});
// Update the footer values after the table
body.replaceText('{{CHARGES}}', formatCurrency(firstEntry[16]));
body.replaceText('{{TOTAL PPN}}', formatCurrency(firstEntry[14]));
body.replaceText('{{TOTAL INV}}', formatCurrency(firstEntry[15]));
doc.saveAndClose();
const url = doc.getUrl();
// Update the URL in column N for each row in the group
group.indices.forEach(rowIndex => {
sheet.getRange(rowIndex, 14).setValue(url); // Column N is the 14th column (index 13)
});
} catch (error) {
Logger.log(`Error processing group for PT: ${invoice}: ${error}`);
}
});
}
// Helper function to format numbers as currency (Rp)
function formatCurrency(value) {
return 'Rp ' + parseFloat(value).toLocaleString('id-ID', {minimumFractionDigits: 2, maximumFractionDigits: 2});
}
function formatDate(value) {
if (value instanceof Date) {
return Utilities.formatDate(value, Session.getScriptTimeZone(), 'dd-MMM');
}
return value;
}
as you can see there's footer there filled with {{CHARGES}} etc but the result is lookin like: and is there a way to change the results of this table so that the alignment is automatically justified?
I have tried with this code but the footer shifts up and the body goes down
const rowCount = firstTable.getNumRows();
for (let i = rowCount - 2; i > 1; i--) {
firstTable.removeRow(i);
}
you can see the document here: https://drive.google.com/drive/folders/1H8pKv0bJU2tgT_wcgXj4GDby55UpABPg?usp=sharing
thanks in advance
In your showing script and your provided Spreadsheet and Document, how about the following modification?
const rowCount = firstTable.getNumRows();
for (let i = rowCount - 1; i > 1; i--) { // Start loop from second last row and end before the second row
firstTable.removeRow(i); // Remove all rows except the first two (header and template) and the last row (footer)
}
// Insert new rows below the header row
group.rows.forEach((row, index) => {
const newRow = firstTable.appendTableRow(); // Create a new row
const rowCount = firstTable.getNumRows();
for (let i = rowCount - 2; i > 1; i--) {
firstTable.removeRow(i);
}
// I thought that the following script might be able to also be used instead of the above script.
// firstTable.removeRow(2);
group.rows.forEach((row, index) => {
const newRow = firstTable.insertTableRow(2 + index);
In this modification, I used your bottom script in your question and replaced const newRow = firstTable.appendTableRow();
with const newRow = firstTable.insertTableRow(2 + index);
.
When this modified script is used in your provided Spreadsheet and Document, the following result is obtained.