3

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

  • I had initially appended the new question on to this one, but they are considerably different situations. Please ask a new question instead. – jonsca Feb 18 '17 at 0:29
4

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.

  • Obviously only the owner triggers this script. I would like that shared users with write permission could also trigger – Juergen Kimmel Feb 19 '17 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.