Remove placeholder text from cell and merge in google docs document using MergeTableCellsRequest

Question

I have a document with multiple tables. Content in the tables is being populated from data from a spreadsheet, with placeholder text being replaced with the text or image in the corresponding spreadsheet column. The image will always be in the fourth column, but not every row will contain an image. So, after all the data has been inserted into the google doc, there will be some image placeholder text left in the document. See here (e.g. {{Threshold gap photo}})

If there is no image in the 4th column, my script should remove the placeholder text and merge the 4th column with the 3rd. Like so.

This solution works to merge the two columns, but only if the 4th column is blank to begin with (i.e. does not have text in the 4th column which is then removed). So, I'm sure the issue is how I am removing the placeholder text from the cell.

  var templateFile = DriveApp.getFileById('1MKshF2xmQvU26hG6S5jqFeynZsLM6iSFr4y-vf1cAx0'); //Document Template ID
  var destinationFolder = DriveApp.getFolderById('1-OCG7h5m6yU5hBqsnUOJADeL8-7gXrld'); //folder where reports will go
  var fileName = "merge-test-doc";
  var newFile = templateFile.makeCopy(fileName, destinationFolder);
  var placeholdersToFind = ['Threshold gap photo', 'Third-party photo', 'Door thickness photo', 'Leaf to frame photo','Meeting stile photo']
  const docId = newFile.getId();
  const doc = DocumentApp.openById(docId);
  const body = doc.getBody();
  const tables = body.getTables();
  const obj = Docs.Documents.get(docId).body.content;

  for (var j = 1; j <= placeholder.length; j++) {
    var currPH = placeholdersToFind [j - 1]; //get the current placeholder to be found in report template to replace with image
    var patternToFind = Utilities.formatString('{{%s}}', currPH); //format it to match how it looks in report template
    var entry = body.findText(patternToFind) // find the placeholder in the template
    if (entry != null) {
      r = entry.getElement().removeFromParent() //remove the placeholder text
    }
  }

//ignore first 3 tables 
  const requests = tables.slice(3).reduce((ar, t) => {
    const tidx = body.getChildIndex(t);
    const rows = t.getNumRows();
    for (let r = 0; r < rows; r++) {
      const col4 = t.getCell(r, 3).getChild(0).asParagraph().getNumChildren();
      if (col4 == 0) {
        ar.push({ mergeTableCells: { tableRange: { columnSpan: 2, rowSpan: 1, tableCellLocation: { tableStartLocation: { index: obj[tidx + 1].startIndex }, rowIndex: r, columnIndex: 2 } } } });
      }
    }
    return ar;
  }, []);
  Docs.Documents.batchUpdate({ requests }, docId);

Tanaike · Accepted Answer · 2024-03-18 04:54:48Z

0

In this answer, in order to achieve your goal, I modified my script.

Modified script:

function myFunction() {
  var templateFile = DriveApp.getFileById("###");  // Please set your value.
  var fileName = "merge-test-doc"; // Please set your value.
  var destinationFolder = DriveApp.getFolderById("###"); // Please set your value.

  var newFile = templateFile.makeCopy(fileName, destinationFolder);

  const docId = newFile.getId();
  const doc = DocumentApp.openById(docId);
  const body = doc.getBody();
  const tables = body.getTables();
  const obj = Docs.Documents.get(docId).body.content;
  const requests = tables.slice(3).reduce((ar, t) => {
    const tidx = body.getChildIndex(t);
    const rows = t.getNumRows();
    for (let r = 2; r < rows; r++) {
      const col4 = t.getCell(r, 3).findElement(DocumentApp.ElementType.INLINE_IMAGE);
      if (!col4) {
        ar.push({ mergeTableCells: { tableRange: { columnSpan: 2, rowSpan: 1, tableCellLocation: { tableStartLocation: { index: obj[tidx + 1].startIndex }, rowIndex: r, columnIndex: 2 } } } });
        const cell = obj[tidx + 1].table.tableRows[r].tableCells[3];
        if (cell.content.some(e => e.paragraph.elements.some(f => f.textRun.content.trim()))) {
          ar.push({ deleteContentRange: { range: { startIndex: cell.startIndex + 1, endIndex: cell.endIndex - 1 } } });
        }
      }
    }
    return ar;
  }, []).reverse();
  if (requests.length == 0) return;
  Docs.Documents.batchUpdate({ requests }, docId);
}

Testing:

When this script is run, the following result is obtained.

Note:

From your showing image, I supposed that the tables after 4th table has 2 header rows.

References:

answered Mar 18, 2024 at 4:54

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GoldIngots Over a year ago

Thank you so much for taking the time to respond - it certainly works. If possible, could you explain what this line is doing? if (cell.content.some(e => e.paragraph.elements.some(f => f.textRun.content.trim()))) { ar.push({ deleteContentRange: { range: { startIndex: cell.startIndex + 1, endIndex: cell.endIndex - 1 } } }); Thank you again, really appreciate your time and effort!

Tanaike Over a year ago

@GoldIngots Thank you for replying. I'm glad your issue was resolved. About your additional question, in that part, when column "D" has a text like {{sample}}, the request body for removing this text is added. If this explanation was not useful, I apologize.

Collectives™ on Stack Overflow

Remove placeholder text from cell and merge in google docs document using MergeTableCellsRequest

1 Answer 1

Modified script:

Testing:

Note:

References:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Testing:

Note:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related