Google Script array "for" performance

Question

Im working with google spreadsheet, and google script.

I have problem with for() function in array. Its run 346++ seconds and failed.

I need to match data in two columns, and matched data is written in the same row that found, but another column i working have almost 30,000 rows and it takes forever.

Here is the code im working, i have tested it in smaller scale and its worked:

function check2dArray() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("2d array");
  
  var dataInputValues = sheet.getRange("A:B").getValues();
  
  var dataCheckValues = sheet.getRange("D:D").getValues();
  
  var writeResultColumn1 = sheet.getRange("F:F").getColumn();
  var writeResultColumn2 = sheet.getRange("G:G").getColumn();
  
  //clearing data
  sheet.getRange("F:G").clear();
  
  for(i=0;i<dataInputValues.length;i++){
    for(j=0;j<dataCheckValues.length;j++){
      if(dataInputValues[i][0] == dataCheckValues[j][0] & dataInputValues[i][0] != "" & dataInputValues[i][0] != "#N/A" ) {
        sheet.getRange(j+1, writeResultColumn1).setValue(dataInputValues[i][0]);
        sheet.getRange(j+1, writeResultColumn2).setValue(dataInputValues[i][1]);
      }
    }
  }
  
}

Im still new in this, maybe there is better way to solve?

Here is the screenshoot of my sheet

Sheet screenshoot

Code screenshoot

Here is Link to my sheet :

https://docs.google.com/spreadsheets/d/1DVbNaehsTWkiIkzW2nQx7w-ZB8CrPmSP5T5CpU24mbU/edit?usp=sharing

Your loop is slow because you call slow methods inside it, and have a nested loop. 30,000 steps in which you check 30,000 other values each time will absolutely take a long time. Follow the [best practices](https://developers.google.com/apps-script/guides/support/best-practices) and limit your range acquisitions (use a RangeList). You'll still need to adjust your logic to remove the nested loop to have any real improvement. — tehhowch, Oct 03 '18 at 02:00
Some related answers of mine that can give you some ideas for improvement: https://stackoverflow.com/a/50286994 https://stackoverflow.com/a/52214811 https://stackoverflow.com/a/50008569 https://stackoverflow.com/a/51393840 https://stackoverflow.com/a/49519839 — tehhowch, Oct 03 '18 at 02:20
This answer will solve your issue https://stackoverflow.com/questions/7854573/exceeded-maximum-execution-time-in-google-apps-script — Nitin Dhomse, Oct 03 '18 at 06:23

Google Script array "for" performance

0 Answers0