1

I have the following function that works to search for a column line by line and hides a row when it finds x. It works but is slow.

function SummaryViewGenerate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  for( i=1 ; i<=lastRow ; i++) { // i <= lastRow
       var status = sheet.getRange("K"+i).getValue();
       if (status == "x") { // status == "x"
         sheet.hideRows(i);
       } 
   }
}

The problem is that it is super slow for my use. Any idea on how I can improve it. Someone mentioned on another thread about putting it into an array. Im still a coding newbie so any help in the right direction would be useful.

Rubén
  • 34,714
  • 9
  • 70
  • 166
penfold255
  • 43
  • 5
  • Possible duplicate of [Is it possible to hide many rows of a Google spreadsheet at once?](http://stackoverflow.com/questions/36607585/is-it-possible-to-hide-many-rows-of-a-google-spreadsheet-at-once) – Rubén Nov 15 '16 at 01:19
  • Related: http://stackoverflow.com/questions/20131304/optimize-google-script-for-hiding-columns – Rubén Nov 15 '16 at 01:20

1 Answers1

1

I think that it will be faster by getValues(). Reference is https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()

  1. Retrieve all data from spreadsheet using getValues. Data is put in 2D array.
  2. Search "x" from the 2D array.

Sample is as follows. 

    function SummaryViewGenerate(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var array = sheet.getRange('k1').offset(0, 0, sheet.getLastRow(), 1).getValues();
      for (var row in array) {
        for (var col in array[row]) {
          if(array[row][col] == "x") {
          sheet.hideRows(row + 1); // (row + 1) is row number.
          }
        }
      }
    }
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks, so for the array we would create, we would search for x via a for loop? We would also need to find out the row number for the position in the array as well? – penfold255 Nov 14 '16 at 22:55
  • Yes. For example, when array is got by getValues, it can be searched as follows, because array is 2D. The row number can also be obtained simultaneously. for (var row in array) { for (var col in array[row]) { if(array[row][col] = "x") { sheet.hideRows(row + 1); // (row + 1) is row number. } } } – Tanaike Nov 14 '16 at 23:06
  • What about the following, i know it doesnt work but is it on the right track, any help or ideas? function SummaryViewGeneratev1() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var column = ss.getRange('K:K'); var values = column.getValues(); for (var row = 0; row < values.length; row++) { for (var col = 0; col < values[row].length; col++) { if(array[row][col] = "x") { sheet.hideRows(row + 1); // (row + 1) is row number. } } } } – penfold255 Nov 14 '16 at 23:23
  • i just saw your answer . i want to search all of column K, so i will put K:K. When i ran the script it says the rows are out of bounds? – penfold255 Nov 14 '16 at 23:33
  • Please change if(array[row][col] = "x") to if(values[row][col] == "x") on your latest code. – Tanaike Nov 14 '16 at 23:35
  • .getRange('K:K') is corresponding to column K. The values are included data in column K. And it is 2D array. – Tanaike Nov 14 '16 at 23:38
  • fantastic, much appreciated. – penfold255 Nov 14 '16 at 23:42
  • Also your version says out of bounds but your comments worked on my other code version. Feel free to correct your version to make it correct so others can use it. – penfold255 Nov 14 '16 at 23:53