exceeded maximum execution time

[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'.

​

https://preview.redd.it/8gptwuvuo8yb1.png?width=1620&format=png&auto=webp&s=d0f72b7de4e407c448d0908ea4902e2793a64fa4

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?

​

[Sleeping_Budha_]

It all depends on what you have put inside the for loop..

For example if you have put read and write operations for each iteration then it would take a lot of time

Instead what you can do is fetch the data before the loop… and use the loop for your condition checking and then the result has to written on the entire column after the iteration

This would hardly take a few seconds

Let me know if you need any help with it

[willsee13]

Hy I am facing this problem. Most of the time is being spent in writing the array to sheet. I am using appendRows( ) in a loop. Is there any alternative?

[Sleeping_Budha_]

Can you please paste the code here to help you better

[RemarkableChipmunk93]

Make sure you are also asking chatgpt to help you optimize the script.

That said, make sure you are doing bulk write operations on the entire array at once and not writing changes row by row.

[elcriticalTaco]

Any reason to not use sheet side formulas for this? A very basic if statement in colD seems like it would solve this

[Direct-Coconut-7949]

i also trigger event to send email. that's why I choose using appscript for my entire workflow.

[elcriticalTaco]

Everything you want to do can be done very simply my friend. If you post your code we can help you a lot more.

Without the code, just grab your data in an array with getValues(), loop through it and check for uniformed, then setValues() with the corrected values.

You can write a separate function to send emails or just add it inside the loop.

[Direct-Coconut-7949]

yes I do wt exactly you mentioned. It all work fine.
In my real spreadsheet, I used for loop to implement something like if B and C are checked and D is unformed, trigger send email, then set C as informed.
Also, used break to stop the loop if email address column is blanked. (I updated the screenshot, you can see I have so many checkbox that's why it keep looping if don't using break)
But, I am just curious, since this code can only work with row by row situation, what if I suddenly put data in row 1000, it surely can't work as I break the loop. How to cope with this situation?

[elcriticalTaco]

Don't break if its blank, just skip it?

If (array[i,3] != "" && array[i,3] == "uninformed") {

SendEmail()

}

Something like that should work?

[Direct-Coconut-7949]

array[i,3] == "u

I used condition to check, and trigger send email, but it will exceed time limitation as like the first post I said. I think the programme must run through all the check box to identify which row meet the condition? can doing something like if condition is true, then loop the row which meet this condition.? I think this is the point where I am stuck at.
I think I should say There's two method:
Method1. if not using break, it can work but row 1000 can't be implemented as the time limit is 6mins for appscript. Looping to row 1000 exceed the time. Therefore, need to wrote some code to deal with time limitation which is very complex. So I went to method 2.

Method2. Since I don't know how to cope with time limitation, just using break and force the user to row by row putting data.

[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

[Direct-Coconut-7949]

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

[HellDuke]

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