Script suddenly VERY slow!

[Jeff DeRyke]

Hello friends,

First time posting in this group. I have a large script project that I've been working on for a couple of years that our company is using to track production in a manufacturing environment. Typically, the doGet function that loads the web interface for the tracking tool will execute in 5-15 seconds and is very snappy and responsive. However, as of this morning that function is taking 60-75 seconds per execution. I've been out of vacation since last week and I'm the only developer working on the code, so nothing in the code base has changed, and the underlying data in a Google sheet doesn't seem to have had any major shifts either. I've narrowed things down to see that the reads from Google sheets is the main source of the slow down. I'm reading the data in a batch with getValues(), but a single call to that function on the same size data set is now taking almost 20 seconds, where the entire function ran in less than that 24 hours ago. Has anyone else experienced something like this, or are there any ideas about how to speed up these reads?

TIA!

[Alan Wells]

Thanks for posting the problem your having.  If it's a wide ranging problem at Google, maybe someone else will validate it.

Post the line(s) of code getting the values, and someone may suggest a way to optimize the code that's getting the values.

Hopefully it's a temporary slow down for some unknown reason.  You could try an internet speed test.  Ask someone in your IT department if they've noticed anything.

[Bennett, Scott]

I am running some onEdit functions in Sheets and they are taking 2-3 times longer.  

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2481d221-55e1-45c5-9ab2-8b54bb625c6en%40googlegroups.com.

--

Scott Bennett

[Jeff DeRyke]

Thanks for confirming it's not just me seeing a slow down Scott.  Things do seem to have picked up a bit now, but certainly not to normal run-time speeds. Hopefully Google will have things sorted out in short order.

Here's a sample of one of the lines that was running slow this morning. I was able to confirm that this single getValues() was taking 15-20 seconds by bracketing it with Logger statements and looking at the time stamps.

var pst = ss.getSheetByName("Panel Status Tracker");

var panelData = pst.getRange(9, 1, pst.getLastRow()-8, 8).getValues();

The Panel Status Tracker sheet currently has ~850 rows. It does have a couple hundred columns, but I'm obviously only pulling the first 8 columns with this getValues() call.

Thanks!

[Jeff DeRyke]

Giving this thread a bump, as this morning my run times are back to actually being worse than they were yesterday, and things are getting desperate on my end. I don't know of any way to speed up this line of code that is suddenly taking x5 longer than it was earlier this week.

[Jacob Jan Tuinstra]

Use the Crop Sheet Addon to restrict the range to data only. Very small mod would be:  .getSheetValues(9, 1, -1, 8).

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d0541a84-248f-4d46-bdc6-ed94d2ab46den%40googlegroups.com.

[Jeff DeRyke]

Thanks for the tip! My sheet is already trimmed to remove all unnecessary white space. The sheet is indeed massive, but I'm using all of it.

I did try getSheetValues, which is a function I didn't know and is certainly more efficient to write, but I didn't see any improvement in performance.

[Jacob Jan Tuinstra]

this operation should be rather quick in my experience. Is this all the onEdit trigger comprises?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2adaedcf-5037-406d-ba50-b5a1af068ea6n%40googlegroups.com.

[Jeff DeRyke]

The operation was very quick until sometime between 7/7 and 7/8, and then all of my server calls to access the sheet have been crawling ever since. There were no changes to my code or significant updates to the data or sheet layout between when the code was running fast and when it slowed down.

This isn't part of an onEdit trigger, it's part of a doGet function to build an html interface for a web app. There are a number of other steps to that function, but the snippet I posted above is the first real work the function is doing, and I have been able to determine that this line (and others like it) are the causes of the code slow down.

[Jack Z]

Interesting.  I noticed that iterating over a bunch of cells in two ranges was taking an exorbitant amount of time  and wondered why that would be the case, but this sounds like it could be the same issue.

Hopefully this will get fixed soon.

[Anthony Ciceron]

Hi everyone, 

I'm new and french so sorry for my english !

I have the same problem. I also have a large project i'd created about 2 month. I'm just starting to share it with employees of my company.

