Get calling cell address in Google Apps Script custom function

Question

I want to use the cell address (of the cell the formula is in) in a custom formula. e.g. In my sheet I have a formula in cell C7: =fSomeFormula() In my code I need something like

function fSomeFormula(){
  var a = source.getA1Notation();
  var b = source.getRow();
  var c = source.getColumn();
  return [a,b,c]; //returns ["C7",7,3]
};

Is this possible? Thanks in advance..

Garcia

Are you trying to get the cell to first calculate something, then refer to itself to make yet another calculation? If so, wouldn't this create a circular reference error? – Fennell

I want to return an array which size depends on the address of my formula. I have solved it now by passing an extra 'size' variable. – Garcia

Answer 1

This is not documented, but calling getActiveCell() or getCurrentCell() in custom functions returns the Range of the cell calling the function, not the "active" cell or "the cell that has focus in the Google Sheets UI, and is highlighted by a dark border".

So the answer is,

function currentCell() {
  const cell = SpreadsheetApp.getActiveSheet().getCurrentCell();
  return `[${cell.getA1Notation()}, ${cell.getRow()}, ${cell.getColumn()}]`;
}

Inimitable

Answer 2

You can use one of the built-in functions.

=ADDRESS(ROW(), COLUMN(), 4)

Celiotomy

Hi Amit, that is not going to work, I need the address in my formula to calculate with. – Garcia

You can write a custom function with parameters and pass row() and column() to the function. – Celiotomy

Thanks, that does the trick but I was looking for a more elegant solution. I guess the answer to my question is 'no' then :-) – Garcia