1

I have a G-Sheet with 3000 rows of data with links to images in one column. I am able to use body.replaceText to fill out a Google Doc template I have created. The issue is that it simply pulls the hyperlink.

Instead of displaying hyperlinks, how would you show the actual image?

Thank you for your time!

function newGoogleDoc() {
const googleDocTemplate = DriveApp.getFileById('XXXXX');
const destinationFolder = DriveApp.getFolderById('XXXXX')
const sheet = SpreadsheetApp
.getActiveSpreadsheet()

const rows = sheet.getDataRange().getValues();

rows.forEach(function(row, index){
if (index === 0) return;
if (row[15]) return;
const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)
const doc = DocumentApp.openById(copy.getId())
const body = doc.getBody();
const friendlyDate = new Date(row[12]).toLocaleDateString();

body.replaceText('{{Scope}}', row[0]);
body.replaceText('{{Block}}', row[1]);
body.replaceText('{{Line Item}}', row[2]);
body.replaceText('{{Date}}', friendlyDate);
body.replaceText('{{User}}', row[13]);

doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(index + 1, 15).setValue(url)
 })
}
Chris Rucker
  • 11
  • 1
  • In your situation, when `rows` of `const rows = sheet.getDataRange().getValues()` is 3,000 rows, you want to copy 3,000 Google Document files and replace each values. Is my understanding correct? By the way, from `3000 rows of data with links to images in one column`, I couldn't understand about `links to images in one column`. – Tanaike Nov 30 '20 at 04:51
  • Thank you for the clarification! This is my first post on here. I have 3000 rows of data inside of a google sheet. In one column I have links to images hosted online. Due to Google Scripts limitations I need to now make this into a single document. – Chris Rucker Nov 30 '20 at 18:06
  • Thank you for replying. Now I noticed that the answers had already been posted. In this case, I would like to respect them. I think that they will resolve your issue. – Tanaike Nov 30 '20 at 23:40

2 Answers2

1

If the placeholder for the image is a paragraph of its own, and not part of a container element that includes other content, you could to the following:

For example, if the URL to the images were in column A (so the images should replace {{Scope}}), you could replace this line:

body.replaceText('{{Scope}}', row[0]);

With this:

const rangeElement = body.findText('{{Scope}}');
const imageBlob = UrlFetchApp.fetch(row[0]).getBlob();
if (rangeElement) {
  const element = rangeElement.getElement();
  element.asText().setText("");
  const image = element.getParent().asParagraph().insertInlineImage(0, imageBlob);
}

Important note:

This will remove all the other content in the paragraph the placeholder was on. You could also append the image just before or after the paragraph, but not at the exact same location the placeholder was on. See this related question.

Other issues:

  • Instead of checking for each iteration whether index === 0, I'd suggest either getting the range without headers (.getRange(2,1, sheet.getLastRow() - 1, sheet.getLastColumn())), or removing the header array from the outer array (rows.shift()). In this case, though, you should be careful to modify all the other mentions of index inside the loop, since the row this will reference will be a different one.
  • It's not a best practice to call setValue inside a loop. I'd suggest to push the data into an array instead (e.g. urls.push([url])) and write those values at once (using setValues) after the loop is finished (e.g. sheet.getRange(1,15,urls.length).setValues(urls)).
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • 1
    Thank you for the response. I have since made edits to the code and tested what you recommended. I would like this to create a continuous document instead of creating new documents every time, and currently the pictures are pulling in quite large. Also I had to add getParent(). in the const image for it to work. – Chris Rucker Dec 01 '20 at 06:42
  • I willl do that. Thank you! – Chris Rucker Dec 01 '20 at 14:15
0

You will need to convert the image url into a blob and then define the position where to enter it in the doc. Replace text only replaces text with text.

Adapted solution from https://www.labnol.org/code/20078-insert-image-in-google-document

function webImage() {
  //continuing from your code
  const doc = DocumentApp.openById(copy.getId())
  const body = doc.getBody();
  
  // using example image url, not sure which row has your urls
  var image = 'www.imageurl.com/image.jpg';
  var blob = UrlFetchApp.fetch(image).getBlob();
  
  // find position, not sure what you are using in the doc, used text PLACEHOLDER as an example
  var position = body.findText('PLACEHOLDER').getElement()
  var offset = body.getChildIndex(position.getParent())
  
  // insert image
  body.insertImage(offset +1,blob)
  // remove placeholder text
  position.removeFromParent()
  }

Another solution: Add images to Google Document via Google Apps Script

sebbagoe
  • 84
  • 3