When i used it alone, i didn't have this kind of problem.

So, for example, i call the doGet function to display an html window. It always take about 4 seconds. 

But sometimes, for 10-15min it takes about 60sec to execute all of my functions.

Something very strange : 

When this function is loading and very slow, i go to the debugging window (which show status, time and duration for each request).

When i go to this windows, the function suddenly works ...

I really don't know where is the problem from. 

There is something we do wrong : we are 2 or 3 people who connect to my project with the same Google account. I'm working on this but i'm not sure the problem is due to this.

So if you have resolved your problem please tell us !!!

Thanks !

[Scents Deliveries]

Hey everybody. I know I'm late to the party, but I have found a long and complicated, but (as of our first test runs Friday) viable workaround to the slowdown solution. My company had the same issue pop up around the same time as the original poster, and while I cannot say for certain why it happens, here's where I've noticed it happens:

On our largest sheets that run a large number of formulas alongside large amounts of data seem to have the most problems being accessed, read, and to a lesser extent, written to, from Scripts. This problem either doesn't occur, or occurs with far less of a slowdown, when called from bound scripts attached to the Spreadsheets in question. The problem also doesn't seem to occur with custom formulas (I'm guessing because you don't have to open the Spreadsheets in the code, since you're just passing in arrays to the functions). The problem does, however, seem to most often occur on library functions (which are super useful to maintaining a unified codebase).

Our solution has been to use the following techniques to reduce slowdown times:
1) Get the sheet variables of the foreign sheets inside the local functions of the bound script, and pass those variables into the library functions. Since the library functions seem to struggle far more with getting the sheets than the bound functions do, opening a sheet object once locally, and passing it into multiple library calls, seems to both alleviate the sheet variable load time error a small bit, and reduce how many times you're calling this suddenly expensive operation.
2) The harder one: we're using a combination of custom formula hashing functions, the Cache Service, and timed functions to keep as much of our most-used data in the cache for as long as possible. Even on days where our column read time (which before June took seconds) would take 2-5 min to complete, reading from the cache seemed always to take only a second to load, and another to read all of the stored data (so for the moment, it seems immune to whatever shenaniganry google is up to).

3) Create your own custom read function that condenses range.getValues() calls into fewer ranges. If you gotta read from A, B, C, E, F, and H, your function should read the ranges as A:C, E:F, and H:H, and then break them apart into their own arrays inside the code. It's way faster when you're having the slowdown to run a little extra computation than it is to run a couple extra reads.   

Step 2 is the heart of the greatest speedup change, and the more complicated of the bunch. If you're not familiar with Cache Service, this guy does a pretty good job of explaining it: https://www.youtube.com/watch?v=Z0rzBzv-vY4&list=PL42xwJRIG3xCCAeJUuxtmr47NYC8sRQ2d&index=1 . Note: our data system is organized into columns, so if your data is organized more by row than column, some adaption is necessary.

So, the basic idea of the solution is that, the first time we read a column of data, we also save that column of data into the cache (we set ours for 30 min before timeout, but do as you will), and whenever we read data, we first check if that data is in the cache, and if so, get the data from the cache. That way, you should only have to actually read data from the sheet that wasn't already stored in the cache, or that timed out. Some limitations of the cache is that (as far as I know), it only works with strings and other primitive data types. Additionally, the cache doesn't keep a live version of your data; you have to update it each time.

The solution to the first is pretty easy. JSON.stringify() turns whatever parameter you pass into it into a string, and JSON.parse() converts the string parameter back into whatever data type it was originally. So, when we want to store data into the cache, we just convert it to a string, and when we retrieve it, we convert it back. Keeping the data live is a little trickier. 

Say I have a column of someone's favorite movie series ranked in order with the column header "movie columns": 1) Lord of the Rings, 2) Star Wars, 3) Fast and Furious, etc... I store this list into the cache using the header, "movie columns", as my key. However, after that, the person watches the Harry Potter series and decides Harry Potter is their new favorite series, so that becomes #1 on the list, and everything else moves down one. If I go to read the sheet, my program will check the cache first, find "movie columns" in the cache, and return the original list instead of the new one, since the cache only has the original list stored. So, I need a way to tell if the data inside the cache has become outdated, and read new data rather than the old, outdated data. This is where the hashing functions I mentioned earlier come in.

