Replacing a placeholder text label in a Google Docs table cell with a Google Drive image

Question

I am trying to automate populating a report using only Google Apps. The sequence goes as followed:

Google Form > Google Sheets > Google Docs Template

The Google Form serves the purpose to enter all details for the final report: A quality score (1, 2 or 3) and some text. In Google Sheets, the quality score gets transformed into a text based label (#good#, #ok#, #bad#).

I've found a script here on Stack Overflow that picks up a Google Doc Template and populates the placeholder labels (%placeholder%) with the values in the Google Sheet. However, I haven't been able to solve for replacing the quality score label with an image/icon (a green, orange and red icon) that I have in my Google Drive. I got as far as replacing the label with the icon's file name (e.g. icon-green.png), but not the icon itself. Good to know is that the quality score label is placed within a table cell.

I'm incredibly new to javascript and could really use your help! I've indicated in the code beneath where I'm stuck exactly (towards the end).

// Replace this with ID of your template document.
var TEMPLATE_ID = '1AatkH57Iq3FwmsvZkCMYddjACItWS_XuO9sCsfcaMso'

// You can specify a name for the new PDF file here, or leave empty to use the 
// name of the template.
var DOC_FILE_NAME = 'First GA Doc Template Try'

/**
* Eventhandler for spreadsheet opening - add a menu.
*/

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('Create Doc')
    .addItem('Create Doc', 'createDoc')
    .addToUi()

} // onOpen()

/**  
 * Take the fields from the active row in the active sheet
 * and, using a Google Doc template, create a Doc with these
 * fields replacing the keys in the template. The keys are identified
 * by having a % either side, e.g. %Name%.
 *
 * @return {Object} the completed Doc file
 */

function createDoc() {

  if (TEMPLATE_ID === '') {

    SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  // Set up the docs and the spreadsheet access

  var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
      iconGreen = DriveApp.getFileById('0B0C0sFLzFLyWOUVSeWxQa2o2MFE'),
      copyId = copyFile.getId(),
      copyDoc = DocumentApp.openById(copyId),
      copyBody = copyDoc.getActiveSection(),
      activeSheet = SpreadsheetApp.getActiveSheet(),
      numberOfColumns = activeSheet.getLastColumn(),
      activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
      activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
      headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
      columnIndex = 0

  // Replace the keys with the spreadsheet values

  for (;columnIndex < headerRow[0].length; columnIndex++) {

    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', 
                         activeRow[0][columnIndex])                         
  }

  copyBody.replaceText('#good#', iconGreen) // This is where I'm stuck..

  copyFile.setTrashed(false)

  SpreadsheetApp.getUi().alert('New Doc created in the root of your Google Drive')

} // createDoc()

alfiethecoder alfiethecoder · Accepted Answer · 2016-06-14T09:47:49

If you want to insert the image into a Google Doc you will need to get it as a blob and call the insertImage function, something like this:

var blob = DriveApp.getFileById(id).getBlob(); // Replace id with the ID of your icon file.

// This is inserting it into a specific cell in an existing table.  
var newImage = curTable.getRow(imgTargetRow).getCell(imgTargetCol).insertImage(0, blob);

  // This sets the image height and width based on my table cell sizes, but if your icon is sized exactly right you won't need to do this
  var h = newImage.getHeight();
  var w = newImage.getWidth();
  var w = ((w > h) ? 352 : 197); // Adjust for landscape or portrait images
  var h = 264;
  newImage.setHeight(h);
  newImage.setWidth(w);

Replacing a placeholder text label in a Google Docs table cell with a Google Drive image

1 Answers