Passing cell references to spreadsheet functions

Question

When I call a spreadsheet function, say int(f2), the function operates on the value in the cell. If cell("F2") contains 3.14159, the result would be 3. But when I call a different type of function — for example: row(f8) — the function takes the cell reference, and not the value, in this case, returning 8.

How do I get my custom function to work with the reference, rather than the value?

I can pass a string, and use getRange(), but, if I move or update the cells on the sheet, the strings won't change.

Really simple example:

function GetFormula(cellname) {
  return SpreadsheetApp.getActiveSheet().getRange(cellname).getFormula();
}

With this in my sheet's code, I can retrieve the formula in C4 like this: =GetFormula("C4")

But, this argument is a string, and I would rather pass a cell reference. A somewhat more complicated issue requires the calling cells to update when copied and pasted.

Any ideas?

Answer 1

I was working on this a few months ago and came up with a very simple kludge: create a new sheet with the name of each cell as its contents: Cell A1 could look like:

= arrayformula(cell("address",a1:z500))

EDIT: The above no longer works. My new formula for 'Ref'!A1 is

= ArrayFormula(char(64+column(A1:Z100))&row(A1:Z100))

Name the sheet "Ref". Then when you need a reference to a cell as a string instead of the contents, you use:

= some_new_function('Ref'!C45)

Of course, you'll need to check if the function gets passed a string (one cell) or a 1D or 2D Array. If you get an array, it will have all the cell addresses as strings, but from the first cell and the width and height, you can figure out what you need.