Google Sheets - Track Current Cell Selection?

2
votes

How to Get the currently selected cell/range? was asked 3.5 years ago, but I'm hoping maybe some progress was made since then.

I'd like to make an interactive spreadsheet where there's a boolean grid that will display more information about the status of the currently selected cell in a static location, allowing users to quickly navigate using the arrow keys.

Referencing the previous instance of this question, I tried using the custom function below to get the cell address, although this isn't very dynamic.

function currentCell() {
  return SpreadsheetApp.getActive().getActiveRange().getA1Notation();
}

When testing, this function returns the address of the cell the function is called from. I've tried pressing F5 while highlighting another cell and nothing happened. I've also dragged the formula across a selection and each cell only contains its own address (according to the documentation, it should contain the range).

I've also read Henrique's post on caching from 2012, and while I don't completely understand it, I get that we could not get scripts which update without edits being made or manual formula updates.

I would ideally like for the current cell to be tracked without any extra prompts from the user besides arrow key navigation or clicking to select a cell, but if pressing a non-altering key (like Enter) would allow for tracking, that would be fine. I also only need the selected cell, rather than a range, if that makes things easier.

Explicit Question: Is it possible to track the position of the currently selected cell in Google Sheets and store that position in a separate cell? If not, is there a way to do this without altering cell values?

EDIT: Adding an example for clarity. In this conceptual example, A2 =currentCell() as described above. If I were to hit the right arrow key, A2 would update to display I4, since it is now the current selection. Current selection position stored in A2.

EDIT2: Jason Allshorn's response to Google app script monitor spreadsheet selected ranges appears to give a way of displaying the current selection (range) on the HTML input text. I'd like to do this, but store the value in a static cell on the sheet rather than input text. I'm new to scripting in Sheets and really only know what I've found for resolving this issue, so if I'm overlooking something, please let me know. I did look at Jason's example sheet and it wasn't clear to me what was actually happening there.

1
google-apps-scriptgoogle-sheetscustom-function
What do you mean may "store that position in a cell"? What cell? (the same cell or another cell? How the position store cell will determined and when the stored value should be updated? – Rubén
Possible duplicate of Google app script monitor spreadsheet selected ranges – tehhowch
@Rubén Another cell. Ex: I could set A1 to =currentCell() and then as I click on different cells in the sheet A1 will display those locations in A1 notation. (Edited for clarity) – Vince
@tehhowch I believe that comes very close to answering my question, as the answer there seems to include a way to automatically update the location at a high frequency, but I'm looking to store the A1 notation in a cell (edited the OP to reflect this more clearly) and unfortunately I'm VERY new/unfamiliar with using scripts so I'm unsure of how to utilize the HTML input text referenced there. – Vince

1 Answers

1
votes

You will have to apply the technique suggested on How do I make a Sidebar display values from cells?. This consist on using using client-side code to constantly pulling values from the spreadsheet and then do the corresponding task.

One approach is to use a side-panel with an input field to set the cells to be tracked and another input field to set the destination cell.