Hide rows in google spreadsheet if Column A is empty?

6
votes

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

EDIT: Bonus question, is there a way I can get it to unhide when Column A is filled? I'm using a =query formula to populate my spreadsheet and the length will need to change as more data is added.

3
google-apps-scriptgoogle-sheetsgoogle-docs
if an answer below worked for you, you might consider marking it answered so the rest of us will know. - ScampMichael
your bonus question leads me to believe that all the rows you are hiding are below the last occupied row. is that correct? are you hiding rows that are interspersed within the data or only at the very end after the last of the data? - ScampMichael
I'm hiding rows that are interspersed within the data. My employer wanted me to create a spreadsheet where he could see both the income and the expenses on a week-by-week basis but I have no way of guessing how long the Income section could be so I added additional blank rows to compensate. Using JPV's formula I was able to hide the blank rows but it becomes a pain when I can't unhide those very same rows (with the same efficiency) once something is added to them (I'm using a importrange => query to generate data). - Darksun
so if my understanding is correct, you are hiding to contiguous sets of rows. 1 set between the income and expenses and the other set after the expenses. is that correct? - ScampMichael
Yes. It's ordered like: Income --> Expenses - Darksun

3 Answers

11
votes

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.

4
votes

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.

1
votes

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.