google-apps-scriptgoogle-docsgoogle-docs-api

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


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

Solution

  • 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.

    enter image description here

    Note:

    References: