I have a Google Sheet that looks like this:
That Google Sheet has some Google Script that looks like Google This:
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
var rng = ss.getActiveCell();
Logger.log("Range " + rng.getA1Notation() + " value = " + rng.getValue());
}
When I run that script with the sheet as seen above, the log has this text:
Range C3 value = Charlie's Horses
That's as expected. Now, I add a Filter View to the sheet so it looks like this:
When I run the code again, I get the same output:
Range C3 value = Charlie's Horses
However, when I sort that filter view, things go haywire.
I've sorted by column A descending but I still have "Charlie's Horses" selected. When I run the script, though, the logger has an unexpected output:
Range C9 value = Eye Doctor
It has the expected range address of C9
but the value is not what I'm seeing. As far as I can tell, that's because .getValue()
is pulling the data from the original, unfiltered view of the sheet.
Is there any way in Google Sheets / GAS to reference cells in a sorted filter view rather than the data in the unfiltered view?
The script that raised this issue needs to collect the values for cells on the same row in column A and C as well as getting / updating the comment on the cell in column C. If I'm able to reference the correct range object, I could work it out from there.