google-sheetsgoogle-apps-scriptgoogle-docs

Appscript (GoogleSheet) sheet.getrange doesnt work


so I'm stuck with a script that I've created, in this script the aim is to automate data from sheets to docs. My script as a whole has been running smoothly but there is 1 function that has an error where I want to put the docs link that has been created into column F in my sheets.

You can access my sheets and docs through here : https://drive.google.com/drive/folders/1lqkZBS89MK2YDUjaFg4FJh7YD0O6Lcma?usp=sharing

if u can't access the script then I put it here

function createNewGoogleDocs() {
  const googleDocTemplate = DriveApp.getFileById('193ukX8NfW64_hJNPzBabFa4FrCioMj2MNkyvSx1AvNQ'); // Template File ID
  const destinationFolder = DriveApp.getFolderById('1lqkZBS89MK2YDUjaFg4FJh7YD0O6Lcma'); // 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) => {
    const pt = row[1]; // "PT" column (Column B)
    if (!groups[pt]) {
      groups[pt] = [];
    }
    groups[pt].push(row);
    return groups;
  }, {});

  // Iterate over each PT group
  Object.entries(groupedData).forEach(([pt, group], groupIndex) => {
    try {
      const copy = googleDocTemplate.makeCopy(`Invoice - ${pt}`, destinationFolder);
      const doc = DocumentApp.openById(copy.getId());
      const body = doc.getBody();

      // Replace general placeholders using the first entry in the group
      const firstEntry = group[0];
      body.replaceText('{{Tanggal}}', firstEntry[0]); // Column A
      body.replaceText('{{PT}}', firstEntry[1]); // Column B
      body.replaceText('{{Alamat PT}}', firstEntry[2]); // 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 and the template row
      const rowCount = firstTable.getNumRows();
      for (let i = rowCount - 1; i > 1; i--) {
        firstTable.removeRow(i); // Remove all rows except the first two (header and template)
      }

      // Insert new rows below the header row
      group.forEach((row, index) => {
        const newRow = firstTable.appendTableRow(); // Create a new row
        newRow.appendTableCell(String(index + 1)); // NO column
        newRow.appendTableCell(row[3]); // AJU NO column (Column D)
        newRow.appendTableCell(row[4]); // Placeholder for TANGGAL SURAT JALAN
        newRow.appendTableCell(row[6]); // Placeholder for NO BL / NO DO
        newRow.appendTableCell(row[7]); // NO CONTAINER column (Column E)
        newRow.appendTableCell(row[8]); // Placeholder for PARTY TRUCKING
        newRow.appendTableCell(row[9]); // Placeholder for PPN
        newRow.appendTableCell(row[10]); // Placeholder for TOTAL INV
        newRow.appendTableCell(row[11]); // Placeholder for TOTAL INV
        newRow.appendTableCell(row[12]); // Placeholder for TOTAL INV
      });

      // Optionally, remove the last row if it is an unnecessary template row
      firstTable.removeRow(rowCount - 1);

      doc.saveAndClose();
      const url = doc.getUrl();
      sheet.getRange(index + 1, 6).setValue(url)
      
    } catch (error) {
      Logger.log(`Error processing group for PT: ${pt}: ${error}`);
    }
  });
}

Solution

  • When the script is tested in the example spreadsheet you provided, the Error processing group for PT: rest2: Exception: Child index (4) must be less than the number of child elements (4). shows up, which is what I assume you meant by the 1 function that has an error where I want to put the docs link that has been created into column F in my sheets. statement in the question.

    Upon reviewing the code, the cause of the error is

    firstTable.removeRow(rowCount - 1);
    

    Removing that from the script should give something like this:

    OUTPUT

    I did not modify the code to generate a separate link for row 3 because the script uses Array.prototype.reduce() to combine all CTH and res2 on the generated Google Document.