CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user2748807
user2748807

Reputation: 109

Hide Rows and Columns That Are Empty

On Open, In Worksheet "Time per Job & Job Costing", if Row from column A is empty or includes a 0 value hide it. If Column from row 1 is empty or includes a 0 value hide it.

I looked around and can find techniques for hiding rows if a column is empty but not the other way around, nor if the value is 0.

I have the Rows accomplished with this, but it only works on empty rows:

function onOpen(e) {

["Time per Job & Job Costing"].forEach(function (s) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(s)
    sheet.getRange('A:A')
        .getValues()
        .forEach(function (r, i) {
            if (!r[0]) sheet.hideRows(i + 1)
        });
    });
  

}

Upvotes: 1

Views: 286

Answers (1)

Marios
Marios

Reputation: 27348

If Column from row 1 is empty or includes a 0 value hide it.

Explanation:

  • I believe your goal is to hide a column if a cell of that column in the first row is empty or 0. You also want to achieve that when you open the spreadsheet file.
  • To achieve that, the first step is to get the values of the first row. You can use getRange() to get the first row starting from the first column until the last column with content. For the latter you need to use getLastColumn(). Then you can flatten the array in order to perform a forEach loop. For every element in the first row, you check whether it is empty or 0. If this condition evaluates to true then you hide that particular column.

Solution:

function onOpen() {
  
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Time per Job & Job Costing');
  const first_row = sh.getRange(1,1,1,sh.getMaxColumns()).getValues().flat();
  
  first_row.forEach((fr,i)=>{
  if(fr=='' || fr==0){
  sh.hideColumns(i+1);
  }});
  
}

Upvotes: 1

Related Questions