How can I return an image as the output of a google sheet custom function?

0
votes

I have a custom function in a google sheet. How can I return an image instead of normal data(for example number) as the output of this custom function ?

In fact I am going to read this image from image-charts.com site, which means that image-charts.com creates this image for us and gives us its url, and I actually want to be able to use this image that was created in a Google Sheet cell.

Can google sheet custom functions support image as the return value of the function?

Thanks in advance

1
google-sheetscustom-function
Could you give a bit more information on how you are making that requests? Is it calling a function via a button? A trigger? Calling the function in a formula? Depending on where you are doing it, you'll have different permissions and may/may not be possible to add an image. – Martí
Sure, there is no button and no trigger. It is only a function which is called in a formula. The script is executed and the output of that script is a link to an image (ie the link contains an image) and I want to display that image inside the cell. – Sophia

1 Answers

0
votes

Update

Found an answer to what you actually wanted. There is IMAGE (see reference), a function that adds an URL as an image into a cell (didn't know it existed). So you could do something like:

=IMAGE(GETCHARTURL(...))

Original workaround

You cannot add an image in a cell. You cannot show an image as the result of a formula either. The best What you can do is having a button that calls a Google Apps Script function that adds the images on top of the cells using Sheet.insertImage (reference).

I've made this little script that automatically adds the image on top of the cells that have the value img+ followed by the image url (eg. img+https://example.com/image.png):

const IMAGE_HEIGHT = 100 // Image height in pixels
function sameRange(a, b) {
  return a.getGridId() === b.getGridId()
    && a.getA1Notation() === b.getA1Notation()
}
function addImages() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const toChange = sheet
    .createTextFinder(/^img\+https?:\/\//.source)
    .useRegularExpression(true)
    .findAll()

  // Remove old image(s)
  for (let img of sheet.getImages()) {
    const range = img.getAnchorCell()
    if (toChange.some(r => sameRange(r, range))) {
      img.remove()
    }
  }

  // Create new ones
  for (let range of toChange) {
    const url = range.getValue().toString().split(/\+(.*)$/, 2)[1]
    const image = range.getSheet()
      .insertImage(url, range.getColumn(), range.getRow(), 0, 0)
    
    image.setAltTextTitle("Chart")

    const w = image.getWidth()
    const h = image.getHeight()
    image.setWidth(IMAGE_HEIGHT*w/h) // Keep aspect ratio
    image.setHeight(IMAGE_HEIGHT)
  }
}

Then simply make a new button (a drawing with text) and set addImages as the assigned script.

I don't know your exact use case so you will probably need to modify the script to better fit your needs.

References