I currently have this script almost working. The issue is that when I open the sheet it hides all previous date along with today's date. I need it to keep today's date (or even today's and yesterdays), but can't figure out what needs changed to do so. I have columns where the top row is just a date. I want to hide the column when the date has passed. It's ok if it shows todays date along with yesterdays. But currently it hides the column with todays date which doesn't do what I need it to do.

function onOpen(e) {
  var sheet = e.source.getSheetByName('Name of sheet'); // or .getSheets()[0]; to apply to the 
first sheet.
  var width = sheet.getDataRange().getWidth();
  var dates = sheet.getRange(1, 1, 1, width).getValues().valueOf();  
  var today = Date.now();
  var minDiff = 1e9;
  var imin = 0;
  for (var i = 0; i < dates[0].length; i  ) {
    if (Math.abs(dates[0][i]-today) < minDiff) {
      imin = i;
      minDiff = Math.abs(dates[0][i]-today);
    }
  }
  sheet.hideColumns(2, imin);
}

CodePudding user response：

Some points to keep in mind

• You retrieve a range starting in the first column, but hide columns starting with the second column

• You do not use a break statement to exit the loop once you find the first date lying far enough in the future (the more in the future lying dates will also fullfill the criterium if (Math.abs(dates[0][i]-today) < minDiff))

• Also, minDiff = 1e9; does not correpond to the difference between two days in ms.

This works for me:

function onOpen(e) {
//  var sheet = e.source.getSheetByName('Name of sheet'); // or .getSheets()[0]; to apply to the first sheet.
sheet = SpreadsheetApp.getActiveSheet();
  var width = sheet.getDataRange().getWidth();
  var dates = sheet.getRange(1, 1, 1, width).getValues().valueOf();  
  var today = Date.now();
  var minDiff = 1000 * 60 *60 *24 //1e9;
  var imin = 0;
  for (var i = 0; i < dates[0].length; i  ) {
    if (Math.abs(dates[0][i]-today) < minDiff) {
      imin = i;
      break;
    }
  }
  if(imin >= 1){
  sheet.hideColumns(1, imin);
  }
}