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

Answer:

This is a new feature and unfortunately at current there isn’t a method to be able to get an image inserted into a Cell this way using Google Apps Script, nor using the Sheets API.

More Information:

Attempting to get the data in a cell using the spreadsheets.get method with the following parameters

  spreadsheetId: "ID of private spreadsheet created in Drive"
  includeGridData: True
  ranges: D7
  fields: sheets/data/rowData/values

Will return a 200 response, however the image data is not returned:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {},
                  "effectiveValue": {},
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Feature Request:

There is however a Feature request for this on Google’s Issue Tracker which you can find here. If you head over to the feature request page and click the star in the top left, you can let Google know that you also would like this feature, and will automatically get updates about its progress.

I hope this is helpful to you!

Answer 2

I believe your goal as follows.

• You want to retrieve the image in the cell of Google Spreadsheet using Google Apps Script.

Issue and workaround:

Unfortunately, in the current stage, there are no methods for retrieving the images in the cell on Spreadsheet in Spreadsheet service and Sheets API. This has already been mentioned by Rafa Guillermo's answer. So in this answer, I would like to propose a workaround for retrieving the images in the cells using Google Apps Script.

In this workaround, I use Microsoft Excel Data converted from Google Spreadsheet. Even when Google Spreadsheet is converted to Microsoft Excel Data, the images in the cells are not removed. I use this. Of course, the images can be also retrieved from HTML data converted from Spreadsheet. But in this case, the parse of HTML data is a bit complicated than that of Excel data. So here, I would like to propose to retrieve the images from Excel Data converted from Spreadsheet. The flow of this workaround is as follows.

1. Convert Google Spreadsheet to Microsoft Excel (XLSX data) using Drive API.
2. Parse XLSX data using Google Apps Script.
   • When the converted XLSX data is unzipped, the data can be analyzed as the XML data. Fortunately, at Microsoft Docs, the detail specification is published as Open XML. So in this case, Microsoft Docs like XLSX, DOCX and PPTX can be analyzed using XmlService of Google Apps Script. I think that this method will be also useful for other situations.
3. Retrieve images from XLSX data.

Pattern 1:

In this pattern, I would like to introduce a simple method.

Sample script:

function myFunction() {
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=" + spreadsheetId;
  const blob = UrlFetchApp.fetch(url, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}}).getBlob().setContentType(MimeType.ZIP);
  const xlsx = Utilities.unzip(blob);
  xlsx.forEach(b => {
    const name = b.getName().match(/xl\/media\/(.+)/);
    if (name) DriveApp.createFile(b.setName(name[1]));
  });
}

• In this sample script, all images in the Spreadsheet are exported as the files. So in this case, both images in the cells and over the cells from all sheets in the Spreadsheet are retrieved. And also, it cannot retrieve the cell coordinate that the image is in the cell.
  • In the current stage, there are no methods for retrieving the images in Google Spreadsheet as the blob. In this sample script, this can be achieved.
• This sample script cannot export the drawings. Please be careful this.
• When setContentType(MimeType.ZIP) is not used, an error occurs at Utilities.unzip(blob). Please be careful this.

Pattern 2:

In this pattern, the images are retrieved with the sheet name and cell coordinate from Spreadsheet. In this case, the script becomes a bit complicated. So here, I would like to introduce the sample script using a Google Apps Script library. Ref Of course, you can see the whole script there.

Sample script:

Before you use this script, please install DocsServiceApp (The author of this GAS library is tanaike.) of the Google Apps Script library. Ref And run the function of myFunction.

function myFunction() {
  const cell = "A1";
  const sheetName = "Sheet1";
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const obj = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getSheetByName(sheetName).getImages();
  console.log(obj)
  const blobs = obj.filter(({range, image}) => range.a1Notation == cell && image.innerCell);
  console.log(blobs.length)
  if (blobs.length > 0) DriveApp.createFile(blobs[0].image.blob);
}

• In this sample, the image in the cell "A1" of "Sheet1" in the active Spreadsheet is retrieved, and the retrieved blob is created to the root folder as an image file.

Note:

• In the current stage, when an image is inserted to Google Spreadsheet and the Spreadsheet is converted to XLSX data, the image including the XLSX data has the filename of image1, image2,,, which are not the original filename. So it seems that this is the current specification.
• When the images are retrieved from XLSX data, it seems that the image is a bit different from the original one. The image format is the same. But the data size is smaller than that of the original. When the image size is more than 2048 pixels and 72 dpi, the image is modified to 2048 pixels and 72 dpi. Even when the image size is less than 2048 pixels and 72 dpi, the file size becomes smaller than that of original one. So I think that the image might be compressed. Please be careful this.
• In the current stage, the drawings cannot be directly retrieved.

References:

• Understanding the Open XML file formats
• XML Service
• DocsServiceApp

Answer 3

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 4

I just tried something pretty basic and it worked. Maybe doesn't work in all cases, depends if you added the images previously through a formula...

• Add image through Google Apps Script :

var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  sheet.getRange('A1').setFormula('=IMAGE("https://developers.google.com/google-ads/scripts/images/reports.png")');

Worked (it's in the cell and will work auto fit on resizing) :

• Then to retrieve the image url from cell :

var imgVal = sheet.getRange('A1').getFormula();

var regEx = /"(.*)"/gm;
var url = regEx.exec(imgVal)[1];

Logger.log(url);

Logs will be :

Answer 5

This question is a little old, but since I faced today this problem, please allow me to share my experience.

I realized that the getValue() of the cell, returns an object that its text is "CellImage". This allows me to understand that there is an embedded image in this cell. This objects seems to be similar to (or the same) with the OverGridImage object. At least, you can use the getAltTextTitle and the getAltTextDescription methods.

By combining all these features, my workaround is:

• Add specific AltText to the image in the cell.
• Get the value of the cell in an object.
• Check if this is equals to "CellImage".
• If it is CellImage, get the AltText.
• Based on the value of this AltText do whatever you like.

The sample code follows:

/*-------------------------------------------------------------------------
 Custom event handler triggered when a single cell is selected in the spreadsheet.
 @param {Event} e The onSelectionChange event.
-------------------------------------------------------------------------*/
function onSingleCellSelected(e) {
  var cell = e.range.getCell(1, 1);
  var v = cell.getValue();
  if(v == "CellImage") {
    var altText = v.getAltTextTitle();
    Logger.log(v.getAltTextDescription());
    if(altText == "@action(recordTime)"){
      cell.setBackground("cyan");
    }
  }
}

Answer 6

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())}`
}