Google sheets Script to Hide Rows in Batch

Question

I'm currently using this script to hide rows containing 0 on col K

function Hide() {

    var s = SpreadsheetApp.getActive()
         .getSheetByName('Sheet1');
        s.getRange('K:K')
        .getValues()
        .forEach(function (r, i) {
            if (r[0] !== '' && r[0].toString()
                .charAt(0) == 0) s.hideRows(i + 1)
        });   
}

Which works perfect, the only thing is that here when I run the script, it hides row by row (now that I have a lot of rows it takes so much time).

Is there a way to change it to work in batch?

Possible duplicate of [How to make my 'Show and hide rows' script function properly in Google Sheets](https://stackoverflow.com/questions/54372743/how-to-make-my-show-and-hide-rows-script-function-properly-in-google-sheets) — Rubén, Feb 01 '19 at 01:35

score 3 · Answer 1 · edited Oct 11 '17 at 14:06

3

This is the script that makes the magic

  function Hide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Ventas");
  var currentRange = ss.getRangeByName("RangeCalculation");
  var rangeStart = currentRange.getRow();
  var values = currentRange.getValues();

  var index = 0, rows = 1;
  var show = !(values[0][12] == "" );

  for (var i = 1, length = values.length; i < length; i++) {
    if (values[i][0] == 1 ) {
      if (show) {
        sheet.showRows(rangeStart + index, rows);
        show = false;
        index = i;
        rows = 1;
      } else
        rows++;
    } else {
      if (show)
        rows++;
      else {
        sheet.hideRows(rangeStart + index, rows);
        show = true;
        index = i;
        rows = 1;
      }
    }
  }

  if (show)
    sheet.showRows(rangeStart + index, rows);
  else
    sheet.hideRows(rangeStart + index, rows);
}

edited Oct 11 '17 at 14:06

Rubén

34,714
9
70
166

answered Oct 10 '17 at 23:59

SATH59

149
1
3
15

It's very good that you shared your solution. It could be better if you include a brief description of your learnings instead of just pasting the code. By the way, you include my suggestion, instead of hideRows(rowIndex) your code use hideRows(rowIndex, numRows) :) – Rubén Oct 11 '17 at 14:16
@Rubén I tried you method nad it runs as slow as the first sample ... Can you please explain few things about yours? – AlexShevyakov Jan 31 '19 at 20:50
@AlezShevyakov What things do you need to be explained? (Please reply by posting a comment on my answer) – Rubén Jan 31 '19 at 21:10

Rubén · Answer 2 · 2017-10-11T14:06:15.240

2

Instead of hideRows(rowIndex), use hideRows(rowIndex, numRows)

The first form use only one parameter rowIndex, the second use two parameters, rowIndex and numbRows.

Obviously, using the suggested method implies to review the logic of your script.

edited Oct 11 '17 at 14:06

answered Oct 10 '17 at 03:18

Rubén

34,714
9
70
166

Google sheets Script to Hide Rows in Batch

2 Answers2

Linked