Hiding columns based on date

Question

Pretty new to scripts.

I have a sheet with name information in columns A-C and dates from column D on.

I found a script that will hide all columns to the left of the column with the current date.

I would like to modify it to hide all columns to the right of column C, except for the column with the current date.

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);
  }

Answer 1

You can hide columns that do not meet your criteria with an onOpen(e) simple trigger.

To easily compare a date in row 1 to today's date, you can get the dates as text strings with Range.getDisplayValue() and today's date as a text string with Utilities.formatDate(), like this:

const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
const todayString = Utilities.formatDate(new Date(), timezone, 'M/d/yyyy');

See the autoHideColumns_ script for sample code. You may be able to use it out of the box with these parameters:

    ////////////////////////////////
    // [START modifiable parameters]
    {
      sheetRegex: /^(Sheet1)$/i,
      _hideCondition: (value) => value !== todayString,
      rowToWatch: 1,
      columnsToWatch: [4, Infinity],
    },
    // [END modifiable parameters]
    ////////////////////////////////

The onEdit(e) simple trigger runs under account of the user at the keyboard. In the event the spreadsheet contains protected ranges or sheets, the script may not be able to hide columns that intersect a protection. The workaround is to run the script under the account of the spreadsheet owner.

To always run the script under the account of the spreadsheet owner, remove the onEdit(e) function, rename autoHideColumnsOnEdit_(e) to autoHideColumnsOnEdit(e) and run it through an installable "on edit" trigger.