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

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 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

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

CellImageBuilder

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

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