Google Script inserting an image without URL to Google Sheets

Question

My goal is to request an image from an endpoint that returns its contents (i.e a blob) and insert it to a cell. Note that the image has not been persisted anywhere, so it does not have a URL. In case I were a JavaScript web developer, I could use an Image object and set its src to the result of URL.createObjectURL(...). But I need to solve this in a Google Script, based on JS.

In the Google Script docs there is this example:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
sheet.insertImage(blob, 1, 1);

I would expect that this code gets the image from the endpoint and inserts it in cell A1:

function onOpen() {
  var SPREADSHEET_URL = 'MY_SPREADSHEET_URL';
  var SHEET_NAME = 'SHEET_NAME';

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

  var response = UrlFetchApp.fetch(
      "ENDPOINT_URL").getContent();

  var image = Utilities.newBlob(response, "image/jpeg", "img.jpeg");
  sheet.insertImage(image, 1, 1);
}

However, I get an error saying that the type of my image variable (that is Blob) it is not supported.

The blob format is unsupported. at onOpen.

The official docs say that the method newBlob returns a Blob object. What am I missing?

I thought that the error message of The blob format is unsupported., the retrieved data from the URL of ENDPOINT_URL might not be the image data. So, in your situation, can I ask you about whether ENDPOINT_URL is the direct link of the image? — Tanaike
Also I am not sure, if there is indeed an image in the webpage without a URL. I would like to be proven wrong and learn something new but I haven't seen an image without a url.. But your question depends 100% on what this "ENDPOINT_URL" so I don't think you can get an answer without reaviling some more info about it. — soMario
I have formulated my text a bit confusingly, sorry for that. I meant that ENDPOINT_URL is the URL to which the GET request is made, the server generates the image and returns it as an InputStreamResource. By image URL I meant the URL at which an image is stored (for instance, on AWS S3). Alas, just calling the insertImage method with ENDPOINT_URL solves my issue. — Eszter

Aerials Aerials · Accepted Answer · 2021-01-21T14:24:41

You can try using HTTPResponse.getAs(contentType) like:

var response = UrlFetchApp.fetch("ENDPOINT_URL").getAs('image/jpeg')

which returns a Blob. Then insert into sheets using Sheet.insertImage like:

sheet.insertImage(response, 1, 1);

And skip the creation of the Blob. But as the comments tell you, the validity of the object will depend on what lives at 'ENDPOINT_URL'

Google Script inserting an image without URL to Google Sheets

1 Answers