How to access new 'in-cell-image' from google apps script?

Question

The new function Insert > Image > Image in Cell in Google sheets inserts an image in a cell and not as an OverGridImage. I would like to insert the image in this manner and then access the image from Google Apps Script. Is this possible?

After inserting the image the formula of the cell is blank when the cell is selected. I tried searching the GAS reference, but I could not find any information on this relatively new feature. There is information on the over grid images. I would expect the in-cell image to have similar functions.

I've tried things like this:

// See what information is available on a cell with inserted image:
var image = sheet.getRange(1, 1).getFormula();
Logger.log(image);

The logs shows up empty. I tried several: .getImage() (does not exist), .getValue(), .getFormula()

I would expect to be able to access the image URL or Blob in some way.

Answer 1

Now available as of January 2022 (release notes):

The following classes have been added to the Spreadsheet Service to let you add images to cells:

• CellImageBuilder: This builder creates the image value needed to add an image to a >cell.
• CellImage: Represents an image to add to a cell.

To add an image to a cell, you must create a new image value for the image using SpreadsheetApp.newCellImage() and CellImageBuilder. Then, use Range.setValue(value) or Range.setValues(values) to add the image value to the cell.

Example:

function insertImageIntoCell()
{
  let image = SpreadsheetApp.newCellImage().setSourceUrl('https://www.gstatic.com/images/branding/product/2x/apps_script_48dp.png').setAltTextDescription('Google Apps Script logo').toBuilder().build();

  SpreadsheetApp.getActive().getActiveSheet().getRange('A1').setValue(image);
}

Result:

function getImageFromCell()
{
  let value = SpreadsheetApp.getActive().getActiveSheet().getRange('A1').getValue();

  console.log(value.getAltTextDescription());
  console.log(value.getUrl());
}

Result:

Note: getUrl returns null for this particular example, which seems to be due some internal API unavailability, from docs:

Gets the image's source URL; returns null if the URL is unavailable. If the image was inserted by URL using an API, this method returns the URL provided during image insertion.

Answer 2

Both Rafa Guillermo and Tanaike requested that I make an answer based on my comment to Tanaike’s post. I do so below, but it falls into the category of a workaround rather than an "answer". A true answer would address the exact question in the original post.

As I said in my comment, I’ve used this method for simple cases, and I’ve also done some tests which suggest it preserves image resolution. Since I've only used this for simple cases like the one below, I don't know how generally it will work.

The steps I provide below are (to the best of my ability) what I remember going through as I did one specific example. Here are the first dozen rows of the final result after using this method:

This example had a total of 7100+ rows

• Column 1 contained 430+ images or blank cells, most of which repeated multiple times

  Column 2 contained unique IDs for each image

  Column 3 are the file names which were tied to each ID using the method below

Steps to extract images from google sheet cells:

1. Resize column and rows containing images to something large (eg, 300)
2. Use File>Publish to Web & paste generated link into a new tab
3. In Chrome, use File>Save Page As…>Webpage, Complete
4. Images will be found in an html folder ending with _files
5. If needed, rename files to use image extension and list in order*

To key downloaded image file names to image cells in the sheet:

1. Duplicate sheet since the following will remove original data
2. Select columns containing images and IDs and use Data>Remove Duplicates
3. Add a new column next to the IDs containing the file names**
4. Use VLOOKUP function to transfer all file names to original sheet based on the unique IDs***

*In my example the images all had names like p.txt, p(1).txt, p(2).txt, etc… In Mac OS Finder, I selected all files and used right click>Rename files… and then the replace option to replace .txt with .jpg, (1) with (001), etc…

**file name listing can be obtained, for example, using the Terminal ls -l command

***for example, I used: =vlookup(B2,unique!$B$2:$C$430,2,false)

Answer 3

This answer is about INSERTING in-cell images. I haven't been able to find a way to actually extract image data so Panos's answer is the best option for reading in-cell image data.

There are a few different ways to do this, some of them use some undocumented APIs.

1. =IMAGE(<http url>)

The =IMAGE is a standard function which displays in image within a cell. It does almost the exact same thing as manually inserting an in-cell image.

2. Copied-by-value =IMAGE

Once you have an =IMAGE image you can copy it and paste it by-value which will duplicate the image without the formula (if you want that for some reason). You can do this in a script using the copyTo function:

