Appscript Speed question

[Bobby Clowers]

With the help of a few people a last year, they helped put together an on edit script that worked pretty good this past year.  Once school end I have been tinkering with trying to make the script run faster and kinda succeed.  Everything seems to work, but with the last thing it does it seems to slow down before it set the cell(its only adding a checkbox).  I think it has something to do with the offset, which is 10, 9 and below all pop at the same time.  I add the same line and added an offset off 11 and its like it paused to do that one also.  Hope that makes sense.  

I'm adding the sheet and a video to see it in action.

Here is LINK to the sheet.

Any help would be appreciated.  It's not that big of a deal, but figured I'd ask anyway.

Thanks

[Edward Wu]

One quick tip that I use when trying to figure out potential speed bottlenecks is to use console.time and console.timeEnd.

e.g. if I suspect that part of my code is slow, I do something like this:

console.time("🛠️ description of code snippet");

...code snippet that I suspect is slow...

console.timeEnd("🛠️ description of code snippet");

And making Google Sheets changes in batches tends to be faster than doing them individually.

Please look at the code changes I made in lines 79-94.

First, the code is building a "group" of ranges.

rangesToClear = [

getOffsetColumn(src, r, 4),

getOffsetColumn(src, r, 5),

getOffsetColumn(src, r, 7),

getOffsetColumn(src, r, 9),

getOffsetColumn(src, r, 10)

];

Then converting them to A1 notation

let rangesToClearA1Notation = rangesToClear.map(range => range.getA1Notation());

Then doing the clearing in one step

src.getRangeList(rangesToClearA1Notation).clearDataValidations().clearContent();

It's not instantaneous but does *feel* faster to me when I did some testing.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/829d0bbd-11f5-45d5-9fe8-544ea4a22102n%40googlegroups.com.

[Bobby Clowers]

Thanks for that info.   The clearing does seem faster for sure.  When you were testing did you notice when its adding the info it seems to add the  3 dropdowns and the first checkbox all at the same time, but the last checkbox it seems to hang for a second before it add that one.  Any idea what is happening there?  I would have thought it would add them all at the same time like it does with the first 4 commands.

[Edward Wu]

Yes, I did notice that. My *guess* is that the delay is "cosmetic". If you look at the timing from the script run, it seems like, as far as the *script* is concerned, it finishes before the effects actually "show up", if that makes sense.

i.e. even if the script is instantaneous, it *seems* like Google Sheets itself can take a second or 2 to actually "show" the results.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0545c69e-590e-4524-bff1-f2525257b930n%40googlegroups.com.

[Bobby Clowers]

Gotcha,  Thanks for the help.