CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Quincey Frewin
Quincey Frewin

Reputation: 97

How to hide a column if all cells in a range are blank?

Simply put, I am writing a script that will check every other column for the number of values it contains, specifically the number of values between rows 3 and 53 in the column. If all cells in the search are blank, I want to hide that column and the column to its immediate left. Similarly, if the search finds a hidden column that has one or more values, I want to unhide that column as well as the column before it.

I have been doing some research on how I could go about doing so. I am using a for loop to get the integer values for every other column. I then use those values to set up my range and use .getValues and .length to count the number of values in the range. Now, I have never created an if/else statement from scratch, but my idea was to compare the output from .length against the number 0, and if length equals 0, hide the columns, but if anything else, unhide the column.

This is the script I have written.

function Hide()  {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Coach");
  var maxRow = sheet.getMaxRows();
  var maxCol = sheet.getMaxColumns();
  var startCol = 5;
  var startRow = 3;
  var endRow = 53;
  for (col = startCol; col <= maxCol-4; col = +2)  {
    var range = sheet.getRange(startRow,col,endRow-startRow+1,1);
    var l = range.getValues().length;
    if (l == 0)  {
      sheet.hideColumns(col);
      sheet.hideColumns(col-1);
    }
    else  {
      sheet.showColumns(col);
      sheet.showColumns(col-1);
    }
  }
}

I have three problems with the script:

As to the first problem, getValues appears to list all cell values in the range, including blank cells. Therefore, .length is effectively counting all cells in the range instead of just blank cells. Also, every cell in the search criteria is populated by a formula, so that could be part of the first problem as well. I am not sure as to how I could properly count the number of non blank values though.

I believe the other two problems have to do with how I set up my if/else statement. I have never created one from scratch before, but I have read that if not created properly, if statements can majorly slow a script's runtime.

I would greatly appreciate any help anyone can give me. Thank you!

Upvotes: 1

Views: 197

Answers (1)

Tanaike
Tanaike

Reputation: 201338

Modification points:

  • In your script, when for (col = startCol; col <= maxCol-4; col = +2) { is run, col is not changed by col = +2. By this, the loop is not finished.
    • I think that this might be the reason of your issue of The script only affects the first range in my search criteria. and The script will run, but never end..
  • And, in the case of var l = range.getValues().length;, l is always not 0. Because range.getValues() is 2 dimensional array.
    • I think that this might be the reason of your issue of The script will only unhide columns..

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
for (col = startCol; col <= maxCol-4; col = +2)  {
  var range = sheet.getRange(startRow,col,endRow-startRow+1,1);
  var l = range.getValues().length;
  if (l == 0)  {
    sheet.hideColumns(col);
    sheet.hideColumns(col-1);
  }
  else  {
    sheet.showColumns(col);
    sheet.showColumns(col-1);
  }
}
To: 
for (var col = startCol; col <= maxCol-4; col += 2) {  // Modified
  var range = sheet.getRange(startRow,col,endRow-startRow+1,1);
  var l = range.getValues().filter(([v]) => v.toString() != "").length;  // Modified
  if (l == 0)  {
    sheet.hideColumns(col);
    sheet.hideColumns(col-1);
  }
  else  {
    sheet.showColumns(col);
    sheet.showColumns(col-1);
  }
}

Note:

  • I think that the modified script can be run without error. But I'm not sure about the result of the script is what you want to do. So if that is not the result you expect, please tell me. I would like to modify it. At that time, can you provide the sample input and output situation you expect? By this, I would like to confirm it.

Upvotes: 2

Related Questions