Geek asks and answers

A script for google spreadsheet to provide multiple hyperlink selection for single cell

I have a google spreadsheet. In some cells, it has several names (strings) that I would like to associate with individual hyperlinks.

eg. if I have a cell, such as “Charles Darwin,” it’s easy for me to create a hyperlink from this name by doing something like =Hyperlink(VLOOKUP("Charles Darwin", People!$A$1:$B$738, 2, false), "Charles Darwin") (note that I have a “People” sheet from which I take a hyperlink)

But if I have several entries in this cell, say ";" or breaking up a new line, for example, “Charles Darwin, George Washington,” I cannot do this. I would like to give the user the opportunity to click on a cell, pass the contents of the cell (as an argument) to some script, and for this script to find hyperlinks in my “People” sheet for these lines, and then present the user with a small “pop-up window” next to with this cell, which you can click on the desired hyperlink.

I tried to find something in these lines on this forum, but nothing of the kind appeared. Can someone have a link or two for me (or a basic code example) with which I could start to try to solve this? (I assume this is possible).

thank

+5
1

.

Google Spreadsheets, , . , , :

  • , .
  • , .
  • ( , ) .

, ? ( Tools > Script Editor)

function onOpen() {
  SpreadsheetApp.getActive().
    addMenu("Test", [{name: 'Show Links', functionName:'showLinks'}]);
}

function showLinks() {
  var values = SpreadsheetApp.getActiveRange().getValue().split(';');

  var app = UiApp.createApplication().setTitle('Links'); 
  var grid = app.createGrid(values.length, 2);

  for( var i = 0; i < values.length; ++i ) {
    var url = findLink(values[i]);
    grid.setWidget(
      i, 0, app.createLabel(values[i])).setWidget(
      i, 1, url ? app.createAnchor(url, url) : app.createLabel('Not Found'));
  }

  app.add(grid);
  SpreadsheetApp.getActive().show(app);
}

var mapName2Url = null;
function findLink(name) {
  if( mapName2Url == null ) { //lazy load
    mapName2Url = {};
    var data = SpreadsheetApp.getActive().getSheetByName('People').getDataRange().getValues();
    for( var i = 1; i < data.length; ++i ) //skipping the header
      mapName2Url[data[i][0]] = data[i][1];
  }
  return mapName2Url[name];
}

, Script, onOpen, . , , .

, , .

+3

More articles:


All Articles