I need help creating a script to hide columns in google sheets if cells in range are empty

Question

I am trying to create a script which will hide columns where cells in a range are empty (the condition).

Below is what my table looks like, you can see there are 12 roles. I basically want to hide any columns from Role 4 onwards which are unused e.g. no values in the 3 rows.

i have a script that works (see below) but it also hides Roles 1, 2 and 3 if they are blank. I want the script to only work from Role 4 (column F) onwards. What do i need to change in the script for this to work?

P.s. I have used code from other posts to put this together so there may be lines in the code I do not need, I am still very new to google script.

function hidecolumns() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Project Team Resources");
  var r = ss.getRange("C5:N10");
  var data = r.getValues();
  var rData, cData, x;
  cData = data[0].map(function (col, c) {
    return data.map(function (row, r) {
        return data[r][c];
      });
  });
  Logger.log(cData.length);
  Logger.log(data.length);
  for(var i=0;i<cData.length;i++){
    if(cData[i].filter(String).length==0)
      ss.hideColumns(i+3)
  }
}

soMario soMario · Accepted Answer · 2020-12-03T17:09:51

Explanation:

Your goal is to hide a column if it is empty with the exception of the first three columns in the range.

One approach to achieve this to iterate over all columns in the desired range and check if they contain at least one non-empty value (length>0). If the length is 0, meaning that all values in this column are empty, then hide it.
In more detail, I use a forEach loop to iterate over each column. Each column is given by a simple map expression:

data.map(d => d[col]) where col takes values 0, 1, 2, ...
I filter on the non-empty values and hide only columns if the length of the non-empty array is 0.
Since the data range starts from column C which is the 3rd column, I hide column col+3. Be careful with this, since it depends on the input range.
Finally, your goal is to keep the first 3 columns (Roles 1,2,3) fixed-unhidden regardless if they contain values or not. To do that, add an extra condition in the if condition to check if column is larger than 2: col>2.

Solution:

function hidecolumns() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Project Team Resources");
  var data = ss.getRange("C5:N10").getValues();
  data[0].forEach((_,col)=>{                 
     if(data.map(d => d[col]).filter(e=>e!='').length==0 && col>2){
       ss.hideColumns(col+3);
     }
  });
}

underscore as a parameter reference

I need help creating a script to hide columns in google sheets if cells in range are empty

1 Answers

Explanation:

Solution: