4

I have a Google Sheet that looks like this:

01

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:

02

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.

03

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.

1
  • As far as I know, Google Apps Script and filter/filter views live in parallel universes, not being aware of each other.
    – user135384
    Commented Jul 11, 2017 at 21:12

1 Answer 1

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

2
  • I follow a thread on the support forums that keyed me in to the new classes. However, my specific issue is to get a value from a different cell in the same row as the current selection. From my testing, the update doesn't address that because nothing captures the sort order of the current view. Commented May 31, 2018 at 19:39
  • @EngineerToast That was I mean by "not for handling filtered views" Commented May 31, 2018 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.