I've made the following code in order to compare two columns between one and another, however it's taking more than a hour for the code to execute and sometimes it error out.

function testif() {
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getSheetByName('test Data');
  const workingCell = ss.getRange(2,1).getValue()
  const today = ss.getRange(2,2).getValue();
  const Avals = ss.getRange("A1:A").getValues();
  const Alast = Avals.filter(String).length;

  for(var i =2;i<=Alast;i  ){

  const today30 = ss.getRange(i,3).getValue();

  if(workingCell >= today && workingCell <= today30){
    ss.getRange(i,4).setValue("Complaint");
  } else {
    ss.getRange(i,4).setValue("Non Complaint"); 
  }
  }
}

As you can see the code goes row by row and sees the if both of the conditions applies and insert data to the last column, I think that the issue is that the code is going row by row hence taking more then 1 hour to complete. Is there a way to fix this issue and make the code run faster?

For more clarity:

This is how the data looks originally:

And basically compare doing what the script does is and If(And)) between the columns, that goes as follow

=IF(AND($A2>=$B2,$A2<=$C2),"Compliant", "Non-Compliant")

CodePudding user response：

I believe your goal is as follows.

• You want to reduce the process cost of your script.
• You want to convert a formula of =IF(AND($A2>=$B2,$A2<=$C2),"Compliant", "Non-Compliant") to Google Apps Script.

Modification points:

• In your script, getValue and setValue are used in a loop. In this case, the process cost becomes high. Ref

When these points are reflected in your script, it becomes as follows.

Modified script:

function testif() {
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getSheetByName('test Data');
  const values = ss.getRange("A2:C"   ss.getLastRow()).getValues();
  const res = values.map(([a, b, c]) => [a.getTime() >= b.getTime() && a.getTime() <= c.getTime() ? "Complaint" : "Non Complaint"]);
  ss.getRange(2, 4, res.length).setValues(res);
}

References:

• getValues()
• map()
• setValues(values)