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

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

CodePudding user response：

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);
}

CodePudding user response：

Sidebar Image Viewer

GS:

function myfunk() {
  let html = HtmlService.createTemplateFromFile("ah2").evaluate().setTitle("Image Viewer");
  SpreadsheetApp.getUi().showSidebar(html);
    }

function getLinkId() {
  return "image file id";//file id of image you shared to public
}

html:

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
  <img src="https://drive.google.com/uc?id=<?=getLinkId()?>" />
 </body>
</html>