CODEWITHSUNDEEP
javascriptloopsfor-loopgoogle-apps-scriptgoogle-sheets
James Sheard
James Sheard

Reputation: 139

For Loops timing out: JavaScript / Google Apps Script

So I am have this issue with Google Apps Script. It is timing out because the app server requests are taking too long. I was just wanting to see if my coding could be cleaned up a bit to run faster or is there another method which would work?

Code below:

for (var y = 1; y < listLast ; y++) {

  var matchor = listSheet.getRange("B" + y).getValue();
  var listEmCo = listSheet.getRange("A" + y).getValue();
  if(matchor == "Match") {
    Logger.log("Do Nothing");
  } else {
  for (var x = 0; x < formLast; x++) {

  if(listEmCo == formData[x]){

    listSheet.getRange("B"+ [y]).setValue("Match");
    break;

  } else {

    listSheet.getRange("B"+ [y]).setValue("No Match");

   }
  }
 }   
}

Thanks for any responses :)

Upvotes: 1

Views: 296

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

Do not use .getValue(); in a loop. This operation is heavy.

Please use range.getValues() and then loop the array to get values.

The plan is:

  1. get the initial range
  2. get data with var data = range.getValues();
  3. make empty array to write new values array = [];
  4. Loop the data and make new array, fill it with proper values [["value1"], ["value2"], ...,]. Note. It should be a 2D-Array.
  5. After the loop define a range to paste new values: rangeTo
  6. Use rangeTo.setValues(array); to paste new values.

See more:

Tag #Another-get-data-from-range-question

See more questions on topic:

http://stackoverflow.com/questions/39859421/google-script-internal-error-after-15-seconds

http://stackoverflow.com/questions/39586911/google-script-exceeded-maximum-execution-time-help-optimize

http://stackoverflow.com/questions/38618266/google-sheet-script-times-out-need-a-new-way-or-flip-it-upside-down

http://stackoverflow.com/questions/44021567/google-sheet-script-multiple-getrange-looping

Code runs too slow

Upvotes: 3

Related Questions