Spreadsheet script usually takes 7 seconds to run now takes over a minute

Question

The following script below used to be faster but is now much slower. Are Google's servers just overloaded today?

Is there a way I can make this script run faster?

function main() //ACTIVE
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();

  var headerCellEntryRow = ss.getRangeByName("headerRow").getRow() + 1;

  var dateRangePostedCol = ss.getRangeByName("serviceDatesPosted").getColumn();

  var datePostedRange = s.getRange(headerCellEntryRow, dateRangePostedCol);
  datePostedRange.setValue(new Date()).setNumberFormat("MM/dd/yyyy");;


  s.insertRowBefore(headerCellEntryRow);

  s.setFrozenRows(headerCellEntryRow);

}

please go through this answer might suit your need : https://stackoverflow.com/a/8608327/17637655 — Ameya, Jul 05 '22 at 05:23
It takes me about 5 seconds. I find that inserting rows can take a fair amount of time but I wouldn't expect it to take a minute unless you are having trouble with you connection. — Cooper, Jul 05 '22 at 14:29
@MiMiI tried it on my home wifi and on my 5G hotspot and on 2 different computers. Other users are showing long script times according to the execution history as well. I noticed that when I deleted the 1000 rows, in the sheet the script sped up significantly. However, it was still running pretty fast when I was at 990 rows so I don't see how having 10 more rows made it really slow. — William Toscano, Jul 05 '22 at 19:24

Logan · Answer 1 · 2022-07-05T06:31:19.010

1

There are many factors to consider here on the performance of the script. Here are some best practices you can do to make your script faster: https://developers.google.com/apps-script/guides/support/best-practices#:~:text=Use%20batch%20operations,-Scripts%20commonly%20need&text=Alternating%20read%20and%20write%20commands,should%20not%20follow%20or%20use.

Looking at your script, for such a short script it should not take that long unless you are working with very big data in your spreadsheet.

Upon checking, all Google services are working fine as of today:

Reference Link: https://www.google.com/appsstatus/dashboard/

Also for such a huge difference from 7 sec to over a minute that alone cannot be caused by a minor change in your script or your data, if you are sure there were no changes made and the script just suddenly increased the runtime I suggest you post your issue/concern here: https://issuetracker.google.com/issues

edited Jul 05 '22 at 06:31

answered Jul 05 '22 at 06:22

Logan

1,691
1
4
11

The only major change was that I added a lot cell protections to the sheet. However, the script still completes compiling. Does the compiler get slower by having to evaluate range protections and permissions? – William Toscano Jul 05 '22 at 06:46
@WilliamToscano The cell protection does impact the script performance but it should be negligible. Have you tried running it multiple times if it is consistently slower than the usual? Since there are times that the script runs very slow but it happens occasionally. – Logan Jul 05 '22 at 06:54
yes it is running slow each time I am running it's still slow. It was slow all of yesterday and all of today. Other users are experiencing it slow (according to the execution history). I made a separate copy with a separate account and I ran the script and it's slow on the other copy too. This only started happening after adding the cell protections and after changing cell colors and adding a lot of data validation throughout the sheet. The data validation utilizes custom formulas down the columns and the sheet has over 1000 rows. Could it be the custom data validation? – William Toscano Jul 05 '22 at 07:54
I just removed the custom data validation and it still takes over a minute to run such a simple script. Does Google throttle speeds if you run scripts too often? – William Toscano Jul 05 '22 at 07:58
@WilliamToscano Try deleting all the empty rows at bottom and empty columns. – TheMaster Jul 05 '22 at 09:17
@TheMaster I noticed that when I deleted the 1000 rows in the sheet the script sped up significantly. However, it was still running pretty fast when I was at 990 rows so I don't see how having 10 more rows made it really slow. – William Toscano Jul 05 '22 at 19:25
@WilliamToscano I don't think it is really caused by the additional 10 rows but mainly because it goes over 1000 rows. Since by default sheets comes with 1000 rows, you can add rows below with the button at the end which also by default is 1000 but can be edited. I'm assuming that having more than 1000 rows can add some complexity to the process and impact the performance of the script which is why it is default to 1000. However, I am unable to find any documentation to back this up so we can't be 100% sure. I still suggest you raise your concern here: https://issuetracker.google.com/issues – Logan Jul 06 '22 at 02:49

Spreadsheet script usually takes 7 seconds to run now takes over a minute

1 Answers1