Google Script for a spreadsheet is very slow

Question

I'm no pro but I need this Google Script for my spreadsheet. I was searching for puzzle pieces to build my code. At the end it works but very slow. Can someone help me? I can see in slowmotion how the code works. :-(

My spreadsheet or sheet has a lot of entries but some rows are empty (over 1000 rows). With the filter function I can hide these rows very fast, just in seconds. I'm not the user of the spreadsheet only the creator. The normal users are really no experts and using the filter function is a challange for them. I thought I can combine all steps in a script and make it usable with a button. One part is hiding the empty rows.

The script checks in column N whether there is 1 or 0. If there is a 0, then the row shall be hidden. The script starts in row 7 till 1150. Hiding one row needs one second each! This is really slowmotion. Is it possible to activate the filter function by script alternatively if my code is rubbish?

function hideRows() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Order"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = 14;
  var cell = 0;
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();

  for (var row = 7; row < lastRow; row++) {
    cell = sheet.getRange(row, column).getValues();
    if (cell == 0){
      sheet.hideRows(row);
    }
    else {
      sheet.showRows(row);
    }
  } 
}

Answer 1

Try this:

function hideRows() 
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1').activate();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  for (var i=6;i<vA.length-1;i++)//vA.length-1 stops before last row i=6 is row 7
  {
    if (vA[i][13] == 0)
    {
      sh.hideRows(i+1);
    }
    else 
    {
      sh.showRows(i+1);
    }
  } 
}

The array indexes are one less than the row and column numbers so you have to keep that straight but you get all of the data at one time and then the loop runs much faster because your reading the array rather than having to access the spreadsheet for each value.

Answer 2

The getValues() method returns a 2D Array, so you should use getValue() instead, which returns the contents of the cell you target. I rewrote the snippet you posted to have a bit better syntax/formatting, as incrementing through 2D Arrays is much quicker than incrementing through cells/ranges:

function hideRows() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Order"); //get sheet
  var nRows = sheet.getLastRow() - 7; //# of rows to search

  var values = sheet.getRange(7, 14, nRows, 1).getValues(); //Get 2D array of values [[],[],...]
  //increment through the full index range of values (0-7)
  for (var i in values) {
    if (values[i][0] == 0) sheet.hideRows(i+7); //i+7 is the rowIndex (7-14)
    else sheet.showRows(i+7);
  }
}

That should accomplish what you're trying to do. Let me know if you have any problems or questions.