Return CellImage from Custom Function in Apps Script

Question

I'm trying to return a CellImage from a Google Sheets custom function. I build out the image in Apps Script:

function TESTIMG() {
    let oneImage = SpreadsheetApp
      .newCellImage()
      .setSourceUrl('https://bluewarelabs.files.wordpress.com/2022/10/cropped-bwl-logo-4.png')
      .build();

    return oneImage;
}

The function does not error out, but it produces blank cell. However, if just generate the image using

=IMAGE("https://bluewarelabs.files.wordpress.com/2022/10/cropped-bwl-logo-4.png")

It works fine.

Is this a known bug? Returning a CellImage not working?

Answer 1

As mentioned in the comments above, in order to put the Class CellImageBuilder object into a cell as the image object, it is required to put it using setValue or setValues. You can submit a feature idea here.

I guess there are other ways to achieve your goal but to give you an idea, I thought of the following workaround:

function insertImage()
{
  let images = [
    'https://bluewarelabs.files.wordpress.com/2022/10/cropped-bwl-logo-4.png',
    'https://developers.google.com/google-ads/scripts/images/reports.png',
    'https://i.imgur.com/gtfe7oc.png',
    'https://www.google.com/images/srpr/logo3w.png'
  ]

  let column = SpreadsheetApp.getActiveRange().getColumn();
  let row = SpreadsheetApp.getActiveRange().getRow();

  for (let i=0; i<images.length; i++){
    //Use this option to insert the IMAGE formula
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, column+i).setFormula('=IMAGE("'+ images[i] +'")');

    //You can use this option if you want to do it with the newCellImage method
    // let oneImage = SpreadsheetApp.newCellImage().setSourceUrl(images[i]).build().toBuilder();
    // SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, column+i).setValue(oneImage);
  }
}

Due to some limitations with custom functions in Google Sheets, this workaround can't be used as a custom function. Instead you can add an option in the menu and run the function from there so you don't have to run the script manually. To add the option in the Google Sheets menu, you can add the following in your script:

function onOpen( ){
// This line calls the SpreadsheetApp and gets its UI   
// Or DocumentApp or FormApp.
  var ui = SpreadsheetApp.getUi();
 
//These lines create the menu items and 
// tie them to functions we will write in Apps Script
  
 ui.createMenu('Custom Functions')
      .addItem('Instert Images', 'insertImage')
      .addSeparator()
      .addToUi();
}

Run the onOpen() function and go back to your Google Sheet and you will see the new option in the menu:

References:

• Class CellImage
• Custom Functions in Google Sheets - Return values