Can't exclude header from selection - alteration to getRange?

[PINE x GINGER]

Hi everyone! I'm having a lot of trouble with what I thought would be a minor issue.

The goal of this function is to filter out "active dates" then delete all of the remaining "inactive dates", before removing the filter. Before I go into full detail, here's the full function code:

function deleteInactiveDates() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var startDate = ss.getRange('MissionControl!L5').getCell(1,1).getValue().toString();

 var criteria = SpreadsheetApp.newFilterCriteria()

   .setHiddenValues([startDate])

   .build();

 var nonActiveDates =

     ss.setActiveSheet(ss.getSheetByName('EnrollmentFile'));

    ss.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);

    ss.getRange('A3:L3').activate();

    ss.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

    ss.getActiveSheet().deleteRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());

     criteria = SpreadsheetApp.newFilterCriteria()

       .setHiddenValues([])

       .build();

   ss.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);

};

After the filter is up, I need to select all rows in my Sheet except row 1, the header, in preparation to be deleted. The catch is that some items have been filtered out to avoid deletion. 

The issue is the getRange on this line:

ss.getRange('A3:L3').activate();

At the moment it is set to A3:L3 because it's the only way it will work on the current dataset. This is because row 2 is hidden, per the filter, and the script will not run successfully if getRange is set to 'A2:L2'. But as I'm trying to develop a script which will be able to process multiple datasets, it needs to be able to handle instances where row 2 is hidden by the filter and instances where it is not.

To this end, is there any way to modify getRange to select the second unfiltered row, whether that row is row 2 or row 22? Alternatively, is there a way to select every row except row 1? I've attempted using the offset function to do this, but I've had no successful results. 

Thank you for taking a look

-G

[cbmserv...@gmail.com]

Have never played with Filters so not sure what the best solution for you is. However. if it is a simple filter you may be able to achieve the same actions by specifically looking at each row and hiding/unhiding if it meets your date criteria. That way you also can know what is the first row that meets your criteria as well.

 

A simple For loop would do the trick to hide all the rows that have the offending date in them. This may take a little more time than a filter as it is specifically acting on each row separately, but if your spreadsheet is not too large, the time difference will be insignificant.

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of PINE x GINGER
Sent: February 15, 2021 1:09 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: [Apps-Script] Can't exclude header from selection - alteration to getRange?

 

Hi everyone! I'm having a lot of trouble with what I thought would be a minor issue.

 

The goal of this function is to filter out "active dates" then delete all of the remaining "inactive dates", before removing the filter. Before I go into full detail, here's the full function code:

function deleteInactiveDates() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var startDate = ss.getRange('MissionControl!L5').getCell(1,1).getValue().toString();

 var criteria = SpreadsheetApp.newFilterCriteria()

   .setHiddenValues([startDate])

   .build();

 var nonActiveDates =

     ss.setActiveSheet(ss.getSheetByName('EnrollmentFile'));

    ss.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);

    ss.getRange('A3:L3').activate();

    ss.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

    ss.getActiveSheet().deleteRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());

     criteria = SpreadsheetApp.newFilterCriteria()

       .setHiddenValues([])

       .build();

   ss.getActiveSheet().getFilter().setColumnFilterCriteria(11, criteria);

};

 

After the filter is up, I need to select all rows in my Sheet except row 1, the header, in preparation to be deleted. The catch is that some items have been filtered out to avoid deletion. 

 

 

The issue is the getRange on this line:

 

ss.getRange('A3:L3').activate();

 

At the moment it is set to A3:L3 because it's the only way it will work on the current dataset. This is because row 2 is hidden, per the filter, and the script will not run successfully if getRange is set to 'A2:L2'. But as I'm trying to develop a script which will be able to process multiple datasets, it needs to be able to handle instances where row 2 is hidden by the filter and instances where it is not.

To this end, is there any way to modify getRange to select the second unfiltered row, whether that row is row 2 or row 22? Alternatively, is there a way to select every row except row 1? I've attempted using the offset function to do this, but I've had no successful results. 

Thank you for taking a look

-G

 

--
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/3b8a192e-d025-4147-b40a-376d140911f5o%40googlegroups.com.

[Michael O'Shaughnessy]

Just giving my 2 cents...  I would look at filtering an array.  Here are 2 good sites to look at:

https://medium.com/poka-techblog/simplify-your-javascript-use-map-reduce-and-filter-bd02c593cc2d

https://www.freecodecamp.org/news/javascript-map-reduce-and-filter-explained-with-examples/

Here is an example spreadsheet:

https://docs.google.com/spreadsheets/d/16NpnmfGFWYsQ_DHGJSOg0zmAI496z2UC7Mmu3qQAkCM/edit#gid=0

Click on it and make a copy.  Look at the script and change the "5" in line 9 to something like "8" then run the function.  It will filter the data and then put it back on the spreadsheet.

So you would do the following:

Get the data into an array

Get the header row

Filter the data

Clear the sheet

Put the filtered data back on the sheet

Hope this helps.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/000001d70414%242f6b57e0%248e4207a0%24%40gmail.com.

[PINE x GINGER]

This helps a lot! Thank you. I haven't been able to get this solution to work yet, but I'm currently working on putting it together because this approach seems very promising. 

Kindly,

G