Reference Cells in Sorted Filter View

Question

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.

Answer 1

On April 11, 2018 Google added new classes to handle filter but not for handling filtered views.

The alternatives are

• do the filtering on the script side and for that use the array handling features of JavaScript
• use the Google Sheets Advanced Service.

Reference

• https://developers.google.com/apps-script/releases/
• Using Google Sheets filters in Add-ons with Google Apps Script