spreadsheet to docs with app script error (footer table disappeared in docs after running the script)

Question

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

Answer 1

In your showing script and your provided Spreadsheet and Document, how about the following modification?

From:

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

From:

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);.

Testing:

When this modified script is used in your provided Spreadsheet and Document, the following result is obtained.