How do I get the image ID of an image IN THE CELL?

Question

I'm currently working on a code in Google Apps Script that allows a user to fill out a spreadsheet and have the spreadsheet generate printouts for a job board. I'm trying to design this in a way where the user can simply insert a logo image into a row of my Google sheet and have it replace a placeholder in my doc template. I have found lots of answers about how you can take an image and convert it to a blob and insert it from a url or an ID, however, I can't seem to find a way to get the ID or url from the image in the cell. Here's my code currently:

//Creates menu option on spreadsheet
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs');
  menu.addToUi();

}

//Defines where to get template and info from
function createNewGoogleDocs() {
 
  const googleDocTemplate = DriveApp.getFileById('14MJNd37pn6D-EmNKCQzXXvxJCcOAoB3KS-TlDgZuWMI');
  const destinationFolder = DriveApp.getFolderById('120Sb_CJJlmz5NzJW8W3DB4TNuC4kdD3e');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('JobBoard');
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index) {
    if (index === 0) return;
    if (row[9]) return;
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Printout`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[2]).toLocaleDateString();

//Replacing text
    body.replaceText('{{Company}}', row[1]);
    body.replaceText('{{jobTitle}}', row[0]);
    body.replaceText('{{datePosted}}', friendlyDate);
    body.replaceText('{{Description}}', row[3]);
    body.replaceText('{{Qualifications}}', row[5]);
    body.replaceText('{{Wage}}', row[4]);
    body.replaceText('{{Apply}}', row[6]);

//A subfunction to handle replacing the image
  function textToImage() {
  var replaceTextToImage = function(body, searchText, image, width) {
    var next = body.findText(searchText);
    if (!next) return;
    var r = next.getElement();
    r.asText().setText("");
    var img = r.getParent().asParagraph().insertInlineImage(0, image);
    if (width && typeof width == 100) {
      var w = img.getWidth();
      var h = img.getHeight();
      img.setWidth(width);
      img.setHeight(width * h / w);
    }
    return next;
  };

  var documentId = doc;
  var replaceText = "{{Upload Image}}";
  var imageFileId = "### File ID of image ###"; //I don't know how to get this variable
  var body = DocumentApp.openById(documentId).getBody();
  var image = DriveApp.getFileById(imageFileId).getBlob();
  do {
    var next = replaceTextToImage(body, replaceText, image, 200);
  } while (next);
}

//Close and saves new doc
    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 10).setValue(url)
    
  })

}

I think what might be messing me up is that I have to loop through all my cells right now so that I can create multiple documents at once (meaning each row will have a different doc and different image ID). I'm just not sure how to work around that.

Here's the template and spreadsheet

https://docs.google.com/spreadsheets/d/1cySHogAxcUgzr0hsJoTyPZakKQkM6uIOtmyPzcMoJUM/edit?usp=sharing

https://docs.google.com/document/d/14MJNd37pn6D-EmNKCQzXXvxJCcOAoB3KS-TlDgZuWMI/edit?usp=sharing

Answer 1

There is a bit of an issue trying to get an image in a specific cell. There's even a Feature Request for that. This year Google released a few classes for image management but there seems to be issues when retrieving those using cellImage class.

I found a related answer (workaround) from user @Tanaike where images are retrieved from Google Sheets, converted to a Blob and inserted into a Google Doc.

Sample code provided was:

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getSheetByName("Sheet1").getImages();
console.log(res); // You can check the retrieved images at the log.
if (res.length == 0) return;
const blob = res[0].image.blob; // Here, 1st image of Sheet1 is retrieved. Of course, you can choose the image on the sheet.

let doc = DocumentApp.create("newDocName Goes_Here");
var body = doc.getBody();
var imgPDF = body.appendImage(blob);

Take into consideration that to make the above work you need to:

1. Install Google Apps Script library. (instructions here)
2. Enable Drive API.

I tested this and indeed, got the images from the given sheet and inserted them into the Google Doc specified. For some reason, running your code did not show me a newly created file from the template but you can tweak the above accordingly to your case.