Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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") {
      sheet.showRows(i);    
    }
    else {
      sheet.hideRows(i);

  }
  }

  sheet.setActiveSelection("A1");
};
share|improve this question
    
Were you able to reproduce the script? –  Jacob Jan Tuinstra Feb 4 '13 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())
    .sort([
      {column: 1, ascending: true}, 
      {column: 3, ascending: false},
      {column: 2, ascending: true}])
    .getValues();

  // 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') {
      cFirst++;
    } 
  }

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

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

  sheet.setActiveSelection("D1")
    .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.

share|improve this answer
    
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). –  Vidar S. Ramdal Jan 29 '13 at 14:29
    
@VidarS.Ramdal What if you had formula's present? –  Jacob Jan Tuinstra Jan 29 '13 at 14:30
    
@VidarS.Ramdal How would you hide the rows? By not adding them? Then you would lose all data. –  Jacob Jan Tuinstra Jan 29 '13 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. –  Vidar S. Ramdal Jan 29 '13 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? –  Nick Kruckenberg Jan 31 '13 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");
}
share|improve this answer
    
Could you add some explanation to your code, perhaps in-line comments? –  Vidar S. Ramdal Mar 13 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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