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.

If this is true, than it won't be as easy as I hoped: productforums.google.com/forum/#!topic/docs/1Vab4UYYG_0 — ZAR
It's appalling that this doesn't work. You basically cannot use any existing spreadsheet functions in a custom function. If you try to use JOIN for example in a custom function, it errors out saying "JOIN is not defined". Likewise you cannot use other functions like SORT, ARRAYFORMULA, etc. I'm absolutely dumbfounded by this design decision; it renders the whole feature useless. — Triynko
Possible duplicate of Google Spreadsheet custom function made of built in spreadsheet functions — Rubén

ZAR ZAR · Accepted Answer · 2014-09-18T13:50:57

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?

Using Built-in function inside Custom Function

1 Answers