Insert multiple images from an array of blobs to column range in google sheet

[maarofi]

Greetings Folks, 

I post to the community from time-to-time when I come across issues/limitations with apps script. It helps me and both the community to share ideas that other can use for future references. 

Being an apps script developer, I am working on tasks to grab images from internet and set them as cell values in a column. I know about the `=IMAGE(source,  alt_text, height, width)` formula and have good knowledge of how to set formulas in cells using apps script. 

The task I am working on has a portion of code that looks like following: 

const sheet = SpreadsheetApp.getActiveSheet();

const imagesURLs = sheet.getRange('B2:B1000').getValues().map((link) => { return link[0] };

const responses = URLFetchApp.fetchAll(imagesURLs);

responses.forEach((response, ind) => {

    const image = response.getBlob();

    sheet.insertImage(image, 1, index + 2,  20,  60).setHeight(50).setWidth(50);

  });

The above solution is working and at the end I get all 999 images fetched from the links and placed in the column A of my Google Sheet. 

The only problem I am facing now is that this happen very slowly (25 to 30 minutes), instead of looking through the blobs and setting images in cells one-by-one, I want to convert this into batch operation, something like this: 

const sheet = SpreadsheetApp.getActiveSheet();

const imagesURLs = sheet.getRange('B2:B1000').getValues().map((link) => { return link[0] };

const blobs = URLFetchApp.fetchAll(imagesURLs).map((response) =>{

    return response.getBlob();

});

sheet.getRange('A2:A1000').insertImages(blobs); // unfortunately this is not possible

But it's not possible to set images as bulk in range like we do setValues(values) on a range. I did search online and didn't find anyone who overcome this issue. Will really appreciate suggestions and ideas on this. 

Note: I need the image in cell as cell content and not a formula  

[maarofi]

Update on the above query...

I did try to use cellImageBuilder but that takes the same amount of time, please have a look at the following version; 

function getCellImage (link) {

  const cellImageBuilder = SpreadsheetApp.newCellImage();

  cellImageBuilder.setSourceUrl(link);

  

  return cellImageBuilder.build();

}

function main () {

    const sheet = SpreadsheetApp.getActiveSheet();

    const imagesURLs = sheet.getRange('B2:B1000').getValues().map((link) => { return link[0] };

    const cellImageBuilderArr =  imagesURLs.map((link) => { return [getCellImage(link)] });

    sheet.getRange(2, 1, cellImageBuilderArr.length, 1).setValues(cellImageBuilderArr);

}

I hope there are other approaches that I do not know about, still waiting for new ideas and suggestions on this.