Google-sheets – Add image in Google Doc from Google Sheet via Script

google docsgoogle sheetsgoogle-apps-script

There is an image in Google Sheet (coming from Google Form) that I would like to insert in Google Doc in Image Format. Currently, I'm getting Url only from the below mention script. How can I get the image. This is the script-

function afterFormSubmit(e) {
  const info = e.namedValues;
  createPDF (info);

}

function createPDF(info){
   
  const pdfFolder = DriveApp.getFolderById("1I7ChP1xRbl7GwnvAcXV_9JIHegvsU2PO");
  const tempFolder = DriveApp.getFolderById("1srcXlFgmh01e4Psw_dxM4bNgarY6tfli");
  const templateDoc = DriveApp.getFileById("1xype5jUa6H8VJ4JSaV-KfccSjK5w6e-hmoI1k6MXSOc");

  const newTempFile = templateDoc.makeCopy(tempFolder);
  const openDoc =  DocumentApp.openById(newTempFile.getId());
  const body = openDoc.getBody();
  body.replaceText("{Location Code}",info['Location Code'][0]);
  body.replaceText(" {Timestamp}",info['Timestamp'][0]);
  body.replaceText( " {Upload Image}",info['Upload Image'][0]);
  
  openDoc.saveAndClose();

  const blobPDF = newTempFile.getAs(MimeType.PDF);
  const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Location Code'][0]+"-"+new Date());
  tempFolder.removeFile(newTempFile);
  /** @type {any} */

}

Google Doc Format

Currently getting data like this

Google sheet input sheet via Google Form

Best Answer

You are trying to add an image uploaded from a Google Form into a Google Document (via Google Spreadsheet) but only the image url is being inserted into the document.

To implement this answer, do the following:

1 - Establish the index number for the paragraph {Upload Image} in the Google Document.

2 - Replace <indexnumber> in the code below with the index number of the paragraph.

3 - Delete this line

body.replaceText( " {Upload Image}",info['Upload Image'][0]);

4 - Substitute these lines

body.replaceText("{Upload Image}", "");
// Convert image to blob
var myimage  = info['Upload Image'][0];
var fileID = myimage.match(/[\w\_\-]{25,}/).toString();
var blob   = DriveApp.getFileById(fileID).getBlob();
body.insertImage(<indexnumber>, blob);

There are two aspects to this answer:

1 - body.replaceText( " {Upload Image}",info['Upload Image'][0]);

This is literally replacing one piece of text {Upload Image} with a second piece of text (the document url); instead you need to delete {Upload Image}. This is the purpose of body.replaceText("{Upload Image}", "");

2 - body.insertImage()

The image needs to be converted to a blob, and then inserted with insertImage().

There are two arguments:

  • the first is the childIndex for the position to insert the image; this is the purpose of establishing the index number for the paragraph {Upload Image}.
  • the second is the blob.

Document template

Merge document

PDF

PDF

Credit: Digital Inspirations for the regex to return the file ID of the image.

Related Topic