CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
mushroom
mushroom

Reputation: 1945

Google Apps Script Caching Large Data

I'm writing a script for Google Spreadsheet that pulls financial data from an external server.

The live feed is a single JSON which contains all the data required.

However I'm faced with the problem of values taking very long to load because it fetches the JSON for every cell with my function. I could have hundreds of cells with functions like these to get the required data in the JSON file I want: =myFinancialData('data1'), =myFinanacialData('data2'), etc.

I read about Google's cache service but it seems like my dataset is larger than what is allowed. I also tried storing individual data values (e.g. data1, data2) as individual cache but the script times out after exceeding the maximum execution time.

Is there a more efficient way to do such a thing?

Upvotes: 1

Views: 1520

Answers (2)

eddyparkinson
eddyparkinson

Reputation: 3700

Put the data direct into the spreadsheet. ... why: At some point the data will need to go into the spreadsheet so it can do your calculations etc. Even if you use a custom function, your data will still end up stored in the sheet. I suspect you don't gain by using a cache. Also custom functions can be slow to update, often over 3 seconds to kick in, sometimes over 10.

Upvotes: 0

Srik
Srik

Reputation: 7947

Try storing your data in ScriptDB which has higher allowance (50MB)

Upvotes: 0

Related Questions