getActiveRangeList() not respecting filters

[Raul Flores, Jr.]

Interesting bug, any ideas for a workaround?

Please star in the issue tracker to promote. Link below has code and spells issue out clearly. Nice quick reading (use markdown view)!

getActiveRangeList() not respecting filters

Thanks,

r in Dallas, Tx

[Tanaike]

How about this answer? Please think of this as just one of several possible answers.

At first, it supposes that the table is put from the cell "A1:C3".

From the flow of issue tracker, in that situation, I thought that when the user selected the cell "B2" and "B4", the user might continuously select by the mouse. In this case, it seems that the range of "B2:B4" is selected, even when the filter is reflected. "getActiveRangeList()" and "getRange()" ignore the filtered rows. This might be related to this issue.

On the other hand, when the cell "B2" and "B4" is individually selected by the mouse, the selected cells are "B2" and "B4". So when your script is run under this situation, "B2" and "B4" are changed and "B3" is not changed.

If you want to continuously select the cells of filtered sheet, how about the following sample script? In this case, the script can be used for only above situation. So please be careful this.

Sample script

function incorrect() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getActiveRangeList().getRanges();
  const rangeList = ranges.reduce((ar, r) => {
    const topRow = r.getRow();
    const endRow = topRow + r.getNumRows();
    const column = r.getColumn();
    for (let row = topRow; row < endRow; row++) {
      if (!sheet.isRowHiddenByFilter(row)) ar.push(sheet.getRange(row, column).getA1Notation());
    }
    return ar
  }, []);
  sheet.getRangeList(rangeList).setValue("updated");
}

If I misunderstood your question and this was not the direction you want, I apologize.

[Raul Flores, Jr.]

Tanaike Sir! you rock. Thanks.

Please star the issue https://issuetracker.google.com/issues/152060515

Also, Her'es my update there:

For example, say there's a sheet with 7275 rows. User filters and is looking at 27 rows. Attempting to block select these 27 cells in a single column, if you use code that gets multiple ranges (even if in this case the user selects a block), because of the bug (or at least inconsistency) here, the code above would erroneously write all the rows from min to max in the range returned.

So, I have this workaround. But, it takes 632.965 seconds in this larger example.  So with some data you can see it's not workable in a sheet expected to support thousands of records.

Here's a portion of the workaround.  It can be pasted into a container bound script. Select multiple cells in the sheet. Go to editor and run the function. See the results. This is a pretty neat workaround, but as the contributor suggested it's something to take care with.

/**

*  here's a workaround sketch

*  

*  because "getActiveRangeList()" and "getRange()" ignore the filtered rows

*

*  please star issue https://issuetracker.google.com/issues/152060515

*

*  This workaround for this odd case provided in group forum

*  Thanks to Tanaike. post at

 *  https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/google-apps-script-community/frh0WTytx2s/86-NG0DEAwAJ

*

* @param newValue

*/

const applyNewValueWorkAround = function (newValue) {

   newValue = "test new value";

   try {

       const s = SpreadsheetApp.getActiveSheet();

       const filter = s.getFilter();

       if (filter) { //determine active rows and apply only to those

           SpreadsheetApp.getActiveSpreadsheet().toast('Filter detected. Beware block selections skipping large number of rows. Individually click your target cells. ');

            const ranges = s.getActiveRangeList().getRanges();

           const rangeList = ranges.reduce((ar, r) => {

               const topRow = r.getRow();

               const endRow = topRow + r.getNumRows();

               const column = r.getColumn();

               for (let row = topRow; row < endRow; row++) {

                   if (!s.isRowHiddenByFilter(row)) {

                       ar.push(s.getRange(row, column).getA1Notation());

                   }

               }

               return ar

           }, []);

           s.getRangeList(rangeList).setValue(newValue);

       } else { // apply to selected ranges

           s.getActiveRangeList()

               .getRanges().forEach(r => r.setValue(newValue));

       }

       replyStatus =

           `Applied '${newValue}' to active range list.`;

   } catch (e) {

       if (e instanceof TypeError) {

           // Handle exception

           console.log(e);

           throw 'Logged an unexpected error applying new value to the slected cell(s); please try again.'

       }

   }

};

[Tanaike]

Thank you for replying. I checked the star. When "isRowHiddenByFilter" is used, when the number of rows is large, the process cost becomes high. In this case, by using Sheets API, the cost can be reduced.

A simple sample script can be seen at https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011

[Raul Flores, Jr.]

Thanks again. I will check out the sheets sample to get filtered values.

I knew about the query approach which I love; however, I was having issues with it when called from google.script.run. Likely auth?

Nonetheless, I'm still getting used to the Sheets API, so thank you for the explanations.

Cheers and kind regards,

"Rudy" in Dallas, TX

On Wed, Mar 25, 2020 at 12:04 AM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. I checked the star. When "isRowHiddenByFilter" is used, when the number of rows is large, the process cost becomes high. In this case, by using Sheets API, the cost can be reduced.

A simple sample script can be seen at https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/731a3b69-be27-4f42-bb03-7d61f124056a%40googlegroups.com.

[Raul Flores, Jr.]

Greetings Tanaike, 

I've tried the Sheets API approach inside a container bound script and from a function call inside a sheets add-on.

However, I'm getting a long 404: 

"HttpResponseException: Response Code: 404. Message: <!DOCTYPE html>

<html lang=en> <meta charset=utf-8> <meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width"> <title>Error 404 (Not Found)!!1</title> <style> *{margin:0;padding:0}html,code{font:15px/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background:url(//www.google.com/images/errors/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background:url(//www.google.com/images/branding/googlelogo/1x/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat 0% 0%/100% 100%;-moz-border-image:url(//w...."

Any ideas?

On Saturday, March 21, 2020 at 10:35:53 AM UTC-5, Raul Flores, Jr. wrote:

[Tanaike]

Thank you for replying. Unfortunately, I cannot understand about your current situation. This is due to my poor skill. I deeply apologize for this. When I could correctly understand about your current situation and find the solution, I would like to answer it. I deeply apologize I cannot resolve your new issue soon.

[Raul Flores, Jr.]

No worries, sir. I will look again. I was not able to use the sample at your last link, the approach that uses Sheets API.

Lot's to learn. Your skill far surpasses most and certainly mine. Thanks for helping me.

On Thu, Mar 26, 2020 at 2:25 AM Tanaike <kanshi...@gmail.com> wrote:

Thank you for replying. Unfortunately, I cannot understand about your current situation. This is due to my poor skill. I deeply apologize for this. When I could correctly understand about your current situation and find the solution, I would like to answer it. I deeply apologize I cannot resolve your new issue soon.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ddbfcbd4-9eb2-449d-8434-0da65da3f292%40googlegroups.com.