Google Script - Hide column if a cell within the column contains a specific value

Question

I am trying to hide all columns in Google Sheets if a cell within that column contains the letter "S", I am checking row 6 which has the initials of each day of the week and want the ability to show and hide the weekends Columns A6:G6 have M,T,W,T,F,S,S

Reason for the 9999 is due to this sheet containing multiple weeks and I am trying to look through all of them.

function Hide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange();
  var lastCol = data.getLastColumn()+1;

  for(var i = 1; i < lastCol - 9999; i++) {
    if(data.getCell(6, 9999 + i).getValue() === "S") {
      sheet.hideColumns(9999 + i);
    }
  }
};

This is what I have so far but it doesn't seem to do anything, can anyone help me understand what I am missing please as I have been looking around and cannot find anything that has helped me with the issue?

Thank you!!

Answer 1

Try this. Google Apps scripts is basically Javascript. This is how I would loop through the columns since the used area of the sheet is dynamically changed, then it will stop at the last used column.

function Hide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  // get the entire 6th Row
  var range = sheet.getRange("6:6");
  // get number of columns to loop through
  var num_cols = range.getNumColumns();
  // loop through columns and check value one by one
  // if value is equal to "S", then hide the column
  for (var i = 1; i <= num_cols; i++) {
    var value = sheet.getRange(6,i).getValue();
    if (value == "S") {
      sheet.hideColumns(i);
      };
    }
  }