Google Sheets – How to Hide Columns with Certain Dates on Open

google sheets

Got a sheet containing dates in the first row.

I would like to hide on open all columns (some are already hidden manually) left of the column which date is closest to the current date

Best Answer

This requires a script with a function onOpen(). Since you are asking about dates and not times, I'm going to break my own advice "do not use .getValue on date/time cells".

Here is a script that does what you described: grabs the dates in the first row, compares them to the present time (ignoring the potential timezone issues: UTC in Google Script vs local time in Sheets), and picks the closest. Then hides the columns to the left of the closest. 

function onOpen(e) {
  var sheet = e.source.getSheetByName('Sheet1'); // 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(1, imin);
}

Whether some of the columns were previously hidden doesn't matter.

You may want to play it safer by hiding one column less, with sheet.hideColumns(1, imin-1); to provide a cushion against timezone discrepancy. Also, nearest date could be before or after today's; this is also worth considering.

Related Topic