0

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

Aamir Khan Maarofi
  • 157
  • 2
  • 13
  • I am open to use the advance Sheets API V4 or external libraries (if there is a solution you have in mind). – Aamir Khan Maarofi Jan 19 '23 at 12:03
  • Unfortunately, the Sheets API V4 won't do it, according to this answer: https://stackoverflow.com/a/43665027 – Aaron Dunigan AtLee Jan 19 '23 at 13:20
  • Have you experimented with using the CellImage class? You could make multiple calls to [`SpreadsheetApp.newCellImage()`](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#newCellImage()), and then call `setValues` just once with an array of CellImages; I'm not sure if this will improve performance though. – Aaron Dunigan AtLee Jan 19 '23 at 13:24
  • @AaronDuniganAtLee, I appreciate your response. I did try to create `cellImageBuilderArray ` for all the image urls, but `range.setvalues(cellImageBuilderArr)` takes the same amount of time. – Aamir Khan Maarofi Jan 19 '23 at 16:54

0 Answers0