My company uses SHA-256 for our purposes. The basic idea is: you call Utilities.computeHmacSha256Signature(stringifiedColumn, standardizedString).join(""), passing in a stringified version of your column into the first parameter, and a string that you want to be the same for every instance of this function, as a second parameter. I'll explain why in a moment. What this function does is it takes your strings, and it generates a 256-bit (32 character) output string. This string varies wildly between columns. Heck, just changing a 0 to a 1 in the column drastically alters the resulting string. The idea is: we use this string of numbers, rather than any name or column header, as our keys for our cache. That way, if even the slightest bit of data in the column changes, the hash key will also change, and when you go to read the data from the cache, that new hash key won't be in the cache, so that new column data will be read from the sheet rather than the cache, while all other unchanged data will still be read from the cache using their unchanged hash codes. 

 We implement the hash function locally on all sheets using bound scripts to make custom formulas. Each page that we use the Cache in has a special hidden sheet called "SHA-256"; this page has only one cell, which has a custom formula that takes in any number of columns (and is also specialized to intake and separate batches of consecutive columns), and returns a stringified object that maps the column header to a hash key. Like I said above, custom formulas don't seem to suffer the slowdown, so this thing usually seems to update 5 columns of about 500 cells within a few seconds of change (you can use SpreadsheetApp.flush() to force the custom formula to finish calculating before you continue). Then, whenever we want to read from a sheet, we first check if that sheet has a SHA-256 sheet, and if so, read that one cell, parse the string, and use the hash keys that the headers map to as cache keys. 

Note: make sure that all instances of this custom formula use the same standardizedString. You're not implementing security here, so the string's complexity or randomness doesn't matter. The string is here to make sure that all data is hashed the same way (and therefore, very, very unlikely to evaluate to the same value as another set of data and cause weirdness with your read functions). 

There are a few more notes here: the cache values can only hold 100KB of data per value, so if you have particularly large columns with lots of data in each cell, you'll want to break the stringified versions of the data down into smaller slices, and store them with some form of numbered prefix or suffix. If you have that problem, you'll also need to create code to check for the first instance of that numbered prefix or suffix on data that wasn't found under just the pure hash key. We split ours into 25000 characters at a time, just to be safe (though I personally have no idea if that's ideal or not, only that it works). 

Next note: I personally recommend using Indirect inside your custom functions. Using =SHA_256(Indirect("Movies!A:A")) makes sure that you're always targeting the right spot with the formula, no matter what. 

