Google Sheets - Hide Column(s) based on Row 1 value

0
votes

I have a bunch of content on a google sheet organised by columns. I want a script that will check Row 1 from Cell 4 to 100 to see if the letter X appears. If the letter X appears then the script should hide that Column.

And then also the reverse of that being unhide all columns regardless of Row 1 content. I have found this snippet of code(s) but they don't seem to really do anything, there is no failure displayed or error, just doesn't hide the column(s)

function hideColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // itterate through columns
  for(var i=1; i<lastCol; i++) {
     if(data.getCell(1, i).getValue() == 'x') {
        sheet.hideColumns(i);
     }
  }
}

function showColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();
  
}

I have managed to code the same actions in Excel directly and they work exactly as I want them as per codes below, however, google doesn't like the command button or code when I upload the .XLSM file to Google Drive and run as a Sheet.... Any ideas? Excel Code below that works well

Private Sub CommandButton1_Click()
For i = 4 To 30
If Worksheets("Current Orders").Cells(1, i).Value = "x" Then
Worksheets("Current Orders").Columns(i).Hidden = True
End If
Next
End Sub

Private Sub CommandButton2_Click()
For i = 4 To 30
Worksheets("Current Orders").Columns(i).Hidden = False
Next
End Sub
1
google-apps-scriptgoogle-sheets

1 Answers

3
votes

Answer:

You can do this with a TextFinder.

Code Example:

function hideColumnsWithx() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var cells = sheet.createTextFinder("x").matchEntireCell(true).findAll();
  
  cells.map(x => sheet.hideColumns(x.getColumn()));  
}

Rundown of this function:

  • Get the first sheet from the Active Spreadsheet
  • Find all cell ranges which contain exactly the string "x"
  • Hide every column that is returned from the TextFinder using array.map()

References: