I have more than 1000000 record how to speed up search in sheet. I normally search for 20s how to improve ? (sheet include 20 column and 10000 record)

var ss =  SpreadsheetApp.openByUrl(urldb);
var ws =  ss.getSheetByName("Account");
var data = ws.getDataRange().getValues();

for(var i = 0; i < data .length; i++){

    if(data [i][1] == "ID998724"){

      Logger.log("found you" + data [i][1]);

    }
};

return data[i][1];

I have more than 1000000 record how to speed up search in sheet. I normally search for 20s how to improve ? (sheet include 20 column and 10000 record)

var ss =  SpreadsheetApp.openByUrl(urldb);
var ws =  ss.getSheetByName("Account");
var data = ws.getDataRange().getValues();

for(var i = 0; i < data .length; i++){

    if(data [i][1] == "ID998724"){

      Logger.log("found you" + data [i][1]);

    }
};

return data[i][1];

• javascript
• google-apps-script
• google-sheets

• 3 Since you're only looking in column 2 for your data, instead of using .getDataRange() have you tried using .getRange() for just column 2 and passing the amount of rows your sheet has? – ross Commented Jun 19, 2019 at 8:31
• A million records should probably be in a database, not a spreadsheet, but anyway, could you do the fetching of the data asynchonously? Request about 1,000 or 10,000 records at a time, and as the next batch downloads search the previous. (And is this a better fit for Code Review?) – Ken Y-N Commented Jun 19, 2019 at 8:40
• @loc dinh Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Commented Jun 25, 2019 at 22:47
• Thanks all for answering my question ! I want speed up find also get data row it – Naoa Lee Commented Jun 26, 2019 at 7:15

1 Answer 1

• You want to search the value of ID998724 form the column "B" on the sheet of "Account" in Spreadsheet using Google Apps Script.

If my understanding is correct, how about these 3 sample scripts? Please think of this as just one of several answers.

Sample script 1:

In this script, I used Class TextFinder for this situation. This was added in a recent update of Google.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var f = ws.createTextFinder(searchValue).findAll();
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    if (f[i].getColumn() == 2) {
      Logger.log("found you" + f[i].getValue())
    }
  }
}

Sample script 2:

In this script, the values are retrieved from the column "B". This is also mentioned at ross's ment. And also from the result of benchmark, I used filter() for this situation.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var data = ws.getRange(1, 2, ws.getLastRow(), 1).getValues();
var f = data.filter(function(e) {return e[0] == searchValue});
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    Logger.log("found you" + f[i])
  }
}

Sample script 3:

In this script, I used Query Language for this situation.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var query = "select * where B='" + searchValue + "'";
var url = "https://docs.google./spreadsheets/d/" + ss.getId() + "/gviz/tq?gid=" + ws.getSheetId() + "&tqx=out:csv&tq=" + encodeURIComponent(query);
var options = {headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}};
var csv = UrlFetchApp.fetch(url, options);
var f = Utilities.parseCsv(csv);
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    Logger.log("found you" + f[i][1])
  }
}

Note:

• In your script, I think that an error occurs at return data[i][1]. Because i is the same with data.length. If you want to retrieve the value by return data[i][1], for example, please put break after Logger.log("found you" + data [i][1]).

References:

• Class TextFinder
• filter()
• Benchmark: Loop for Array Processing using Google Apps Script
• Query Language Reference (Version 0.7)

If I misunderstood your question and these sample scripts were not the results you want, I apologize.