inserting an image in a google sheet with a custom function from sheet it self

Question

I am trying to create a google spreadsheet that will list and display creatures from a game I play.

In this project i have custom function that you can call from the spreadsheet like this:

=getAllMons("" ,"","" ,"" ,"" ,4, "beast","", "" ,"", "", "", "tail-the-last-one", "horn-imp")

with multiple different search params.

My issue is that the first column should contain the image of the monster. I thought that building a formula in the array as a string would work and render the images when geAllMons is called from a sheet. But it doesn't

Here is an example of one row in my arrays:

var Stats = [ '=image(\"' +a.image +'\", 1)', a.id, a.name, a.class, a.stage,
              a.level, a.exp, a.pendingExp, a.breedable, a.breedCount ,
              a.stats.hp, a.stats.speed, a.stats.skill, a.stats.morale,
              a.parts[0].name, a.parts[0].class, a.parts[0].type, a.parts[0].stage,
              a.parts[1].name, a.parts[1].class, a.parts[1].type, a.parts[1].stage,
              a.parts[2].name, a.parts[2].class, a.parts[2].type, a.parts[2].stage,
              a.parts[3].name, a.parts[3].class, a.parts[3].type, a.parts[3].stage,
              a.parts[4].name, a.parts[4].class, a.parts[4].type, a.parts[4].stage,
              a.parts[5].name, a.parts[5].class, a.parts[5].type, a.parts[5].stage,
              a.owner, a.sireId, a.matronId, a.birthDate, a.title
            ];

I pretty sure the issue is that the content is still in an active formula because when i select the whole data and copy paste as plain text. Image formulas get processed and I get image. It also work when i run my function from google app script editor, but this is not a solution because I dont want people to have to go in script editor every time they want to search for a different thing, also a lot lot less intuitive.

I have searched and most solution are custom function that you run from script editor and append the image with a new row or use a method to setValue, setImage. Those dont work with in-sheet custom functions.

Any body have an idea? Would be very appreciated.

Here is what i get:

and ere what i should get:

Unfortunately, the formulas cannot be put to the cell using the custom function. I think that this is the specification. So in your case, how about directly putting the formulas using Google Apps Script without the custom function? For example, the script can be run by the custom menu, the OnEdit event trigger and so on. I'm not sure about your whole script. So I posted this as a comment. If this was not the direction you want, I apologize. — Tanaike
Thank you for the confirmation. Yes thats pretty much my only option. The only issue is that I wanted it to be possible to call it anywhere on the sheet with different params. I concluded that I'll have to take of the image formula for the custom function and use the images in an other structure. Maybe a dashboard-like interface with predefined locations for the lists so i can append or insert formulas. I will leave the question open to see other people idea and solutions — Guillaume M-D

Iamblichus Iamblichus · Accepted Answer · 2019-12-10T12:39:33

As Tanaike said, you cannot set a formula in a cell using a custom function. Fortunately, though, there are several ways which can be followed as a workaround, whose validity depend on the workflow you desire here:

Workaround 1

Depending on your preferences, you could use an onEdit trigger that will only fire if the edited cell is in the first column and in the desired sheet (called Sheet1 in the example below, please change accordingly). The idea would be that you write all parameters in your function separated by, for example, a comma, and the function triggered by the edition will use split to separate the string into the different values, and write them in different columns. In the first column, corresponding to the image, setFormula can and will be used successfully. Something like this:

function onEdit(e) {
  var cell = e.range;
  var sheet = cell.getSheet();
  var row = cell.getRow();
  if (cell.getColumn() == 1 && sheet.getName() == "Sheet1") {
    var value = cell.getValue();
    var values = value.split(",");
    cell.setFormula("=image(\"" + values[0] + "\")");
    for (var col = 1; col < values.length; col++) {
      sheet.getRange(row, col + 1).setValue(values[col]);
    }
  }
}

Workaround 2

An option that would take a bit more effort, but could be more appropriate depending on your circumstances, would be to use a sidebar that would contain a form where all data from the monster would be added. When the form is submitted, this data would be written in the spreadsheet, in the row that is indicated.

Below is an example where the monster data has only three fields, but that can be useful in order to get the idea.

First, create a custom menu when the spreadsheet is opened (via an onOpen trigger), which will be used to show the sidebar with the form:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Custom Menu')
      .addItem('Add monster', 'showSidebar')
      .addToUi();
}

When the option Add monster is clicked, a sidebar is generated out of an html file:

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('sidebar')
    .setTitle('Set new monster')
    .setWidth(300)
  SpreadsheetApp.getUi()
      .showSidebar(html);
}

Here is the html file out of which the sidebar is created:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form onsubmit="handleFormSubmit(this)">
      <label for="row">Choose a row to write monster data</label>
      <input type="number" name="row"><br>
      <label for="image">Add a monster image url</label>
      <input type="text" name="image"><br>
      <label for="id">Add the monster id</label>
      <input type="number" name="id"><br>
      <label for="name">Add the monster name</label>
      <input type="text" name="name"><br>
      <input type="submit" value="Submit">
    </form>
  </body>

  <script>
    function handleFormSubmit(formObject) {
      google.script.run.addMonster(formObject);
    }  
  </script>
</html>

This file contains a form with the fields to be added (the row index to be edited and the data corresponding to the monster to add). Also, when the form is submitted, a function called addMonster that gets the data from the form as parameter (formObject) is fired:

function addMonster(data) {
  var row = data["row"];
  var url = data["image"];
  var id = data["id"];
  var name = data["name"];
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  sheet.getRange(row, 1).setFormula("=image(\"" + url + "\")");
  sheet.getRange(row, 2).setValue(id);
  sheet.getRange(row, 3).setValue(name);
}

This last function gets the data from the form and writes it in the row that was indicated in the form itself. In this way, you can successfully use setFormula to show the image.

Please bear in mind that this is a very simplified example and could be expanded and improved in many ways. Its purpose is just to show the idea behind this.

I hope this is of any help.

inserting an image in a google sheet with a custom function from sheet it self

1 Answers

Workaround 1

Workaround 2