Google Apps Script Fastest way to find a row?

Question

I am trying to find out what the fastest way is to get some data from a row where a value matches my query, I am currently using this code:

var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CustomerList'); 
  var klantNummer = array1[0].toString();
  var emailgebruiker = Session.getActiveUser().getEmail();      
  var data = spr.getDataRange().getValues(); 
  for(n=0;n<data.length;++n){ 
    if(data[n][0].toString() == klantNummer){ //.match(klantNummer)
      data[n][21] = emailgebruiker

   var KlantNR = data[n][0];
   var Email = data[n][3];
   var Stad = data[n][12];          
   var Taalschool = firstToUpperCase( data[n][13].toString() );    
   var Cursus = firstToUpperCase( data[n][14].toString() );      
   var Weken = data[n][15];
   var Accommodatie1 = data[n][17];
   var TypeAccommodatie = data[n][18];
   var TypeKamer = data[n][19];
   var VertrekDatum1 = data[n][20];

    };
  }
  spr.getRange(1,1,data.length,data[0].length).setValues(data); 

It works fine when I use it in a small sheet but when the sheet gets bigger it starts to become slow, is there a better way to do this?

Answer 1

There is a quick solution that would solve for some cases. You can break the loop after you found a match. This however would not help if your data is at the bottom of your list.

if(data[n][0].toString() == klantNummer){ //.match(klantNummer)
   data[n][21] = emailgebruiker;
   var KlantNR = data[n][0];
   var Email = data[n][3];
   .
   . 
   .  
   var VertrekDatum1 = data[n][20];
   break;
 }

A second way of speeding up a search is to order your data by your search key, klantNummer in your case, and performing a binary search. Without knowing your dataset though this may not be faster as you first need to sort the set before you can search. You can solve this by having a duplicate sheet of your customers that is pre-sorted by customer number. That would be the sheet you use to do you search.

Here is a binary search in javascript. This is from https://github.com/Wolfy87/binary-search. I modified it to work with multidimensional arrays as the way getValues() return them. The last argument in search index in which column the customer number is in. This code below was returning the row number in about .139 seconds in a dataset off 2000 records.

  function myFunction() {
  var customerId = 11359;
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getDataRange();
  var customers = range.getValues();  
  var index = binarySearch(customers, customerId,0);

  Logger.log(index+1); // This is the Row in the spreadsheet.


}


/**
 * Performs a binary search on the provided sorted list and returns the index of the item if found. If it can't be found it'll return -1.
 *
 * @param {*[][]} list Items to search through.
 * @param {*} item The item to look for.
 * @return {Number} The index of the item if found, -1 if not.
 */
function binarySearch(list, item,column) {
    var min = 0;
    var max = list.length - 1;
    var guess;
    var column = column || 0

    while (min <= max) {
        guess = Math.floor((min + max) / 2);

        if (list[guess][column] === item) {
            return guess;
        }
        else {
            if (list[guess][column] < item) {
                min = guess + 1;
            }
            else {
                max = guess - 1;
            }
        }
    }

    return -1;
}

Answer 2

I've found that JSON object saved as a text file inside Drive works really fast, a 6mb text file is read then parsed in under 200 milliseconds. With this, you can save all lines of the klantNummer to the Object and call it, get the lines and get all values, under 1 sec, as such:

Save, update and get klantNummer examples:

function saveFile(){
  var klanNumbers = {'ben@gmail.com ': 3, 'dora@gmail.com' : 12, 'ksd@gmail.com' : 33};
  return DriveApp.createFile('klanNumbers', JSON.stringify(klanNumbers));
}

function updateFile( newKlanNumb, klanLine ){
  var klanNumbFile = DriveApp.getFilesByName('klanNumbers').next(),
      file = JSON.parse(file.getAs("application/none").getDataAsString());

  if( file[ newKlanNumb ] )
    file[ newKlanNumb ] = file[ newKlanNumb ] + ',' + klanLine;
  else
    file[ newKlanNumb ] = klanLine;

  klanNumbFile.setTrashed(true);

  return DriveApp.createFile('klanNumbers', JSON.stringify( newKlanNumb ));
}

function getKlanNumbers( klanNumb ){
  var file = DriveApp.getFilesByName('Transactions').next(),
      info = file.getAs("application/none").getDataAsString(),
      klanNumbers = JSON.parse(info);

  return klanNumbers[klanNumb];
}