Google Script to autohide empty cells, should refresh?

Question

So I'm looking to auto-hide completely empty rows. With the code, I have now it is autohiding everything with an empty A column when I would still like the columns and rows with N/A shown, reference screenshot#1. I would also like for it to update onOpen or onChange and "unhide" previously empty rows that now have values. By this, I mean this spreadsheet is based on a query & importrange from another spreadsheet that only pulls over certain columns. So the other sheet is the sheet that gets edited, and this one updates with importrange. Hence, if something is added to a previously empty row (for the purpose of this spreadsheet, an assignment to an individual), I would like it to unhide the rows that now contain data as well.

Additionally, this only seems to work when I'm logged in with the ability to "edit" not when view-only and accessible by the public.

Screenshot#1 (All Rows showing)

Screenshot#2 ("Blank" rows hiding)

Screenshot#3 (View-only mode does not update with script)

This is to clean up formatting by hiding empty rows. I've gotten the progress I have by searching solutions, and have come to a dead end.

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

function onOpen() {
    var menuEntries = [];
  menuEntries.push({name: "Show All Rows", functionName: "showAllRows"});
  menuEntries.push(null);
  menuEntries.push({name: "Hide Empty Rows", functionName: "hideEmptyRows"});

  ss.addMenu("Row Visibility", menuEntries);
}{

    //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());
}

function hideEmptyRows(){
        //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);
        }
    }
}

Answer 1

Several points:

I would still like the columns and rows with N/A shown

Your current code

if(values[i][0] === ""){

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

hides the whole row if the cell in the first column is empty. To hide the row only if ALL cells are empty, you need to iterate through all of them:

function hideEmptyRows(){
    //go through every row
    for (var i=0; i<values.length; i++){
        var empty=true;
        //go through every column
        for (var j=0; j<values[0].length; j++) {
         if(values[i][j] != ""){
           empty=false;
           break;
           }
          }
        if(empty == true){
        //hide that row
        sheet.hideRows(i+1);   
        }   
    }
}

this only seems to work when I'm logged in with the ability to "edit" not when view-only

As you can verify consulting Restrictions section of the documentation, unfortunately both simple and installable triggers do not run if a file is opened in read-only (view or comment) mode.

I would also like for it to update onOpen or onChange and "unhide" previously empty rows that now have values.

Unfortunately, changes made to the sheet programmatically (with a query) will not fire: Script executions and API requests do not cause triggers to run.

However, you can still first unhide all rows onOpen() and then hide the empty ones. For this, you just need to embed your functions showAllRows() and hideEmptyRows() into onOpen():

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
var values = range.getValues();
function onOpen() {
  showAllRows();
  hideEmptyRows();
  var menuEntries = [];
  menuEntries.push({name: "Show All Rows", functionName: "showAllRows"});
  menuEntries.push(null);
  menuEntries.push({name: "Hide Empty Rows", functionName: "hideEmptyRows"});
  ss.addMenu("Row Visibility", menuEntries);
}

Additionally:

If you want your rows to update periodically, even if the sheet has not been closed and reopened, you can install a time-driven trigger that will periodically update the hidden rows. For this, you need to bind it to your already existing onOpen function and select a time interval of your choice:

Answer 2

Purely for the sake of providing an alternative (and proving that there is nothing new under the sun). This is the take from @Mogsdad from 2014.

---

/**
 * Mogsdad's "whole row" checker.
 //https://stackoverflow.com/a/27179633/1330560
 */
function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 0;
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  // return (row+1);
  Logger.log("the row to hide is = "+(row+1));
}