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}`);
}
});
}
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:
I did not modify the code to generate a separate link for
row 3
because the script usesArray.prototype.reduce()
to combine allCTH
andres2
on the generated Google Document.