how do I change this google app script to hide sheets columns instead of rows based on column dates

Question

so I'm a long way form a competent programmer, but I do dabble in google sheets scripts.

I previously had a script running on a timer trigger to hide rows based on dates found in column A.

function min() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("OLD");
    var v = s.getRange("A:A").getValues();
    var today = new Date().getTime() - 86400000‬
    for (var i = s.getLastRow(); i > 1; i--) {
        var t = v[i - 1];
        if (t != "") {
            var u = new Date(t);
            if (u < today) {
                s.hideRows(i);
            }
        }
    }
}
function max() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("OLD");
    var v = s.getRange("A:A").getValues();
    var today = new Date().getTime() + 86400000 
    for (var i = s.getLastRow(); i > 1; i--) {
        var t = v[i - 1];
        if (t != "") {
            var u = new Date(t);
            if (u > today) {
                s.hideRows(i);
            }
        }
    }
}
function SHOWROWS() {
  // set up spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet(), sheets = ss.getSheets();

  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    // get sheet
    var sh = sheets[i];

    // unhide rows
    var rRows = sh.getRange("A:A");
    sh.unhideRow(rRows);
  }
}

this would be set to run at midnight every night so as to hide all rows not +-1 day from the current date.

I have now switched to using this document primarily through the android app I need to swap the input layout Moving dates from rows and values in columns to the inverse, values are now in rows and the dates are in columns, inputing vertical values would be much quicker....but honestly its more about understanding what im doing wrong thats really motivating my search for a answer.

can anyone help me modify my old script to work on this changed sheet. my attempts fall flat..eg:

function min() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("OLD");
    var v = s.getRange("1:1").getValues();
    var today = new Date().getTime() - 86400000‬
    for (var i = s.getLastColumn(); i > 1; i--) {
        var t = v[i - 1];
        if (t != "") {
            var u = new Date(t);
            if (u < today) {
                s.hidecolumns(i);
            }
        }
    }
}

example spreadsheet: https://docs.google.com/spreadsheets/d/1EjRIeDPrG_qp1S9QhInl9r3G0cZx_16OvnTXORgA3n4/edit?usp=sharing

there is two sheets one with the old version of my layout called "OLD" and my new desired layout called "NEW"

thanks in advance to anyone able to help

Cooper Cooper · Accepted Answer · 2019-11-24T17:54:15

This function hides a column whose top row date is less yesterday.

   function hideColumnWhenTopRowDateIsBeforeYesterday() {
      var ss=SpreadsheetApp.getActiveSpreadsheet();
      var sh=ss.getSheetByName("Sheet1");
      var rg=sh.getRange(1,1,1,sh.getLastColumn());
      var vA=rg.getValues()[0];
      var yesterday=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1).valueOf();
      vA.forEach(function(e,i){
        if(new Date(e).valueOf()<yesterday) {
          sh.hideColumns(i+1);
        }
      });       
    }

My Spreadsheet before hiding columns:

My Spreadsheet after hiding columns:

how do I change this google app script to hide sheets columns instead of rows based on column dates

1 Answers