Replace text placeholder with image using Google Apps Script

Question

I am trying to place four images into my google document, they should be formatted to the same size and within a square. I am trying to replace text placeholders but I am open to other methods.

The image URLS are located in my google sheet, and I am initialising them as variables.

I am trying to loop through each of the 4 images and replace each corresponding text placeholder with them.

function createDocument() {
  var headers = Sheets.Spreadsheets.Values.get('1Yy70i8aQ3GZEp5FE5mhO458Bmivu01ZeykG0GFZmrVo', 'A4:AL4');
  var tactics = Sheets.Spreadsheets.Values.get('1Yy70i8aQ3GZEp5FE5mhO458Bmivu01ZeykG0GFZmrVo', 'A5:J26');
  var templateId = '1ajVQxJAgGxXF3ivkaWL5WEv2xhiJc9r0YwxLH5Hm17w';
      
  for(var i = 0; i < tactics.values.length; i++){
        
        // see what the sheets API is returning
        Logger.log(tactics);
        
        // initialise variables from the sheet
        var projectID = tactics.values[i][0];
        var projectName = tactics.values[i][1];
      
        // copy our template and capture the ID of the copied document
        var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
        
        // name the copied doc by projectID
        DriveApp.getFileById(documentId).setName(projectID + 'overview');
        
        // update the body of the document
        var body = DocumentApp.openById(documentId).getBody();
        
        // replace template placeholders with sheet variables
        body.replaceText('##ProjectID##', projectID)
        body.replaceText('##ProjectName##', projectName)

        // initialise images from URLs
        var URL1 = tactics.values[i][10];
        var URL2 = tactics.values[i][11];
        var URL3 = tactics.values[i][12];
        var URL4 = tactics.values[i][13];

        var fileID1 = URL1.match(/[\w\_\-]{25,}/).toString();
        var img1   = DriveApp.getFileById(fileID1).getBlob()
        var item1 = body.appendListItem('Item 1');
        // item1.addPositionedImage(img1); add images so that they are formatted as a square as shown below

A github function to do this has been commented - https://gist.github.com/tanaikech/f84831455dea5c394e48caaee0058b26 - how can I implement this for each of the 4 image URLs?

Answer 1

You seem to be using the advanced service instead of the regular ones. You should also split this code into more manageable functions. I liked the problem so here is my solution:

const SHEET_ID = ''
const TEMPLATE_ID = ''

/**
 * Entry, what you call to make the template.
 */
function createAllDocuments() {
  const spreadsheet = SpreadsheetApp.openById(SHEET_ID)
  const sheet = spreadsheet.getSheets()[0] // Only sheet?
  const tactics = sheet.getRange('A5:M')
    .getValues()
    .filter(arr => arr.some(v => !!v)) // Remove empty rows
  
  for (let tactic of tactics) {
    // Get the variables for the tactic
    const projectID = tactic[0]
    const projectName = tactic[1]
    const images = tactic.slice(10).filter(v => !!v)
    createDocument(projectID, projectName, images)
  }
}

/**
 * Creates a single document from the info.
 * @param {string} projectID ID of the project.
 * @param {string} projectName Name of the project.
 * @param {string[]} images URL of the images to replace with.
 */
function createDocument(projectID, projectName, images) {
  // Copy the document and get its body
  const doc = openDocumentCopy(TEMPLATE_ID)
  const body = doc.getBody()
  
  // Make the changes
  doc.setName(`${projectID} overview`)
  body.replaceText('##ProjectID##', projectID)
  body.replaceText('##ProjectName##', projectName)
  
  for (let i = 0; i < images.length; i++) {
    const img = getFileByUrl(images[i])
    const placeholder = `##GoogleID${i+1}##`
    replaceTextWithImage(body, placeholder, img, 200)
  }
}


/**
 * Creates and opens a copy of a template.
 * @param {string} id ID of the template.
 * @returns {DocumentApp.Document}
 */
function openDocumentCopy(id) {
  return DocumentApp.openById(DriveApp.getFileById(id).makeCopy().getId())
}


/**
 * Gets a file that doesn't have a resource key from its URL
 * @param {string} url URL of the file
 * @returns {DriveApp.File}
 */
function getFileByUrl(url) {
  const id = url.match(/[\w\_\-]{25,}/)[0]
  return DriveApp.getFileById(id)
}


/**
 * Replaces a document text with an image file. It replaces the entire paragraph.
 * Based on Tanaike's https://gist.github.com/tanaikech/f84831455dea5c394e48caaee0058b26
 * 
 * @param {DocumentApp.Body} body Body of the file. Where to replace in.
 * @param {string} text Text to replace.
 * @param {DriveApp.File} imgFile Image as a file.
 * @param {number} [width] Optional width to set to the image.
 */
function replaceTextWithImage(body, text, imgFile, width) {
  for (let entry of findAllText(body, text)) {
    const r = entry.getElement()
    
    // Remove text
    r.asText().setText("")
    
    // Add image
    const image = r.getParent().asParagraph().insertInlineImage(0, imgFile.getBlob())
    
    // Resize if given
    if (width != null) {
      setImageWidth(image, width)
    }
  }
}


/**
 * Generator that finds all the entries when searching.
 * @param {DocumentApp.Body} body Body of the file.
 * @param {string} text Text to find.
 * @returns {Iterator.<DocumentApp.RangeElement>}
 */
function* findAllText(body, text) {
  let entry = body.findText(text)
  while(entry != null) {
    yield entry
    entry = body.findText(text, entry)
  }
}


/**
 * Sets an image size based on its width.
 * @param {DocumentApp.Image|DocumentApp.InlineImage} image Image to apply.
 * @param {number} width Width to set
 */
function setImageWidth(image, width) {
  const ratio = image.getHeight() / image.getWidth()
  image.setWidth(width)
  image.setHeight(width * ratio)
}

Note that most work here was to clean the code and split them into different functions. I used a modified version of Tanaike's code.

It may be the case that you need to modify this code a bit. Also, it only support replacing an entire paragraph with the image, which should be enough for your case.

References

• Body.prototype.findText(searchPattern) (Apps Script reference)
• Paragraph.prototype.insertInlineImage(childIndex, image) (Apps Script reference)
• Array.prototype.slice() (MDN JavaScript reference)
• Array.prototype.filter() (MDN JavaScript reference)
• Array.prototype.some() (MDN JavaScript reference)
• Template literals (Template strings) (MDN JavaScript reference)
• for...of statement (MDN JavaScript reference)
• Iterators and generators (MDN JavaScript guide)

Answer 2

You can try to adapt this solution:

var template_ID = '####';

function main() {

  // array to test, you can replace it with your data
  var tactics = [
    [ 'Project ID 123',
      'Project Name ',
      '###', // img1 id 
      '###'  // img2 id
             // etc
    ]
  ];

  tactics.forEach(t => create_doc(t));
}

function create_doc(tact) {

  var project_ID  = tact[0];
  var projectName = tact[1];
  var img_IDs     = [tact[2], tact[3]]; // etc

  var doc_file    = DriveApp.getFileById(template_ID).makeCopy(projectName + 'overview');
  var doc         = DocumentApp.openById(doc_file.getId());
  var body        = doc.getBody();

  body.replaceText('##ProjectID##', project_ID);
  body.replaceText('##ProjectName##', projectName);

  for (var i in img_IDs) {
    var pattern = '##GoogleID' + i + '##';  // square brackets get problems sometimes
    // pay attention to numbering, it will starts from zero: ID0, ID1, ID2, ...
    replaceTextToImage(body, pattern, img_IDs[i]);
  }

}

// credits: https://tanaikech.github.io/2018/08/20/replacing-text-to-image-for-google-document-using-google-apps-script/

function replaceTextToImage(body, searchText, imageFileId) {
  var image = DriveApp.getFileById(imageFileId).getBlob();
  var next = body.findText(searchText);
  if (!next) return;
  var r = next.getElement();
  r.asText().setText('');
  r.getParent().asParagraph().insertInlineImage(0, image);
  return next;
};