CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-sheets-formulaselectioncustom-function
Ondra Žižka
Ondra Žižka

Reputation: 46796

How to Get the currently selected cell/range?

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"

Upvotes: 20

Views: 46293

Answers (2)

Wicket
Wicket

Reputation: 38131

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.

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45710

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();
}

Upvotes: 24

Related Questions