JST_search is very slow and often resolves in #ERROR!

[gregorpraprotnik]

For the last couple of months I've been noticing that JST_search is very slow. It's stuck in Loading... for a couple of minutes and then some of them load and some of them randomly fail with #ERROR!.

Temp. User Key: ALEt7zR1odK10eNsUokihs95+PXkrkK9TLwi364keYU+qcQNzwarWJmh18/DFY6Z5lzlq/GUESlG

Steps to reproduce

1. Have a sheet with a couple of cells using JST_search (approx. 80 in my case).
2. Force all cells to reload by inserting a row above the first one.
3. Observe loading of cells. Some take a while, other fail eventually.

Expected result

1. JST_search should be fast to resolve fields from Jira.
2. JST_search should not resolve in error if link to Jira issue is setup correctly.

Actual result

[simonjaz]

Same here.

[LiliKuplenk]

yup, same with me.

[nezacec]

Same here.

[ljay79]

Hi guys, i think its first time we have an issue here, which had so many user interaction within same day :)

As far as i can see and tell, its actually a say performance issue with regards to my bad javascript skills but as well with rate limits enforced on Jira's API endpoints as well.

I will review this specific performance issue a little deeper, but as i am aware of it since some time, im not sure if i can find some new significant improvements.
Every single "JST_search()" call is quite heavy on the Google APIs as well as on Jira APIs before my add-on can actually try parsing all responses which has heavy payload as well (not so nicely tweaked javascript).

PS: You are welcome to contribute with Code or Ideas on the project.

[gregorpraprotnik]

@ljay79 I really like your tool and use it on a daily basis. Thank you for all the effort you've put into this!

The issue I'm describing above is sadly making my sheets unreliable and hard to edit. I would really appreciate it if there is anything you can do about it.

Can you tell if the rate limiting problem is coming from Jira API or Google? I've been trying to find some information on Jira's rate limits but was not very successful. I found some mentions where they say there is some rate limiting going on but no specific documentation.
Regarding Google I've read in the documentation for custom functions you are linking to that calling a custom function multiple times from within one sheet will be slow. Which I guess is my case. There might be a solution which is described here: https://developers.google.com/apps-script/guides/sheets/functions#optimization. What do you think?

[ljay79]

Jira rate limiting is nothing specific to my knowledge. What i was always able to find was just this explanation here:
https://developer.atlassian.com/cloud/jira/platform/rate-limiting/

Google has limits as well, the most relevant ones for my add-on in such cases are the limits of accessing property services:
https://developers.google.com/apps-script/guides/services/quotas

"Properties read/write"
"Custom function runtime"
"Triggers total runtime"
"URL Fetch calls"
"Script runtime"
"Triggers"

From the nature of such add-on, everything is handled client side. Every single action is always a fully fletched complete new runtime process on the client and google. So it does always the full add-on initilization, data check, connectivity check, settings check, jira connection check etc etc etc...
For fetching a single data response from Jira is very much happeing on the add-on :/
ie:

• read quite a lot add-on properties for the user settings, jira settings etc
• connect to Jira api and fetch details
• parse response and compare each returned field with users custom field definitions, then try to parse and format/convert all the values
• read/determine users position / cell pos in the sheets to correctly insert, add, attache data to the sheet
• ...
  .. every time i convert ie. a username entity from Jira response, i will access "get" from PropertyService" about a dozend times. That all will count towards the daily quota limits.

Beside, that all this above is not very performance efficient implement and can slow down the rending a lot :(

[Adel-Magebinary]

@ljay79 Maybe fetchall() would be the solution?

#290

After some debugging, I found that the google script is firing the request one by one which takes a very long time just to fire API requests.

Maybe polling all the requests and firing async calls at the rate of 50 - 150 requests/sec might save the game?

Thanks,
Adel.