CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
Darksun
Darksun

Reputation: 63

Hide rows in google spreadsheet if Column A is empty?

I'm trying to hide rows if Column A is empty. I want to apply this to specific sheets (Week1, Week2, Week3, Week4 and Week5). This is the code I have so far for Week1.

 function ConditionalHideRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Week1");
  var condition = sheet.getRange("A:A").getValue();
  if (condition = "EMPTY") {
    sheet.hideRows(1,125)
   }

Upvotes: 6

Views: 27624

Answers (3)

Jabari
Jabari

Reputation: 5509

You don't need a formula for this, you can use a filter...

  1. Highlight the column in question
  2. Select *Data > Filter
  3. There should be a drop down button in in the header row now. Click it and select/un-select your conditions

UPDATE: This should also work for your bonus question as well. Once the column is populated, it will no longer meet the filter conditions if "(Blanks)" is unchecked.

Upvotes: 11

ScampMichael
ScampMichael

Reputation: 3728

Answer to Edited: The only approach for unhiding occupied rows that makes sense to me at this point would be to unhide all rows for the sheet before hiding the blank rows.

Add the line below where it says // Add this

function hideRows() {
["Week1", "Week2", "Week3", "Week4", "Week5"].forEach(function (s) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(s)
    sheet.unhide(sheet.getRange('A:A'))  // Add this
    sheet.getRange('A:A')
        .getValues()
        .forEach(function (r, i) {
            if (!r[0]) sheet.hideRows(i + 1)
        });
    });
}

Or to unhide all independently:

function unhideRows() {
["Week1", "Week2", "Week3", "Week4", "Week5"].forEach(function (s) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(s)
    sheet.unhide(sheet.getRange('A:A'))
    });
}

For future reference, there are more efficient ways of doing things when you can handle contiguous rows as a block instead of one at a time. Even though this works I believe JPV might have done it a bit differently had he understood the layout.

Upvotes: 1

JPV
JPV

Reputation: 27242

I assume 'EMPTY' is not really the string that is to be found in col A and you do want to check if col A is truely empty ? If so, try:

function hideRows() {
["Week1", "Week2", "Week3", "Week4", "Week5"].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)
        });
    });
}

Or, for a more 'classical' approach:

function hideRows2() {
var sheets = ["Week1", "Week2", "Week3", "Week4", "Week5"];
for (var i = 0, sLen = sheets.length; i < sLen; i++) {
    var sheet = SpreadsheetApp.getActive()
        .getSheetByName(sheets[i])
    var val = sheet.getRange('A:A')
        .getValues();
    for (var j = 0, vLen = val.length; j < vLen; j++) {
        if (!val[j][0]) sheet.hideRows(j + 1)
        }
    }
}

Make sure to you don't have too many blank rows (after the last row with data) as that may lead to an execution time-out of the script.

Upvotes: 4

Related Questions