google-sheetsgoogle-apps-script

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

Solution

  • 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.