0

I'm trying to use Sheets API to update a CellImage.

I'd like to use Sheets API rather than SpreadsheetApp because it will be batched with several other operations.

Creating the cell image:

  const sheetId  = '0000000000000';
  const photoId  = '0000000000000';
  const image     = DriveApp.getFileById(photoId);
  const mimeType  = image.getMimeType();
  const base64    = Utilities.base64Encode(image.getBlob().getBytes());
  const cellImage = SpreadsheetApp.newCellImage()
                    .setSourceUrl(`data:${mimeType};base64,${base64}`)
                    .build();

This works:

  SpreadsheetApp.openById(sheetId)
                .getSheets()[0]
                .getRange(1,1)
                .setValue(cellImage);

But this results in "CellImage" written in the cell, rather than displaying the image:

  Sheets.Spreadsheets.Values.update(
    { values: [[cellImage]] },
    sheetId,
    'A1',
    {valueInputOption: 'RAW'}

I've tried valueInputOption: "USER_ENTERED" too, but the same result.

Is there a way of using Sheets API to update a cellimage?

Thanks!

user22309393
  • 3
  • 2

1 Answers1

0

(Possible duplicate)

Please have a look at this: Insert image into Google Sheets cell using Google Sheets API

This comment made by "Sam Berlin" is still viable

iniubong peter
  • 93
  • 6
  • Many thanks for the quick reply. Unfortunately, AFAIK the IMAGE formula option doesn't really work with images stored on Google Drive (the workarounds for that didn't really work for me, which is why I ended up with the CellImage solution). I did notice that this behaviour is the same as a closed issue on the SpreadsheetApp with setValues() versus setValue() - https://issuetracker.google.com/issues/220464196?pli=1 – user22309393 Jul 30 '23 at 14:53
  • Images stored in Google Drive can be used in the IMAGE formula. The format is as follows: `=IMAGE("https://drive.google.com/uc?export=view&id=")`. Look up [IMAGE DOCUMENTATION](https://support.google.com/docs/answer/3093333?hl=en) for the other parameters you can use to tweak how the image is presented. Also, look out for how the image file is shared(People with access) – iniubong peter Jul 30 '23 at 20:03
  • Thanks again for the response - unfortunately, I am restricted by how I can share the images meaning that workaround isn't an option for me. CellImage works great, I just was hoping I could batch that in with Sheets API but I guess not. – user22309393 Aug 01 '23 at 15:45