Get row from currently highlighted cell in google sheets

Question

I want to create a data validation list that automatically updates itself based on the current row. In other words, I want a dropdown in column C with all the values that have been entered into column C for the current value in column A.

I placed this in row 1 of a hidden column for my data validation list range: =SORT(UNIQUE(FILTER(C3:C,A3:A=C1,NOT(ISBLANK(C3:C)))))

It works perfectly except I have to manually update the value in C1 with the current column A value. Sub-optimal. I want the condition A3:A=C1 to be A3:A=C + CURRENTROW(), where CURRENTROW() is any kind of function or custom function that will return the, you guessed it, current row. I can't find anything that will give me either the current cell or the current row. I have tried the following custom functions:

function CURRENTROW() { 
  return  SpreadsheetApp.getActiveSheet().getActiveRange().getRow(); 
}

function CURRENTROW() { 
  return  SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
}

function CURRENTROW() {
  return  SpreadsheetApp.getActiveSheet().getCurrentCell().getRow();
}

All 3 variations return the row number of the cell that contains the function but I want the row number that the user is currently on. Can someone please show me how to get this value? Any help here is greatly appreciated.

Please provide an example spreadsheet. – PySeeker Oct 11 '19 at 14:14 — PySeeker, Oct 11 '19 at 14:14

score 0 · Accepted Answer · answered Oct 11 '19 at 14:31

Based on your little information you could do something like this, but just works after an edit is made in column c and will update the number in D1:

function onEdit(e) {
  if (SpreadsheetApp.getActiveSheet().getActiveRange().getColumn()==3){
    var rownum = SpreadsheetApp.getActiveSheet().getActiveRange().getRow()
    SpreadsheetApp.getActiveSheet().getRange('D1').setValue(rownum); 
  }
}

score 0 · Answer 2 · answered Oct 11 '19 at 15:22

The following code would work in your case:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Custom function', 'custom')
      .addToUi();
}

function custom() {
  var currentRow = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
  var formula = "=SORT(UNIQUE(FILTER(C3:C,A3:A=C" + currentRow + ",NOT(ISBLANK(C3:C)))))";
  SpreadsheetApp.getActive().getRange("A1").setValue(formula);
}

What it does is to set the formula you want in the A1 cell (which you can change as you please, in the last line). In order to call the function, it gets installed in a dropdown menu when you open your sheet, and after selecting the cell you want to be referenced you just have to click on it.

Get row from currently highlighted cell in google sheets

2 Answers2