How to return the cell the function is being written in?

Question

I'm struggling to find a solution to return the current cell a function is being written in.

For instance, when defining a range I want to make the first and second parameters (integer row and integer column) be dynamic.

If the cell I'm currently calling the function in is cell H1, then the integer row has to be 1 and the integer column has to be 8. Moving the function would also move the row and column.

sheet.getActiveCell() and sheet.getCurrentCell() is just returning null.

Google Apps Script Code

Google Sheets Example

In the first image above: Lines tagged as 1 are where the parameters need to be dynamic. Box number 2 is where I am trying to get the current cells value.

function dynamicRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var completePercentRangeStart = sheet.getActiveCell().getValues();

  var range = sheet.getRange(1, 8, 3, 1);

  return completePercentRangeStart;
}

[MCVE](https://stackoverflow.com/help/mcve) & [How To Ask](https://stackoverflow.com/help/how-to-ask): Your code should never be in an image. — tehhowch, Jan 25 '19 at 19:20
Also - it doesn't make sense: how can you be both **calling a custom function from a cell** and **have a value in that same cell**? As with all custom functions, just pass the associated cell as an argument to the function. Review the documentation. — tehhowch, Jan 25 '19 at 19:42
"As with all custom functions, just pass the associated cell as an argument to the function." How do you get the value of the cell to pass as an argument is exactly my question? — Brady Brown, Jan 25 '19 at 19:55
Literally the same way you pass it to any Google Sheets function: `B1: =MYFN(A1)` and whatever the value of A1 is, inside your `function MYFN(inputValue) {...}`, `inputValue` has it. Review the documentation on custom functions. — tehhowch, Jan 25 '19 at 19:59
possible duplicate of https://stackoverflow.com/questions/12206399/passing-cell-references-to-spreadsheet-functions/37764535 — HardScale, Jan 30 '19 at 11:50

Rubén · Accepted Answer · 2019-01-26T20:12:16.617

3

It not make sense to use getValues() on sheet.getActiveCell() because the active cell is a single cell and getValues() return an Array of Arrays (2D Array). Instead of it use getValue().

By the other hand, it not makes sense to return the value of the active cell using a custom function as it's some sort of circular reference.

Regarding

How to return the cell the function is being written in?

Assuming that this means the reference of the cell with a formula like =whoAmI()

function whoAmI() {
  var sheet = SpreadsheetApp.getActiveSheet();
  return sheet.getActiveCell().getA1Notation();
}

If H1 has the formula, it will display H1

edited Jan 26 '19 at 20:12

answered Jan 25 '19 at 22:24

Rubén

34,714
9
70
166

I did take the advice above on passing in the cells to the function instead of calling them out within the function. However, your answer with whoAmI() was exactly what I was looking for. Thank you! – Brady Brown Jan 26 '19 at 18:48
Why isn't it documented [what `getActiveCell()` and `getCurrentCell()` do when called from custom functions](https://stackoverflow.com/questions/28405787/get-calling-cell-address-in-google-apps-script-custom-function/76500686#76500686)? – Dan Dascalescu Jun 18 '23 at 13:14
@DanDascalescu I'm sorry, I don't understand your comment. – Rubén Jun 18 '23 at 15:50

How to return the cell the function is being written in?

1 Answers1