google-apps-scriptgoogle-sheetsgoogle-sheets-macros

Google Sheets Hide Images When Hiding Rows

I have been trying to figure out a way to hide an image in google sheets when I am hiding rows that include the image.

The image is a button that has a script attached to it, which means that I cannot just use the =IMAGE() formula, and cannot use an image inside of a cell either.

The problem is that when I hide rows the image stays. Is there any way to get around this?

Solution

  • Answer:

    Unfortunately, there is no way of doing this.

    More Information:

    Images in a Google Sheet that are not inserted using the =IMAGE() formula, nor by inserting the image directly in a cell, are represented by an OverGridImage object in Google Apps Script.

    As you can see in the documentation for this class, there exists no method which allows you to hide the image, other than deleting it altogether.

    The reason that hiding cells/rows/columns does not hide the image either, is because the image is not tied to any indvidual cell, row or column - it is inserted over the grid system of a specific sheet.

    A Mix of Both Good and Bad News:

    Now, in theory, what you could do instead, is store the information about the image in the script properties, and then delete/insert the image again when you wish to hide it.

    Unfortunately, it appears that the .getUrl() method of the aforementioned OverGridImage class is bugged and does not return the URL of the image - this is something that I have checked myself.

    In this case, I would suggest going to the issue link for this bug and hit the ☆ next to the issue number in the top left to let Google know that more people need this to be seen to.

    I would also suggest filing a Feature Request with Google here, detailing that you would like to see the showing and hiding of overlay images implemented in Google Apps Script. The above link is directly for Apps Script feature requests.

    And for future readers: If you are encountering this, and the .getUrl() method has been fixed, then you can use the following functions as a workaround to show and hide images. This uses PropertiesService to save the image data into the script's properties, and then uses them to re-insert the image after deletion.

    function hideImage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var image = ss.getImages(); // assuming this is the only image
  var url = image[0].getUrl();
  var col = image[0].getAnchorCell().getColumn();
  var row = image[0].getAnchorCell().getRow();
  var xOffset = image[0].getAnchorCellXOffset();
  var yOffset = image[0].getAnchorCellYOffset();
  
  var details = [url, col, row, xOffset, yOffset];
  console.log(details);
  PropertiesService.getScriptProperties().setProperty("image", details.toString());
  image[0].remove();  
}

function showImage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var details = PropertiesService.getScriptProperties().getProperty("image").split(",");
     
  // change for your sheet name
  ss.getSheetByName("Sheet1")
    .insertImage(details[0], details[1], details[2], details[3], details[4])
  
}

    References: