CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheets
tortilla
tortilla

Reputation: 151

strange server error GAS

I have a function running in google script. However it does not finish, it enters the main loop and then it stops somewhere, no errors. If i look in the execution description it says server error wait a while and try again. I tried running it several times but it keeps stalling, but each time the last values of i and j are different from the previous stall. I really can't imagine that this is a real server error, it must be the code.

 function start() {
  var sheet1 = SpreadsheetApp.openByUrl("xx").getSheetByName('Blad1');
  var sheet2 = SpreadsheetApp.openByUrl("xx").getSheetByName('Blad1');
  var range1 = sheet1.getRange(1,1,54,26); 
  var range2 = sheet2.getRange(1,1,10,7); 
  var teller = 0;

  for(var i = 1; i<=range1.getNumRows(); i++){
    for(var j = 1; j<=range2.getNumRows(); j++){
      Logger.log(i);
      Logger.log(j);
      if(range1.getCell(i, 8).getValue() == range2.getCell(j, 2).getValue() && range1.getCell(i, 16).getValue() == range2.getCell(j, 4).getValue() && range1.getCell(i, 19).getValue() == range2.getCell(j, 6).getValue()){
        range1.getCell(i,25).setValue(range2.getCell(j, 7).getValue());
        range2.getCell(j,1).setValue("Script")
        teller++;
      }
    }
  }
  Logger.log(teller);

};

Upvotes: 0

Views: 81

Answers (1)

fooby
fooby

Reputation: 849

I imagine you are hitting the SpreadsheetApp api too much. Best practice is to load the data you need into the script and iterate over it. Using your script as an example:

function start() {
  var sheet1 = SpreadsheetApp.openByUrl("xx").getSheetByName('Blad1');
  var sheet2 = SpreadsheetApp.openByUrl("xx").getSheetByName('Blad1');
  var range1 = sheet1.getRange(1,1,54,26); 
  var range2 = sheet2.getRange(1,1,10,7); 
  var data1 = range1.getValues();
  var data2 = range2.getValues();
  var teller = 0;

  for(var i = 0, r = data1.length; i < r; i++){
    for(var j = 1, c = data2.length; j < c; j++){
      Logger.log(i);
      Logger.log(j);
      if(data1[i, 8] == data2[j, 2] && data1[i, 16] == data2[j, 4] && data1[i, 19] == data2[j, 6]){
        range1.getCell(i,25).setValue(data2[j, 7]);
        range2.getCell(j,1).setValue("Script")
        teller++;
      }
    }
  }
  Logger.log(teller);
};

Upvotes: 1

Related Questions