0

I have written some Apps Script code to fetch stock data from Yahoo and Fizviz, and for a while it worked fine.

Ever since I reached a certain number of rows in my table (around 20 tickers), some specific rows stop executing, the cells say "Loading" forever. The row's apps script functions do not show up in stackdriver logs. If I sort the table to trigger a refresh, these tickers are then executed, but some other different specific rows stop executing, so the problem shouldn't be the ticker. Also, the bizarre thing is that the number of rows not executing is different for different sort orders, sometimes I could find a sort order that most of the rows execute. If I delete all rows except one that does not execute, it stays not executed.

Loading forever

I tried to formulate different explanations, and at this point it just all seems like random black magic to me.

Does anyone have the same experience as I am having?

The code is here if anyone is curious, the main entry point functions used by the sheet directly is in FinanceData.gs: https://github.com/hoangbv15/FinanceAppsScripts

Edit: Thank you guys very much for the array formula suggestions. I tried to use it but it cannot cope with my filter table when I change filtering/sorting. Perhaps I am not using it correctly?

hoangbv15
  • 360
  • 2
  • 4
  • 14
  • Are you exceeding the quota? [Apps Script Quotas](https://developers.google.com/apps-script/guides/services/quotas) – James D Apr 02 '20 at 09:40
  • 1
    use a single array formula instead of multiple formulas – TheMaster Apr 02 '20 at 10:19
  • @JamesD I don't think so as it is only my precache trigger that is doing the network calls, the methods invoked by the cells simply lookup the value in another temporary cache sheet. At least I don't see any quota related to reading values from another sheet? Also would it give me an error about exceeding my quota in that case? – hoangbv15 Apr 02 '20 at 18:46
  • @TheMaster I tried, but the rows get all messed up once I change the filter or sorting. Is there a way to use array formula and be compatible with table filter/sort? – hoangbv15 Apr 02 '20 at 18:47
  • No. but you can use query to filter /sort. Something like `= query( custom fun, " where Col1='example' sort by Col1")` – TheMaster Apr 02 '20 at 19:08

1 Answers1

1

I tried to reproduce your error but for the moment it works fine, it's just a bit slow. It could be a quota problem, and I might not facing it yet. To avoid exceeding the quota I suggest you to wrap your custom function calls in an =ARRAYFORMULA(). In this way your n function calls will be batched in one.

I don't think your App logic is suited to work well on Google Apps Script though. You will have to adjust it to reduce the response time otherwise you will get a timeout on the custom function call.

Alessandro
  • 2,848
  • 1
  • 8
  • 16
  • thank you for your reply! unfortunately arrayformula doesn't work as I need to be able to sort & filter the table, however it is still a great idea. W.r.t. response time, my get functions are very quick as they only look in the cache and not do any network calls. It is the precache service that is doing the network calls and putting values into the cache. In theory, the get calls should just return straight away. – hoangbv15 Apr 02 '20 at 16:13
  • I ended up changing to arrayformula, and adjust my sheet to allow filtering/sorting! It works fully now! Strange how it's not any of the quota or slow response time problems, just Google servers acting up I guess. – hoangbv15 Apr 03 '20 at 08:25
  • Yeah, sometimes it's hard to understand what is going on. I'm glad it worked! – Alessandro Apr 03 '20 at 08:29
  • weird I can't set your answer as the correct one? Is Stack Overflow having an issue? – hoangbv15 Apr 03 '20 at 12:51
  • I have no idea, just try later maybe. I see that you tried. – Alessandro Apr 03 '20 at 12:56