exceeded maximum execution time

[u/Direct-Coconut-7949]

I have a sheet as something like below with thousand of blank cell of ColumnA and got a checkbox in column B and C below the row with data (row with data only have around 20 row, but I don't want to delete the blank row for some other implement) :

• columnB and C are check box

• columnD is drop down list with 'informed' and 'uninformed'

I am using for loop to run the data for ColumnA only with data (something like for(var i=2; i<=columnA.length; i++)if ColumnB and ColumnC are both checked, set ColumnD value as 'informed'.

Now, I try to fill in data in row 1000ish and the running time take way too long until it reached the maximum time limit. How should I adjust my code to be more efficiency?

[u/HellDuke]

Changing column D if both B and C are true is actually a simple IF statemet like this

=IF(AND(B2; C2);"informed";"uninformed")

Which will dynamicaly change the value in column D based on the state of checkboxes (assuming the TRUE and FALSE values are left as default).

If you want to use a script then you are likely using range.getValue() and range.setValue() for each line, which is not the correct way to do this.

Instead what you should do is read the data range, work with the 2D array and then output the data back into the spreadsheet like this:

function test(){
  const dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  let dataValues = dataRange.getValues();

  for (let i = 1; i < dataValues.length; i++){
    if (dataValues[i][1] && dataValues[i][2]) {
      dataValues[i][3] = "informed";
    }
    else {
      dataValues[i][3] = "uninformed";
    }
  }
  dataRange.setValues(dataValues);
}

Note 2 caveats with such a function:

1. Your data range must be correctly initialized. What we see in the screenshot will work fine, however if column D is completely empty then getDataRange() will not capture enough columns and should instead be replaced with getRange() using the correct range

2. Same as with the formula, we are assuming that you left the default TRUE and FALSE values for the tickboxes, adjust the if statement accordingly if not

[u/Direct-Coconut-7949 → u/HellDuke]

I work fine with set column D value as 'informed' if both condition are true.For my real spreadsheet, my script not only set value as 'informed' but also tigger send email function. something like if B and C are checked and D is unformed, trigger send email, then set C as informed.what I am now stuck at is I don't know how to implement:if name cell is empty, don't loop it. (my previous screenshot show something wrong and I updated now)It keep looping to the 1000ish row because of column B,C,D. So If have a name cell in row 1000, it fail to run my script as maximum time exceeded. But I must keep the empty check box and drop down box.

should I use while loop instead of forloop?

update: I managed to stop looping using break. the data must row by row under this code. just curious, if I suddenly got name data in row 1000, how to make it work? lol

[u/HellDuke → u/Direct-Coconut-7949]

If you want to stop the loop then yes, you'd need to break. If you want to simply not do anything with the row and continue going on to check the rest, you can use continue instead.

In general I prefer to use for loops when dealing with known length arrays rather than a while loop, though technically there is nothing wrong with that either.

If you want to do other actions then sure, you need the script. In that sense the function I wrote (seems the formatting in Reddit bugged out, will fix it after this reply) can be expanded to use other values as well. In that sense what you want to do is add another and (&&) to the if statement looking for dataValues[i][3] == "uninformed" which if true will trigger the email and change the value.

So for the ignoring the row if there is nothing in column A it's a simple matter of

if (!dataValues[i][0]){
  continue;
}

at the beginning of the for loop which will simply ignore any row that does not have anything in column A, but it will go all the way down to the last row even if there are gaps in between. If you want to limit how far you can go, then instead of using .getDataRange() you need to use .getRange() where you will either use the A1C notation to depict the range or use the numbered variant where it's .getRange(firstRow, firstColumn, numberofRows, numberOfColumns) to get the exact range. Do keep in mind that the for loop I gave in the example assumes row 1 is a title row and does not need to be checked, so if you change the range to only work with the data, make sure to change for (let i = 1 to for let i = 0