1

I'm looking at a faster solution for this query. Currently the code works but as it is going through 2000 entries, it takes a while to return.

function findName(stuID) {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.setActiveSheet(ss.getSheetByName('STU401'), true);
  var last=ss.getLastRow();
  var data=sh.getRange(1,1,last,5).getValues();

  for(nn=0;nn<data.length;++nn){
    if (data[nn][0]==stuID){break} ;
      }
  var StuName = data[nn][4]

return StuName
VikingPerson
  • 13
  • 2
  • Is this thread useful for your situation? https://stackoverflow.com/q/56663168/7108653 I think that the script of sample 1 of [this](https://stackoverflow.com/a/56663884/7108653) that Class TextFinder is used is easy to use. If this was not useful for your situation, I apologize. – Tanaike Aug 09 '19 at 23:07

1 Answers1

4
  • You want to reduce the search cost using Google Apps Script.
  • In your situation, the value of column "A" is searched, and the value of column "E" at the first found row is retrieved.

If my understanding is correct, how about this modification? In this sample script, your script is modified.

Modified script:

Please modify as follows.

From: 
  var last=ss.getLastRow();
  var data=sh.getRange(1,1,last,5).getValues();

  for(nn=0;nn<data.length;++nn){
    if (data[nn][0]==stuID){break} ;
      }
  var StuName = data[nn][4]
To: 
var StuName = "";
var f = sh.createTextFinder(stuID).findAll();
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    if (f[i].getColumn() == 1) {
      StuName = sh.getRange(f[i].getRow(), 5).getValue();
      break;
    }
  }
}

Note:

  • In this modification, I used Class TextFinder. There are several methods for this situation. So please think of this as just one of several answers.
  • In this script, when no values are found, "" is returned.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165