Issue:
There is no native way to trigger actions on hover events in Sheets, and there is no way to retrieve images that have been uploaded from the computer and don't have a valid URL. This greatly limits what you can accomplish.
Nevertheless, there are workarounds available that can get you close to the functionality you're asking for.
Workaround (showModelessDialog):
You could, for example create a modeless dialog whose purpose would be to show the image corresponding to the selected cell.
Ideally, onSelectionChange trigger would be used, since this trigger to refresh the modeless dialog with current image when the user changes the selected cell, but since creating a modeless dialog requires authorization, simple triggers cannot run services that require authorization, and onSelectionChange is only available as a simple trigger (cannot be installed), that won't be possible.
So one possible workflow could be the following:
- Show a modeless dialog when the spreadsheet is opened by a user.
- The user selects the cell with the image that should be shown.
- User clicks a button in the modeless dialog (or the previous image) to refresh the modeless dialog with the currently selected image.
How to do this:
To achieve this, you can follow these steps:
- Install onOpen trigger that creates modeless dialog when the user opens the spreadsheet (the trigger needs to be installed, since, as I mentioned, simple triggers cannot use services that require authorization). To do that, go to the Apps Script editor (selecting
Tools > Script editor), copy this function to your Code.gs file, and install the trigger following these steps:
function onOpenTrigger(e) {
var html = HtmlService.createTemplateFromFile("Page").evaluate()
.setWidth(800) // Change dimensions according to your preferences
//.setHeight(600) // Change dimensions according to your preferences
SpreadsheetApp.getUi()
.showModelessDialog(html, "My image");
}
- Create the HTML template corresponding to the modeless dialog. The idea here would be: when the page loads, the currently selected image is shown. If the button (or the image itself) gets clicked, the page will refresh with current image (see
retrieveImage and refreshImage). Create an HTML file in the editor via File > New > HTML file, and copy the following code:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body onload="retrieveImage()">
<img id="currentImage" onclick="retrieveImage()" alt="No image is selected" width="600">
<button onclick="retrieveImage()">Click to refresh image!</button>
</body>
<script>
function retrieveImage() {
google.script.run.withSuccessHandler(refreshImage).getSelectedImage();
}
function refreshImage(imageUrl) {
if (imageUrl) document.getElementById("currentImage").src = imageUrl;
}
</script>
</html>
- Back in your script file (
.gs), create a function to retrieve the image URL from current cell (in case this cell has an image). This function will get called by the modeless dialog when it loads and when its button is clicked. It could be like this (regex is used to retrieve that — see this answer:
function getSelectedImage() {
var formula = SpreadsheetApp.getActiveRange().getFormula();
var regex = /=image\("(.*)"/i;
var matches = formula.match(regex);
return matches ? matches[1] : null;
}
Note:
- You can adapt the dimensions of both the selected image (
<img width="" height="">) and the modeless dialog (.setWidth, .setHeight) according to your preferences.
Reference:
