How to show image in Google Sheets Sidebar based on active cell

Question

I'm looking to create an "image viewer" in Google Sheets.

When the user runs the sidebar, I want it to show the image link that is contained in whatever cell is active.

There is more logic I want to build in (potentially changing the picture when the active cell changes in a specific column, etc), but for now I'm just stuck surfacing the image.

When I use a static link in the code (the first commented out line), I can see it in the sidebar perfectly.

However, when I try to retrieve the image link from the active cell, the sidebar works but with a broken image link.

Any thoughts? Is my image link possibly set up wrong? Not sure where to go from here - have tried editing it but no luck.

// @OnlyCurrentDoc

function onOpen() {
 SpreadsheetApp
   .getUi()
   .createMenu("Admin")
   .addItem("Admin page", "showAdminSidebar")
   .addToUi();
}

function showAdminSidebar() {

var ssThis = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ssThis.getActiveSheet();
var currentCell = activeSheet.getCurrentCell();


//// THIS LINK WORKS

//  var image = HtmlService.createHtmlOutput("<p><img src='https://pbs.twimg.com/media/FT-abnEWUAINPuV?format=jpg&name=4096x4096' /></p>");


//// USING LINK BASED ON CURRENT CELL: DOESNT SHOW IMAGE

  var image = HtmlService.createHtmlOutput("<p><img src="+currentCell+" /></p>");
 SpreadsheetApp.getUi().showSidebar(image);

 Logger.log(currentCell)
}

Sample sheet with image links in cells a10 and a11:

https://docs.google.com/spreadsheets/d/1QBukh3yTgbAl1j7-2rgG4Dsxwv2KxkMcIMFl6FnZogU/edit?usp=sharing

Answer 1

When I saw your script, currentCell of var image = HtmlService.createHtmlOutput("<p><img src="+currentCell+" /></p>"); is var currentCell = activeSheet.getCurrentCell();. In this case, the Class Range object is directly used. If you want to use the URL in the active cell, please modify it as follows.

From:

var currentCell = activeSheet.getCurrentCell();


////THIS LINK WORKS

//  var image = HtmlService.createHtmlOutput("<p><img src='https://pbs.twimg.com/media/FT-abnEWUAINPuV?format=jpg&name=4096x4096' /></p>");


////USING LINK BASED ON CURRENT CELL: DOESNT SHOW IMAGE

var image = HtmlService.createHtmlOutput("<p><img src="+currentCell+" /></p>");

To:

var currentCell = activeSheet.getCurrentCell().getDisplayValue();
if (!currentCell || !(/^https?:\/\/.*$/.test(currentCell))) return;  
var image = HtmlService.createHtmlOutput("<p><img src='" + currentCell + "' /></p>");

Reference:

• getCurrentCell()

Added:

When my proposed modification is reflected in your script, showAdminSidebar() is modified as follows. So, please replace your current showAdminSidebar() as follows, and test it again.

function showAdminSidebar() {
  var ssThis = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ssThis.getActiveSheet();
  var currentCell = activeSheet.getCurrentCell().getDisplayValue();
  if (!currentCell || !(/^https?:\/\/.*$/.test(currentCell))) return;
  var image = HtmlService.createHtmlOutput("<p><img src='" + currentCell + "' /></p>");
  SpreadsheetApp.getUi().showSidebar(image);
}