Using Built-in function inside Custom Function

Question

In google sheets, I'd like my custom function to use one of the built-in functions. Specifically, this function would take a string as a parameter, comb through another sheet to locate that value, and then return a link to that cell's address.

function href(findMe) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var hrefSheet = ss.getSheetByName("otherSheet");
  hrefSheet.activate();
  var rows = hrefSheet.getDataRange(); 
  var numRows = rows.getNumRows(); 
  var finds = rows.getValues()[1][0];

  var hrefCellRow;

  for(var i=0; i<numRows; i++){
    if(finds[i][0] == findMe){
      hrefCellRow = i+1;
      break;
    }
  }

  return address(hrefCellRow, 1); //address() is a function that is built in to sheets

}

So if I have a value "XYZ" in the "otherSheet" sheet, when I type

=href("XYZ")

I'd like it to try to find the value XYZ and return an address to the active cell. Extra kudos if it returns an actual link that when clicked, goes to that cell.

Answer 1

I didn't think of this before. I could just use the address function outside of the script and have the custom function embedded in the built-in function (instead of the reverse, which I tried to do). it's not as pretty, but it would be simply

=address(href(findMe),1)

Still haven't found a way to link to another cell, without perhaps creating a function that would reset the active cell?