Adding Images Efficiently to a Google Spreadsheet

Question

I am exploring using a GAS script to build a human-readable product catalogue as a Google Spreadsheet, it's easy to generate a PDF or print from there. The product data is all quickly accessible via an API including image URLs for each product.

I'm running into issues because inserting an image which references a URL, then re-sizing it takes 3-4 seconds in my prototype, and I might have 150x products. Runtime is capped at 6 minutes. Here's a simplified example of the image processing loop that I'm imagining:

function insertImages(sheet, array_of_urls) {

  for (var i in array_of_urls) {
  
    let image = sheet.insertImage(list_of_urls[i], 1, (i+1)*3);
    
    image.setWidth(90);
    image.setHeight(90);
    
  }
  
}

I think it takes so long because of the interaction with the UI. Can anyone recommend a way that I could make the script functionally efficient?

Answer 1

Insert images over cells:

If you want the images over cells (that is, not contained in a specific cell), I don't think there's a way to make this significantly faster. There's no method to insert multiple images at once.

You could at most try to retrieve the image blobs, resize the images through some third party before inserting them, and finally insert them via insertImage(blobSource, column, row).

In any case, there are ways to get through the 6 minute execution time limit. See, for example, this answer.

Insert image in cells:

If you don't have a problem having the images in specific cells, and not over cells, I'd suggest adding the images via IMAGE formula, using setFormulas.

The image size can be set through the IMAGE formula, the following way:

=IMAGE("URL", 4, [height in pixels], [width in pixels])

Also, to make sure the cells' height is large enough for the images to be seen, you can use setRowHeights.

Code snippet:

function insertImages(sheet, array_of_urls) {
  const formulas = array_of_urls.map(url => ["=IMAGE(\"" + url + "\", 4, 90, 90)"]);
  const firstRow = 1;  
  sheet.getRange(firstRow,1,formulas.length,formulas[0].length).setFormulas(formulas);
  sheet.setRowHeights(firstRow, formulas.length, 90);
}