CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gary Fuller
Gary Fuller

Reputation: 1

Google Sheets hide X rows based on today's date

I have named ranges with all the same number of rows (21), all named ranges begin with a cell containing a date. I would like to hide all rows below their date cell when that date is older than today's date. Or, hide the associated 21 rows immediately following the date. I am ignorant as to how to write scripts, but I will learn. I also found I cannot ask a question well, very sorry.

I found a script in stackoverflow to hide a row with a date earlier than today:

function hideRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("MON");
var v = s.getRange("A:A").getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
for (var i = s.getLastRow(); i > 2; i--) {
    var t = v[i - 1];
    if (t != "") {
        var u = new Date(t);
        if (u < today) {
            s.hideRows(i);
            }
        }
    }
}

When the sheet is opened I expect the row with the date earlier than today to cause the next 21 rows to be hidden.

Upvotes: 0

Views: 278

Answers (1)

Cooper
Cooper

Reputation: 64110

Try this:

This gets all of the ranges on the active sheet

function hideNamedRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rgA=sh.getNamedRanges();
  var dv=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()).valueOf();
  for(var i=0;i<rgA.length;i++) {
    var vA=rgA[i].getRange().getValues();
    if(vA[0][0] && new Date(vA[0][0]).valueOf()<dv) {
      sh.hideRows(rgA[i].getRange().getRow(), rgA[i].getRange().getHeight());
    }
  }
}

This does the same thing for the spreadsheet

function hideNamedRange() {
  var ss=SpreadsheetApp.getActive();
  var rgA=ss.getNamedRanges();
  var dv=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()).valueOf();
  for(var i=0;i<rgA.length;i++) {
    var rg=rgA[i].getRange();
    var sh=rg.getSheet();
    var vA=rg.getValues();
    if(vA[0][0] && new Date(vA[0][0]).valueOf()<dv) {
      sh.hideRows(rgA[i].getRange().getRow(), rgA[i].getRange().getHeight());
    }
  }
}

Upvotes: 0

Related Questions