Populating a table in a google doc template

[dalebyers]

I have a script that creates a Google Doc based on a template with place holders and such, pulling the data from a Google Sheet that is the data source for an AppSheet app.  The trigger for the script is an Appsheet action that calls the script and feeds the function parameters.  Everything is working beautifully except...  I have a "Sample" table that is pulling data to fill a table inside the Doc.  Even this part works just fine when the template has enough rows in the table for the amount of sample rows needed for the job.  However, I would like the final doc to have the right sized table.  I cannot seem to get the script to either add rows as needed or delete empty rows when complete.  Here is my latest attempt:

function createCoc(sheetId, templateId, folderUrl, jobId, inspectorEmail, sampleDate, customer, address, lab, inspectorName, turnaroundTime) {

  console.log("Received lab value:", lab);

  console.log("Received tat value:", turnaroundTime);

  // Get the spreadsheet and template

  const spreadsheet = SpreadsheetApp.openById(sheetId);

  const template = DriveApp.getFileById(templateId);

 // Extract TAT using regular expression

  const regex = /-(.*)/;

  const match = regex.exec(turnaroundTime);

  const extractedTAT = match ? match[1] : "";

 // Get specific sheets

  const usersSheet = spreadsheet.getSheetByName('Users');

  const labsSheet = spreadsheet.getSheetByName('Labs');

  const samplesSheet = spreadsheet.getSheetByName('Samples');

  // Get all data in one go for efficiency

  const usersData = usersSheet.getDataRange().getValues();

  const labsData = labsSheet.getDataRange().getValues();

  const samplesData = samplesSheet.getDataRange().getValues();

  // Find the row with the specified job ID

  const dataRange = spreadsheet.getDataRange();

  const data = dataRange.getValues();

  const row = data.find(row => row[0] === jobId);

  // Find the inspector row

  const inspectorRow = usersData.find(row => row[0] === inspectorEmail);

  const inspectorPhone = inspectorRow[3];

  // Find the lab row

  const labName = lab[0];

  const labRow = labsData.find(row => row[1] === labName);

  const labAddress = labRow[5];

  const labPhone = labRow[6];

  const labEmail = labRow[7];

  const labCustid = labRow[8];

  // Extract folder ID from the URL

  const folderIdRegex = /[-\w]{25,}/;

  const folderIdMatch = folderUrl.match(folderIdRegex);

  const destinationFolderId = folderIdMatch[0];

  // Create a copy of the template

  const copy = template.makeCopy(`Job-${row[1]} COC`, DriveApp.getFolderById(destinationFolderId));

  // Open the copy and replace placeholders with data

  const doc = DocumentApp.openById(copy.getId());

  const body = doc.getBody();

  const footer = doc.getFooter();

  const header = doc.getHeader();

  // Replace placeholders (adjust indices as needed)

  if (customer !== undefined && customer !== null) {

    body.replaceText('{{Customer}}', customer);

  } else {

    body.replaceText('{{Customer}}', '');

  }

  body.replaceText('{{InspectorEmail}}', inspectorEmail);

  body.replaceText('{{Address}}', address);

  body.replaceText('{{SamplingDate}}', sampleDate);

  body.replaceText('{{Inspector}}', inspectorName);

  body.replaceText('{{InspectorPhone}}', inspectorPhone);

  body.replaceText('{{TAT_Selected}}', extractedTAT);

  body.replaceText('{{Lab}}', lab);

  body.replaceText('{{LabAddress}}', labAddress);

  body.replaceText('{{LabPhone}}', labPhone);

  body.replaceText('{{LabEmail}}', labEmail);

  body.replaceText('{{LabCustid}}', labCustid ? labCustid : 'N/A');

  //body.replaceText('{{LabCustid}}', labCustid);

  // Find the corresponding samples for this job ID

  const samplesForJob = samplesData.filter(sampleRow => sampleRow[0] === jobId);

  // Filter samples with non-zero sample numbers

const filteredSamples = samplesForJob.filter(sampleRow => sampleRow[2] !== 0);

  // Find the maximum sample number for the job ID

  const maxSampleNumber = samplesForJob.reduce((max, sample) => Math.max(max, sample[2]), 0);

// Replace the placeholder in the document

  body.replaceText('{{#ofSamples}}', maxSampleNumber.toString());

// Find the table in the document

let table = null;

for (let i = 0; i < body.getNumChildren(); i++) {

  const child = body.getChild(i);

  if (child.getType() === DocumentApp.ElementType.TABLE) {

    const headerRow = child.getRow(0);

    if (headerRow.getCell(0).getText() === 'Sample #') { // Replace with your actual header text

      table = child;

      break;

    }

  }

}

if (!table) {

  // Handle the case where the table is not found

  Logger.log('Table not found');

  return;

}

// Populate the table with sample data

const numRows = table.getNumRows();

const numSamples = samplesForJob.length;

// Ensure there are enough rows in the table

for (let i = numRows; i < numSamples; i++) {

  table.appendTableRow();

}

// Populate the table with sample data

//samplesForJob.forEach((sampleRow, index) => {

filteredSamples.forEach((sampleRow, index) => {

  const row = table.getRow(index + 1); // Start from the second row

  row.getCell(0).setText(sampleRow[2]); // Assuming sample ID is in column C

  row.getCell(1).setText(sampleRow[3]); // Assuming sample description is in column D

});

// Delete extra rows from the bottom

let i = filteredSamples.length + 1; // Start from the row after the last populated row

while (i < table.getNumRows()) {

  table.deleteRow(i);

}

  doc.saveAndClose();

}

