Hiding multiple rows in google sheet based on cell value - perfromance issue

Question

I have a simple dataset within a google sheet, taking up several colums (A:N) and about 100 rows down. Each row represents a task and a task may either be in OPEN or CLOSED status. The status value is in column M.

By default all tasks have status "OPEN", however, when a user closes a task and selects status "CLOSED", I want to hide that task's row. Although the code below works as desired (intial version 1), it feels laggish and slow.

Looking for a solution, I have shortened the code to version 2, however with the same results and thus I think there must be something fundamental about the way this script works.

I have also checked this (Google sheets Script to Hide Rows in Batch.) solution, but in my case it did not work at all, i.e. the speed did not really change that much. Working with their example, I produced version 3 of the code.

I would appreciate any improvements to this code please.

Thank you.

PS. In the code below (version 1), I am testing second column (B), hence getRange(1,2,lastRow,1)

// Version 1
function hideClosed_ver1() {

  var sheet = SpreadsheetApp.getActive().getSheetByName("Hide"); 
  var lastRow =  sheet.getLastRow();
  var arr = sheet.getRange(1,2,lastRow,1).getValues();

  for(var i = 0; i < arr.length; i++)
  {
    if(arr[i][0]==='Closed')
    {
      sheet.hideRows(i + 1);
    }
  }
}

// Version 2

function hideClosed_ver2() {

  var app = SpreadsheetApp.getActive().getSheetByName('Hide');
  app.getRange('B:B').getValues().forEach(function (r, i) {
    if (r[0] == "Closed")
      app.hideRows(i + 1)
      });   
}

// Version 3

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

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

  for (var i = 1, length = values.length; i < length; i++) {
    if (values[i][0] == "Closed" ) {
      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);
}

@tehhowch. Originally I was planning to run the script for th banch of rows, but now think it would be better to hide individual rows each time status "CLOSED" is selected. — AlexShevyakov, Feb 02 '19 at 09:21

score 2 · Accepted Answer · answered Feb 01 '19 at 23:21

You want to hide rows, when there are the values of "Closed" in the column "B".
You want to run above with the low cost.

If my understanding is correct, how about using Sheets API? When the method of batchUpdate of Sheets API is used, several rows can be hidden by one API call.

When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Modified script:

Please copy and paste the following script and run.

function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Added
  var sheet = ss.getSheetByName("Hide"); // Modified

  var lastRow =  sheet.getLastRow();
  var rng = sheet.getRange(1,2,lastRow,1);
  var arr = rng.getValues();

  // Following script was added.
  var sheetId = sheet.getSheetId();
  var reqs = arr.reduce(function(ar, e, i) {
    if (e[0] === 'Closed') {
      var req = {"updateDimensionProperties": {
        "properties": {"hiddenByUser": true},
        "range": {"dimension": "ROWS", "startIndex": i, "endIndex": i + 1, "sheetId": sheetId},
        "fields": "hiddenByUser",
      }};
      ar.push(req);
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({"requests": reqs}, ss.getId());
}

References:

If this is not the result you want, please tell me. I would like to modify it.

Thank you for the hint. unfortunately could not initiate API ... However, I think I should run onEdit(e) each time the user selects "CLOSED" status and hide each row individually. — AlexShevyakov, Feb 02 '19 at 09:23

Hiding multiple rows in google sheet based on cell value - perfromance issue

1 Answers1

Modified script:

References: