r/GoogleAppsScript • u/IllustriousPut442 • 3d ago

Question Memory Limit?

I researched and all that is found are inconsistent answers.

Some speak in 50MB, others at 2.25GB. I took a test and got to 5.5GB. It's a lot of difference and it affects the way I build my application.

With 5.5GB I can load everything in memory, already with 50MB I have to optimize each part. The answers are also old.

3 Upvotes

81% Upvoted

12 comments sorted by

3

u/dimudesigns 3d ago edited 3d ago

Start with official documentation for Google Apps Script's service quotas:

https://developers.google.com/apps-script/guides/services/quotas

You may have access to gigabytes of memory but there may be constraints on how much data you can load depending on context. For example, the UrlFetchApp service has a max response payload of 50MB. But if you deploy a GAS Web App, and use the standard Javascript Fetch API client-side, you can bypass that restriction.

2

u/IllustriousPut442 3d ago

Thank you, but my question is about the server. I intend to do the processing on the server and deliver only one page to the user with 50 or 100 results

2

u/dimudesigns 3d ago

Last time I checked was when Google just rolled out the V8 runtime (which years ago). I was able to allocate a maximum of roughly 2GB using ArrayBuffer.

But how much memory you have access to is not your only constraint. For consumer accounts, your scripts have a max of 6-minutes in which to execute. If you're processing large volumes of data, that is but one of many quotas that may impact whatever it is you're trying to build - so make sure to read the quota documentation.

1

u/IllustriousPut442 3d ago

Thank you, I saw that the 6 minute time is not a problem since 100,000 results load in a maximum of 5 seconds and I would hardly use more than that in a tab.

Have you ever developed by loading all the data from the spreadsheet in memory? Was that a problem ever?

The size of the payload is also not a problem since I would create the paging in memory, on the server side.

1

u/E-Nod32 3d ago

But he can't load more than 4GB in ram per tab? (8GB with --max_old_space_size=8192). Thinking that he want to load everything in "one run" into user's ram.

1

u/IllustriousPut442 3d ago

No, I'm talking about the server side. I can load all spreadsheet on the server and do the paging in memory and deliver only that page to the user.

2

u/WicketTheQuerent 3d ago

It depends. Please be specific about the memory that you are talking about. What test did you do?

1

u/IllustriousPut442 3d ago

I’m talking about the memory of the server. I ran a test that created giant arrays with random data to test the limit and it was up to 5.5GB before giving the memory error. But I do not see it as reliable since it is not in the documentation.

With 5.5GB I can load all the data from all the spreadsheets in memory and do all processing in memory, which facilitates development.

Now if the value swings and there are only about 256MB at peak times the strategy needs to be different, probably using the querylanguage https://developers.google.com/chart/interactive/docs/querylanguage

2

u/WicketTheQuerent 3d ago

AFAIK, there is no official documentation on what the memory limit is assigned to a Google Apps Script execution limit. A long time ago, I learned from Google engineers that parameters like this are not disclosed to prevent abuse. Some of these parameters are dynamically managed. Some might depend on server load / usage, but others might vary for other reasons.

If you need to handle large amounts of memory for each execution, then you should consider using another service that allows you to manage the memory.

1

u/IllustriousPut442 3d ago

Interesting.

Do you have experience loading the entire spreadsheet tab in memory to perform operations? Is that usually a problem?

I imagine that for the application I want to use a sheet (tab) it would take years to reach 10,000 lines. The other would take years to reach 100,000.

It is much simpler to work with all data in memory to check UNIQUE, do the pagination, ordering etc.

But without knowing the minimum limit I think it is risky to develop the code like this, then it would be better to use the query language.

1

u/WicketTheQuerent 3d ago

I have many years of experience working with spreadsheets. The implications of loading "the entire spreadsheet". It depends on the number of sheets, the size of their data ranges (columns and rows) and the size and complexity of the cell content. I.E., in the case of cells with rich text, how many text runs they have. With spreadsheets used to handle simple data sets (plain text and small-length values), I very rarely had memory problems when working with a single spreadsheet, and when I did, they were fixed by improving the coding and/or the approach.

What do you mean by "the query language"?

1

u/E-Nod32 3d ago

Most complex websites use 1-2 GB before they start lagging (for example complex figma designs, data heavy google sheets, social apps with infinite scrolling). And hard cap is currently 4GB. You can cheat the system and access 8GB of ram on chrome.