CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
NoobCoder
NoobCoder

Reputation: 137

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

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.

enter image description here

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)
  }
}

Upvotes: 1

Views: 654

Answers (1)

Marios
Marios

Reputation: 27348

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

Upvotes: 2

Related Questions