add image to cell function

[Ron Peer]

Hi Pros.

I need insert a dynamic URL that directs to a PNG image into a cell range.

Adding it manually through the GUI results in a good resolution render into the sheet and to the correct cells range.

After countless tries through the code, I was only managed  to add the image above cells or add it into the cell which results in bad placement or with =image("URL") function that results with poor resolution render.

I'm trying to achieve same behaviour as manually clicking the image -> add image to cell function through the code which looks great in both resolution and placement. 

Looking forward for your wisdom.

Thanks and regards!

[Fabrice Faucheux]

Hello,

To insert a high-resolution image into a cell in Google Sheets using Google Apps Script, achieving the same visual quality and positioning as manually inserting the image through the Sheets GUI, you can utilize the =IMAGE() function with specific attributes. However, as you mentioned, using the =IMAGE("URL") function often results in images that do not meet the desired quality or placement standards.

Google Apps Script doesn't directly support the exact same method of embedding images into cells as the GUI method Insert > Image > Image in cell. The script options typically involve either placing the image over cells or inserting it with a function that may not render with the best quality.

Workaround Solution

Since the current script methods (like setFormula('=IMAGE("URL")') or using overlays) do not fully replicate the GUI approach's quality and fit, the recommended workaround is to refine the usage of the `=IMAGE()` function with additional parameters to improve the result as much as possible.

Here’s how you can use the =IMAGE() function with all its parameters to optimize the placement and appearance:

function insertImage() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell = sheet.getRange("A1"); // Specify the cell where the image should go
 
  var imageUrl = "YOUR_IMAGE_URL"; // Replace with your image's URL
  var formula = '=IMAGE("' + imageUrl + '", 4, 100, 100)'; // Image function with resizing options
  cell.setFormula(formula);
}

Explanation of the =IMAGE()

Function Parameters:

1. URL: The first parameter is the URL of the image.
2. Mode:

1. Resizes the image to fit inside the cell, maintaining aspect ratio.
2. Stretches or compresses the image to fit the cell, possibly ignoring aspect ratio.
3. Leaves the image at original size, which might overflow the cell.
4. Allows specifying custom dimensions while maintaining aspect ratio (the last two parameters define these dimensions).

3. Height and Width (optional): When mode 4 is used, these specify the desired height and width in pixels.

Fabrice

https://atelier-informatique.com/blog/