google-apps-scriptgoogle-sheetsgoogle-sheets-custom-function

Get row from currently highlighted cell in google sheets


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.


Solution

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