Search code examples
sortinggoogle-sheetshideshow-hide

How to show columns based on today's date, and hide the rest?

I am new to Google sheets and coding stuffs, hoping to get some help, as I have been trying to fix this for couple of days. I want my current sheet to show only Today column, and hide the rest of the columns when I click the checkbox at B1, how do I go about it? Say today is 12th Jul, so it will show Column A,B,L,M,N; and hide the rest of the column. Thank you in advance.

enter image description here

Link here

I tried to google for similar result, edited the script over and over again but didnt work. I know the problem lies in me lol.

Solution

  • In your situation, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.

    When you use this script, please check the checkbox of "B1" of "Attendance" sheet. By this, the script is automatically run by the simple trigger of onEdit. And, please don't directly run the script of onEdit. Because in that case, the event object e is not given. So, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.

    function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (range.getA1Notation() != "B1" || sheet.getSheetName() != "Attendance") return;
  if (!range.isChecked()) {
    sheet.showColumns(3, sheet.getMaxColumns() - 2);
    return;
  }
  const now = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "d-MMM");
  sheet.hideColumns(3, sheet.getMaxColumns() - 2);
  sheet.getRange(3, 3, 1, sheet.getLastColumn() - 2).getDisplayValues()[0]
    .forEach((e, i) => {
      if (e == now) {
        sheet.showColumns(i + 3, 3);
      }
    });
}

    Testing:

    When this script is run to your provided Spreadsheet, the following result is obtained. When I checked the checkbox of "B1" today (2023-07-12), the following result is obtained.

    enter image description here

    Note:

    • This script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

    References: