0

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);
  }
2
  • Welcome to Web Applications. Are the dates is ascending order? Are you looking to hide the future dates rather than the past dates? What you have tried to adapt the script to your needs?
    – Rubén
    Jun 3 at 15:17
  • The dates are in ascending order. I wish to hide all past dates listed as well as all future date. I only want to see todays date column. I'm not familiar enough with scripts for sheets to have modified this. I was able to modify a different script for Excel to get it to work, but this is a shared doc so I need to use sheets.
    – JDC
    Jun 3 at 18:00

1 Answer 1

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.

3
  • Thank you for this help. I was able to get what I wanted here, but the script appears to only work for the owner of the doc. Not for anyone else with editor rights. The script runs and the pop up message says that "X" number of columns were hidden, but none of the columns actually get hidden. Is there something in the script that would cause this? I didn't see anything. Is there some setting within the sheet that sets this?
    – JDC
    Jun 21 at 13:49
  • That suggests that you have protected sheets or ranges in the spreadsheet. Check My Executions for failed executions, and view those logs to learn more about why the function fails. Jun 21 at 13:52
  • Edited the answer to include instructions on how to use an installable trigger. Jun 21 at 14:50

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.