the error I get on this one is:

TypeError: table.deleteRow is not a function at createCoc(Code:126:9)

I get similar errors for other attempts such as "row.remove".

How can I have the table resize dynamically based on the number of rows of sample data needed from the specific "job"?  I am brand spankin' new at this so please be gentle! 🙂  Any help is much appreciated!

[WillowMobileSys]

I have never used a Google App Script to interact with a Google Doc.  I'd love to see the document you are starting with and the end result.

The error you are showing is implying that the function name of "deleteRow(i)"  does not exist on the object of "table".  Are you sure you are using the correct function name??

Actually, looking in the documentation...the correct function name is "removeRow" not "deleteRow".  Refer to the link below for the available list of functions.

https://developers.google.com/apps-script/reference/document/table

[scott192]

My advice here is that instead of starting with a table that already has rows added, start with a table that has the header row and a secondary row with placeholders.

When you iterate through your data, you will (for each line of data) add a copy of the placeholder row and then replace the placeholders in that row.  Note, you will not be globally looking through your document for placeholders, you will be checking that specific row only.

Do this for each item of data in your array.  Finally, you can then remove the second row of your table that has the placeholders.  Table.getRow(1).removeFromParent()

[scott192]

So maybe a little bit of code:

myTable = myDocBody.getTables()[0]  // retrieves the first table in the document body
myPlaceholderRow = myTable.getRow(1)

// Populate the table with sample data

filteredSamples.forEach((sampleRow, index) => {

var thisRow = myTable.appendTableRow(myPlaceholderRow.copy())

  thisRow.getCell(0).setText(sampleRow[2]); // Assuming sample ID is in column C

  thisRow.getCell(1).setText(sampleRow[3]); // Assuming sample description is in column D

});

myPlaceholdedRow.removeFromParent()

That will only add the same number of rows one by one as  is in your data.  And finally it will remove the placeholder row.

[dalebyers]

Thanks @scott192!  We ended up with a code that worked to remove the unnecessary rows of a oversized table in the template - I will play with your solution, as it seems cleaner.  Appreciate the help!

[scott192 · Accepted solution]

The way I do this sort of task is I have two template google docs.  The one template is the main document.  It has logos and artwork in the correct places.  The second doc is a doc of tables.  Each table has a header row and a content row with placeholders.  The tables are styled because the tableRow.copy() method mostly (yeah mostly) carries over all formatting to the destination.
The workflow being that my data source specifies the tables it should be inserted into as well.  So my flow is to make a copy of the main doctemplate and then flow down through my data and copying the required table from the tables doc, filling it using the method I showed and then grabbing the next table and repeating.
The tables doc is easy to understand as body.getTables() get's all the tables without messing about with elements and so on.
Hope that helps 😊