How to add hyperlink to cell in google sheet sidebar addon

Question

This shared spreadsheet has a side bar (opened via the custom menu Extensions Menu)

The side bar displays a list of cells within the spreadsheet. Currently hard coded A1,A2, ... (in SideBarTemplate.html in the script Editor)

Is it possible to make a link to a specific cell ?

The last line displayed in the side bar This is HyperLink test to A1 is an attempt to use an href tag with a link to A1 (href="#gid=1744285851&range=A1") but this is not working.

Side Bar image:

GAS code:

function onOpen(){

  addMenu();
  
}

function addMenu(){
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'ShowSideBar', functionName: 'showSideBar'}
    ];
  spreadsheet.addMenu ('Extensions Menu', menuItems);
}


function showSideBar(){
  // Display a sidebar with custom HtmlService content.
  var htmlTemplate = HtmlService
                   .createTemplateFromFile("SideBarTemplate");
  
  htmlOutput = htmlTemplate.evaluate();
 
  htmlOutput.setTitle('Ext. Side Bar');
  
  SpreadsheetApp.getUi().showSidebar(htmlOutput);
  
}

HTML template:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
.style1 {
  color: blue;
  
}
.style2 {
color: red;
}
</style>
  </head>
  <body>
    <?var cells = ["A1","A2"]
    for (var i = 0; i< cells.length;i++){?>
   
    <div class="style1">Cell: <?=cells[i]?> <p class="style2">Required: How to make the blue text to hyper link to cell <?=cells[i]}?>
    </p></div>
    
    <a href="#gid=1744285851&range=A1">This is HyperLink test to A1</a>
  </body>
</html>

we are only allowed to view the file. We can't edit or see script/add on menu. — soMario
Ok. Why do you want this? What do you hope to achieve by doing this? — TheMaster
@TheMaster this is exactly what I want to achieve. list of hyper links to listed cells. Just for the sake of the example the list is hard coded set to [A1,A2], but it will dynamically be changed in the actual project - displaying predecessors range(s) for the active cell — OJNSim
@TheMaster There is no special motivation but be user friendly. Just navigate to the cell, if that is what the user wants. — OJNSim

TheMaster TheMaster · Accepted Answer · 2020-08-25T15:31:14

As documented in this answer, the sidebar frame sandbox attribute lacks allow-top-navigation and therefore, it is not possible to navigate the top frame(including hash change).

Workarounds:

Use server side range.activate() methods. Using google.script.run, you can change the active range in google sheets:

//client side
a1buttonElement.addEventListener('click',()=>google.script.run.select("a1"))

//server side
const select = e => SpreadsheetApp.getActive().getActiveSheet().getRange(e).activate()

A twisted workaround to bypassing the sandbox limitation is by using a web-app. In this case, Google sheets should be opened by a web-app. If it is opened by a webapp, We can exploit the window.opener api to write to the top frame. See related answer.

How to add hyperlink to cell in google sheet sidebar addon

1 Answers

Workarounds: