CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrowshow-hidegoogle-sheets-api
AlexShevyakov
AlexShevyakov

Reputation: 423

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

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

Upvotes: 0

Views: 839

Answers (1)

Tanaike
Tanaike

Reputation: 201388

  • 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.

Upvotes: 2

Related Questions