CODEWITHSUNDEEP
google-sheets
user2622970
user2622970

Reputation: 11

I want to hide all rows in a google drive spreadsheet that do not have any value in the cells in Column A

I want to hide all rows in a google drive spreadsheet that do not have any value in the cells in Column A. Then be able to view them again when needed. What is the script/formula for this function? Thanks

Upvotes: 0

Views: 10490

Answers (2)

Fong Kah Chun
Fong Kah Chun

Reputation: 1

I've modified Blexy's menu to allow hiding of empty rows as a menu item rather than doing so during onOpen.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; //Sheet1 is in Array[0]
var range = sheet.getRange(1, 1, sheet.getLastRow());


function onOpen() {
    //add menu called Visibility onOpen
    ss.addMenu("Visibility", 
               [{
                 name: "Show All Rows", functionName: "showAllRows"
                 },
                {
                 name: "Hide All Empty Rows", functionName: "hideAllEmptyRows"
                 }
               ]
              );
}

function showAllRows(){
    sheet.showRows(1,sheet.getLastRow());
}

function hideAllEmptyRows(){
  //get the values to those rows
    var values = range.getValues();

  //go through every row
  for (var i=0; i<values.length; i++){
        //if row value is equal to empty  
        if(values[i][0] === ""){

        //hide that row
        sheet.hideRows(i+1);   
        }   
    }

}

Upvotes: 0

Nick Blexrud
Nick Blexrud

Reputation: 9603

UPDATED to include a menu to Show all Rows:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, sheet.getLastRow());


function onOpen() {
    //add menu called Visibility onOpen
    ss.addMenu("Visibility", [{
        name: "Show All Rows", functionName: "showAllRows"
    }]);

    //get the values to those rows
    var values = range.getValues();

    //go through every row
    for (var i=0; i<values.length; i++){

        //if row value is equal to empty  
        if(values[i][0] === ""){

        //hide that row
        sheet.hideRows(i+1);   
        }   
    }
}

function showAllRows(){
    sheet.showRows(1,sheet.getLastRow());
}

Upvotes: 3

Related Questions