2

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.

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

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

  • 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. – Engineer Toast May 31 '18 at 19:39
  • @EngineerToast That was I mean by "not for handling filtered views" – Rubén May 31 '18 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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