google-sheetsgoogle-apps-script

Google scripts on execution below code giving error as "exceeded maximum time"

function myFunction(input) {

    var valuetobereturn =0 ;   
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh0 = ss.getSheetByName("Mark-ups");

    for (var row = 2; row <= 193; row = row + 1) {
      //valuetobereturn = 0

      if (input >= sh0.getRange(row,7).getValue() && sh0.getRange(row,8).getValue() ) {
            valuetobereturn = sh0.getRange(row,9).getValue();
           
      }

    }
    return valuetobereturn;

}

a) Parameter "input" is passed to function myFunction

b) which is then checked if it exists in range with sheet "Mark-ups"

c) from row 2 to row 193 for column 7 and column 8

d) it returns value as "valuetobereturn" if condition is satisfied

e) "exceeded maximum time" ERROR to be resolved

Solution

  • When your showing script works if the maximum execution time for the script is large, from your showing script, I understand your expected result is as follows.

    1. You want to check whether for the rows from 2 to 193, the value of column "G" is the same as the value of input and whether the value of column "H" has a value (You want to check True and False?).
    2. In your script, the value valuetobereturn is overwritten when the if statement is true. From this situation, you want to retrieve the value of column "I" of the last checked row.
    3. When the if statement is false for all rows, you want to return the value 0.

    If my understanding is correct, how about the following modification?

    Modification points:

    When this is reflected in your script, how about the following modification?

    Modified script:

    function myFunction(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh0 = ss.getSheetByName("Mark-ups");
  
  // Retrieve the values from cells "G2:I193".
  var values = sh0.getRange("G2:I193").getValues(); // or var values = sh0.getRange("G2:I" + sh0.getLastRow()).getValues();

  // Retrieve the value of column "I" of the last checked row.
  var res = values.findLast(([g, h]) => input >= g && h);
  var valuetobereturn = res ? res[2] : 0;

  // Return the result value.
  return valuetobereturn;
}

    References: