I'm exactly one day into writing Google scripts. I've managed to write one that will filter data in a spreadsheet with 24 columns and, for now, about 900 rows.

The problem is, it's slow. Any suggestions for how to make this script more efficient? I should note that the script runs slow without the sort or setActiveSelection commands.

function open_interventions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  var numRows = range.getNumRows()-1;

  range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

  for (var i = 2; i <= numRows+1; i++) {
    var checkstatus = sheet.getRange(i, 15).getValue();

    if (checkstatus == "Open") {
    else {


  • Were you able to reproduce the script?
    – Jacob Jan
    Commented Feb 4, 2013 at 12:05

2 Answers 2


The key approach is to minimize the API calls. Performing those in for loops will slow down the process considerably, especially with 900 rows. I've re-worked your code in such a way, that it calls for the Spreadsheet API only twice:

function open_interventions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // start logger time  
  var start = new Date();

  // collect data and sort
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn())
      {column: 1, ascending: true}, 
      {column: 3, ascending: false},
      {column: 2, ascending: true}])

  // set counters
  var cFirst = 0, cSecond = 0;

  // itterate to find number of "Open" rows
  for(var i=0, dLen=data.length; i<dLen; i++) {
    if(data[i][0] == 'Open') {

  // itterate through array (backwards) to calculate the rowIndex 
  for(var j=dLen=data.length-1; j>=0; j--) {
    if(data[j][0] != 'Open') {      
    } else {

  // calculate rowIndex
  var rIndex = data.length - (cFirst+cSecond);

  // hide all rows
  sheet.hideRows(2, data.length); 

  // show (unhide) only "Open" rows
  sheet.showRows(rIndex+2, cFirst); 

    .setValue("Execution time (ms): " + (new Date() - start).toString());

I've added a time tracker thingy to it, so that you can monitor the execution speed. See example file I created: Efficient Sorting. When the file is opened, it will add another menu item called Sorting. There you can play around and see what happens.

  • 1
    Like Jacob says, the API calls (like sheet.getRange(i, 15).getValue()) are time-consuming, and should be kept at a minimum. What you don't have to minimize, is memory space. So whenever you're iterating through spreadsheet data, you could just take a copy of the entire sheet, and store the values in a JavaScript array: var values = sheet.getDataRange().getValues(). Then iterate through values, modifying data where appropriate. When done, write back the array with sheet.getDataRange().setValues(values). Commented Jan 29, 2013 at 14:29
  • @VidarS.Ramdal What if you had formula's present?
    – Jacob Jan
    Commented Jan 29, 2013 at 14:30
  • @VidarS.Ramdal How would you hide the rows? By not adding them? Then you would lose all data.
    – Jacob Jan
    Commented Jan 29, 2013 at 15:07
  • Yeah, my comment was meant as a more generic advise when working with spreadsheet data. In this specific case, you would definitely need to do more than just writing back an array. Commented Jan 29, 2013 at 15:25
  • Thanks, @JacobJanTuinstra. I understand the principle of reducing calls. I cannot get your code to work, though, because your process groups all "open" rows together and then hides them. The other possible values in that column make it a fair bit trickier to push all "open" rows to the top or bottom. I think I reworked the code to call the spreadsheet only once, store the entire range in an array, and then loop through the array variable, but it doesn't seem to go any faster. Suggestions? Commented Jan 31, 2013 at 5:22

Simple fast filter execution for larger sheets...

// x & y are passed as parameters - x = filter / y = column to filter

function displayit(x,y){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.getRange("A1").setValue("Please wait - Filtering .....");
  var data = sheet.getDataRange().getValues();
  if ( x == "Show all"){ sheet.showRows(1, data.length-1);return;}
  sheet.hideRows(1, data.length-1);
  for (var i = data.length-1; i >1 ; i--){
    if (data[i][y] == x){sheet.showRows(parseFloat(i+1))} ;
  sheet.getRange("A1").setValue("Finished filtering");
  • 2
    Could you add some explanation to your code, perhaps in-line comments? Commented Mar 13, 2014 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.