Google Apps Script - Insert Image into Spreadsheet cell using Google Drive ID

Question

As I can see from Spreadsheet UI you can insert images in two ways providing a file directly from your Google Drive

Choose to place your image in the cell or over the cells. Cells with an image cannot also have text.

but from AppsScript I'm unable to perform the "in the cell" mode.

The first reference is adding image giving directly from Blob source (so with AppsScript code you can download the blob content and provides it to the function), but is not suitable to my case.

To insert an image directly into the cell in a programmatically way, the only way I found is using =IMAGE() formula, but in this case a public url is required.

This is the solution I came up so far:

function getCdnImageUrl_(driveFile) {
  // https://stackoverflow.com/a/59537829
  let imageUrl = "https://docs.google.com/uc?id=" + driveFile.getId();
  console.info("Access to embeddable link %s", imageUrl);

  // Retrieve url of cdn disabling redirects
  let fetchResponse = UrlFetchApp.fetch(imageUrl, {
    followRedirects: false
  });

  // Access to 302 header "Location"
  let cdnUrl = fetchResponse.getAllHeaders()["Location"]
  console.info("CDN URL %s", cdnUrl);

  return cdnUrl;
}

function insertImageAsFormula_(range, driveFile) {
  // Retrieve url
  let url = getCdnImageUrl_(driveFile);

  // Set formula
  range.setFormula("=IMAGE(\"" + url + "\")");
}


function main() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getActiveSheet();
  let range = sheet.getRange("A1");

  let driveImageId = "my-drive-image-id";
  let driveFile = DriveApp.getFileById(driveImageId);

  // Set file as publicly available so CDN url will be callable for anonymous users
  driveFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

  // Retrieve url from CDN and set as formula
  insertImageAsFormula_(range, driveFile);

  // Force Spreadsheet to update view (download the image) otherwise when the file will be restored to "private" the link will provide HTTP 403
  SpreadsheetApp.flush();

  // Restore previous sharing access
  driveFile.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.VIEW);
}

Once Spreadsheet has downloaded the image, I found that there is no problem if the used url return authenticated because the image is already download and in view.

Can you confirm that, at this moment, there is no direct way to use AppsScript to add an Image inside a cell? I'm not very happy that I need to make the file public (even if for a short amount of time), do you have any suggestion on how to perform the same operation maintaining the file private?

EDIT

I ended up uploading the file from Google Drive to Google Cloud Storage in order to create a signedUrl Upload file to GCS Create signedUrl

With this approach the Drive file is and stays private, the generated url automatically expires after configured TTL and the url itself is not guessable at all as contains a signature.

When =IMAGE() is used, the image is required to be shared publicly. It seems that this is the current specification. When the access token had been able to be used as query parameter, this had been the workaround. But now, that cannot be used. So, as other workaround, at first, the images are manually put to a Spreadsheet with "in the cell" mode as a strage. When you want to an image to the Spreadsheet you use, the cell is copied from the storage Spreadsheet to the Spreadsheet you use. By this, your goal can be achieved. But if this was not the direction you want, I apologize. — Tanaike

Rafa Guillermo Rafa Guillermo · Accepted Answer · 2020-03-30T09:52:54

Can you confirm that, at this moment, there is no direct way to use AppsScript to add an Image inside a cell?

Answer:

I absolutely can confirm this.

More Information:

As adding images into cells without using the =IMAGE formula was added to Google Sheets in March 2019 and so at the writing of this post there is currently no way of doing this via Apps Script nor the API.

There is however, already a report on Google's Issue Tracker which requests this, posted 27^th March 2019:

Insert image in cell

You can hit the ☆ next to the issue number in the top left on the feature request which lets Google know more people want the feature to be implemented and so it is more likely to be seen to faster.

I hope this is helpful to you!

Google Apps Script - Insert Image into Spreadsheet cell using Google Drive ID

EDIT

1 Answers

Answer:

More Information:

References: