How to Get the currently selected cell/range?

Question

Is there a way to get the identification (i.e. A1 notation) of a selected cell or range in Google Sheets?

Something like:

=SELECTION(['Sheet1']) -> "D6"

Trihydric

use getActiveRange() you can read about it here – Creatine

Do you mean as a function in the spreadsheet, or by use of google-apps-script? – Jessie

I meant a spreadsheet function. I wanted to bring some interactivity to the sheet, so it would react to cell selection change. (Range not included). Last time I checked, that's was not possible. – Haemin

Answer 1

This custom function will get the selection at the time you update the cell with the function. But it will not update as the selection changes.

Henrique provides a good explanation of why custom functions like this don't update in Google Apps - script to summarise data not updating.

/**
 * A function that gets the current selection, in A1Notation.
 *
 * @customfunction
 */
function SELECTED_RANGE() {
  return SpreadsheetApp.getActive().getActiveRange().getA1Notation();
}

Jessie

As of Jun 2, 2024, there is no need to use a Google Apps Script / Custom Function. See my answer. This is relevant because the OP didn't include the google-apps-script and didn't mention it in the original version of the question. – Meet

Answer 2

One way to get the reference of the cell holding formula using only Google Sheets built-in functions is

=ADDRESS(ROW(), COLUMN())

---

From a OP's comment to the question

I meant a spreadsheet function. I wanted to bring some interactivity to the sheet, so it would react to cell selection change. (Range not included). Last time I checked, that's was not possible. – Ondra Žižka Oct 14, 2018 at 18:09

The built-in function to get a cell "identification", aka, reference, is ADDRESS. This function has existed for a long time, but I don't remember needing to handle the use case shown in the question, only to return the cell reference, before finding this question.

While using a custom function (Google Apps Script / JavaScript function) might do the same, this option might be overkilling and not so convenient:

1. It implies having a broader knowledge base as it requires using another web app, Google Apps Script, and a programming language, JavaScript.
2. It implies doing more steps to get the same result.
3. Custom functions have some limitations. The most relevant for this use case might be that a custom function is only recalculated when the spreadsheet is opened and when the parameters change.

Meet