Next note: I mentioned using timed functions to speed this up further. The basic idea is that, since locally called functions don't seem to suffer the slowdown as severely, you periodically have a local function from that spreadsheet's bound script load all of the data columns that you have in your hash function into the cache (so that the first user to read from the sheet isn't slowed down). 

Last note: sometimes, the cache might still be a problem, and you'll need a way to clear it. We keep a list of all of the hash-keys that we push data into the cache under in the Properties Service. Properties service works basically the same as the cache, except that it doesn't time out, and it's not as efficient. We keep and update a stringified object of cache keys mapped to true inside properties service under the key "cache keys". Whenever we want to clear the cache, we just get this property, parse it, get the object's keys, and remove them from the cache. Boom, cache is cleared. 

We just got this implemented Friday, so if any of this suddenly doesn't work, we'll let you know. Hope this helps! 

[Anthony Ciceron]

Wow ! A great thank for your response !!!

What you said is very interesting ! Now i'm just starting to use my script project in my company so i won't do it for now.

But i'll do it maybe in 2 or 3 months :) 

And i'll let you know what are my results !

So again, thank you for the time you took ! Have a nice day 

[Edward Ulle]

For a few weeks now I have also experienced slow down in script development.  Some times it takes a long time to save the changes to a script.  My script has a dialog so I can visually see problems.  I run the script and the dialog does not fully paint, but it says script has finished.  If I close the dialog and run the script again the dialog fully paints.

[Christopher Webster]

So I started getting reports from users that one of my google web apps is running slow.  This app has not been updated for a while so I was surprised to see the sudden issues.  As I began investigating the issue I also noticed the GAS script editor is running painfiully slow.  Any idea what is going on?

[Allan Ostrea]

Agreed! Both script execution and script development are considerably slower. =( Prolly a server problem?

[Barak Harizi]

Hi, Same here, my app is way slower than before, and I use it as a backend for a chrome extension I've built.
Perhaps it is intentional by Google?

[Anthony Ciceron]

Hi everyone !

Due to all of the answers that you published here, I considerally reduce my spreadsheet calls.

Be sure that you don't call a spreadsheet (spreasheet.getrange for example) in a loop. I did that mistake i saw with your advices.

Also, i save my datas in the cache service, as some of you had mentioned.

Then now i don't have this slowdown problem :)

[john virtualized]

The solutions suggested here will work, but they are the wrong solutions.  It is an incorrect approach to require a World Community of users to be forced into arcane inferiors overly complicated coding practices because of slow script operations when a single good system developer should be able to dig into the real problem and single handedly fix it for good for everyone. 

To put it another way, the right solution is for Google to fix the problem.  

When I can publish my updated 1000+ line spreadsheet to the web in about 2 seconds, and calculations without functions can be executed quickly in a timely manner, that is proof positive the problem can be easily fixed if done in the right way.

The situation is now so bad that when I do one single call on a function that simply returns the input argument, and it takes about 1 second to complete every time the argument cell is updated.

Here is function I used for the test which generally takes a second to load and execute:

function TRY (get) {

  return get

}

The system I used to test the problem has a top end laptop Ryzen 4800H cpu with 16 virtual processors, and my connection to the web is about 125/125  Megabits/sec  upload/download.

Please Google, do the right thing and fix this problem for good.

Sincerely, JS

[Harekrishna Acharya]

I am new to this group. Apologies if my question does not make sense. 

This is a serious issue. There is a 3x jump in delay on html page load. the app reads from a sheet and responds to user input. The delay shows  no signs of going away.

I do not see a bug report on this slowdown issue on google issuetracker. There is one but that was closed as Obsolete. (https://issuetracker.google.com/issues/222338447?pli=1).

There is a lot of data that people have collected here which I wish had a issuetracker id.

When does a serious issue discussed in this community get reported to Google?

best

[Alan Wells]

Issues discussed in this group are not officially referred to Google for correction. I'm not sure what the current status is concerning anyone from Google monitoring this group, but the amount of involvement from Google in AppsScript seems to have dropped off considerably in the last 2 years. You can submit a new issue on the Issue Tracker, which I encourage you to do. Work being done on fixing AppsScript problems seems to have become a very low priority over that past 2 years, although I have seems some activity recently from Google.  So, I'm giving you bad news, with maybe a tiny bit of hope.

[mort piedra]

Using the SHa-256 as cache key seems (to me) incredibly advanced and incredibly useful to solve an issue I am facing with performance (not that of the original post though).

As I really have little knowledge/experience with programming GAS I am having a hard time understanding how to set up this solution technically. I have tried googling, but found nothing so far.

Is there an example/video/tutorial out there that you know of which shows this (or similar) concept?

Im hoping for your feedback.

[Paul Armstrong]

If you are talking about this https://developers.google.com/apps-script/reference/cache

Then it's pretty simple as it operates like a map. 

You put things in with a key, indicating how long they should be cached for, and then get the thing out with the same key. 

You also need to choose one of three types of caches depending on how you want the data scoped. 

It only accepts strings. You can NOT store object references. 

I use it to store JSON and stringify it in and parse it out.

But your reference to SHa-256 makes me think you are talking about something else?

[Guy Baker]

I'm also finding Google Sheets scripts unacceptably slow, they really are usable like this, faster to download to excel and run the processes on the PC!