0

Here is my code:

function dataValidation()
{ 
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2019년 출결 정보");
  var ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("원생 정보");
  var array = [];

  for (var c = 0; c <= 1000; c++)
  {
    array[c] = [];
  }
  var count = 0;

  var lastRow1;
  var data1 = ss1.getRange("C:C").getValues();
  for (var k = data1.length-1; k >= 0; k--)
  {
    if (data1[k][0] != null && data1[k][0] != '')
    {
      lastRow1 = k+1;
      break;
    }
  }

  var lastRow2;
  var data2 = ss2.getRange("A:A").getValues();
  for (var n = data2.length-1; n >=0; n--)
  {
    if (data2[n][0] != null && data2[n][0] != '')
    {
      lastRow2 = n+1;
      break;
    }
  }


  for (var i = 2; i <= lastRow1; i++)
  {
    for (var j = 2; j <= lastRow2; j++)
    {
      if (ss1.getRange(i, 3).getDisplayValue() == ss2.getRange(j, 1).getDisplayValue())
      {
        array[i-1][count] = ss2.getRange(j, 2).getDisplayValue();
        count++;
      }

    }

    var rule = SpreadsheetApp.newDataValidation().requireValueInList(array[i-1], true).setAllowInvalid(false).build();
    ss1.getRange(i, 4).setDataValidation(rule);
    count = 0;
  }

}

I am so concerned because the compile speed takes too long... It takes about a minute to fully execute :(

Execution hint suggests to change from line 71:

for (var i = 2; i <= lastRow1; i++)
  {
    for (var j = 2; j <= lastRow2; j++)
    {
      if (ss1.getRange(i, 3).getDisplayValue() == ss2.getRange(j, 1).getDisplayValue())
      {
        array[i-1][count] = ss2.getRange(j, 2).getDisplayValue();
        count++;
      }

    }

Personally I think this is because of nested loops but I don't have any clever ideas to replace this loops, nor I have much knowledge about google apps script. What else I can do to decrease compile time?

Rubén
  • 34,714
  • 9
  • 70
  • 166
NAMSOO LEE
  • 21
  • 2
  • 2
    You need to rethink your script to use getValues()/setValues() instead of getValue()/setValue(). Each getValue()/setValue() is a call to the server which can affect performance. Also you should look at getDataRange() to avoid getting rows that contain no values instead of using getRange("A:A"). See https://developers.google.com/apps-script/best_practices – TheWizEd May 17 '19 at 16:41
  • For an example: https://stackoverflow.com/questions/49020131/how-much-faster-are-arrays-than-accessing-google-sheets-cells-within-google-scri/49020786#49020786 – tehhowch May 18 '19 at 13:19

0 Answers0