srcImageRange.copyTo(dstRange, { contentsOnly: true })

This formula-less IMAGE is only distinguishable from a true in-cell image in that when you right-click on it is missing the "Alt text" and "Put image over cells" context menu options. Those options only show up on real in-cell images.

3. The undocumented CellImage APIs

When you call getValue() on a in-cell image (both formula and manually inserted) you get a CellImage instance.

CellImage

Prop/method	(Return) Type	Description
toString()	string	returns "CellImage".
getContentUrl()	?	always throws an error?
toBuilder()	CellImageBuilder	Convert this into an writable CellImageBuilder instance.
getAltTextDescription()	string	Returns the alt text description.
getAltTextTitle()	string	Returns the alt text title.
getUrl()	?	Doesn't seem to work, always returns undefined. :(
valueType	?	Same as SpreadsheetApp.ValueType, doesn't seem meaningful.

CellImageBuilder

Has all the same properties and methods as CellImage with these additional ones:

Prop/method	(Return) Type	Description
toString()	string	returns "CellImageBuilder".
build()	CellImage	Convert into a (read-only) CellImage instance.
setSourceUrl(string)	void	Update the image by supplying a web or data URL.
setAltTextTitle(string)	void	Sets the alt text title.
setAltTextDescription(string)	void	Sets the alt text description.

The major benefit I see with using this over IMAGE() is that it supports data URLs and therefore indirectly supports blobs.

Working Example Code

Keep in mind the undocumented APIs might change without notice.

Link to Example Spreadhseet

// 1 (or just use IMAGE in formula directly)
function insertImageFormula(range, httpUrl) {
  range.setFormula(`=IMAGE("${httpUrl}")`);
}

// 2
function insertImageValue(range, httpUrl) {
  range.setFormula(`=IMAGE("${httpUrl}")`);
  SpreadsheetApp.flush(); // Flush needed for image to load.
  range.copyTo(range, { contentsOnly: true }); // Copy value onto itself, removing the formula.
}

// 3
function insertCellImage(range, sourceUrl) {
  range.setFormula('=IMAGE("http")'); // Set blank image to get CellImageBuilder handle.
  const builder = range.getValue().toBuilder();
  builder.setSourceUrl(sourceUrl);
  builder.setAltTextDescription(sourceUrl); // Put url in description for later identification, for example.
  range.setValue(builder.build());
}

const DATA_URI = "data:image/gif;base64,R0lGODlhEAAQAMQAAORHHOVSKudfOulrSOp3WOyDZu6QdvCchPGolfO0o/XBs/fNwfjZ0frl3/zy7///"
  + "/wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAkAABAALAAAAAAQABAAAAVVICSOZGlCQAos"
  + "J6mu7fiyZeKqNKToQGDsM8hBADgUXoGAiqhSvp5QAnQKGIgUhwFUYLCVDFCrKUE1lBavAViFIDlTImbKC5Gm2hB0SlBCBMQiB0UjIQA7";

function test() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  sheet.clear();

  sheet.getRange(1, 1).setValue("IMAGE formula");
  insertImageFormula(sheet.getRange(2, 1), "https://www.google.com/images/icons/illustrations/paper_pencil-y128.png");
  
  sheet.getRange(1, 2).setValue("Copied-by-value IMAGE");
  insertImageValue(sheet.getRange(2, 2), "https://www.google.com/images/icons/illustrations/paper_pencil-y128.png");

  sheet.getRange(1, 3).setValue("In-Cell Image (Http URL)");
  insertCellImage(sheet.getRange(2, 3), "https://www.google.com/images/icons/illustrations/paper_pencil-y128.png");

  sheet.getRange(1, 4).setValue("In-Cell Image (DATA URI)");
  insertCellImage(sheet.getRange(2, 4), DATA_URI);

  sheet.getRange(1, 5).setValue("In-Cell Image (Blob DATA URI)");
  const blob = UrlFetchApp.fetch("https://www.gstatic.com/script/apps_script_1x_24dp.png").getBlob();
  insertCellImage(sheet.getRange(2, 5), blobToDataUrl(blob));
}

function blobToDataUrl(blob) {
  return `data:${blob.getContentType()};base64,${Utilities.base64Encode(blob.getBytes())}`
}