I have a number of really large spreadsheets I have to update weekly ALL on Google Sheets.
This data is then used in multiple other spreadsheets through ImportRange functions.
My issue is updating these ‘master sheets’. When I have a single one of these spreadsheets open in Safari, they normally take up anywhere from 4-6GB of RAM - updating them is tedious and slow and prone to becoming ‘unresponsive’.
From a google sheets perspective I’ve limited any of the typical functions that slow down the sheets (i.e. today functions, array formulas etc/.).
Any ideas on how to potentially reduce the strain on RAM with these spreadsheets?
I had the same issue previously. I got rid of all formulas, especially lookups and custom scripts. Ultimately even that wasn’t enough and I moved everything to a database. Excel also handled it better than Google Sheets.
25+ years ago an accountant asked me for some help. She had 13 linked Excel spreadsheets (one for each month and a summary sheet) with something like 300,000 + active cells. Changing one number caused a several second delay. She could really have used an M1
Long story short I turned off automatic calculation and set it to calculate on Save. (Which normally took more than an hour)
It appears you can make a similar adjustment in Google Sheets. Might be worth a try.
I’ll second the vote for converting to a database, or using something like Python.
The first time I moved a too-large spreadsheet into a database, it was eye opening how much faster it was. Same with the first time I moved one to Python. (In Python, the gold standard for this kind of thing is probably Pandas. But if you want a more spreadsheet-like approach, check out the agate library. But with both, you have to get used to manipulating data without necessarily seeing all of it. On the plus side, it’s a lot harder to fat-finger mistakes directly into your data!)
I would guess that some of the business-oriented spreadsheets alternatives out there could be tailor made for this kind of use – Airtable and the like – but I have no experience with them.
I would also suggest VisiData, a CLI tabular data tool written in Python.
@Danny_Pettiona I’m not sure how many Sheets-specific features you depend on, but I bet a local spreadsheet app like Excel or maybe Numbers would perform faster than Google Sheets running in a browser.
In meantime, try using a Chromium-based browser for Sheets, such as Edge. While I primarily use Safari, I have noticed it does not handle large JavaScript apps well. The “significant memory” messages + forced reloading makes it unusable, so I am forced to use Edge for serious work in web apps.
I’m actually working with a fair bit of data in Zoho Analytics at the moment which uses SQL. Bit of getting used to, but I suspect moving everything to a system like this (as most have suggested) would curb some of these problems!
I’ll try this Wayne thanks for the idea
Also an idea for me try, given I actually use Edge anyway (just not for this google account!) Thanks Ikhrs
If you are using your “master” spreadsheets primarily to hold rows is data that filtered and reduced in some way for subsequent use in a further sheet, I really suggest using a database; they are designed for that type of processing. For example, selecting rows of data typically requires a spreadsheet to scan every row, whereas a database can (given a properly designed scheme) maintain an index that directly identifies the rows meeting the filter criteria. Very, very much faster.
If you don’t want to learn SQL (the language used by most databases), or a programming language like Python, you might take a look at Panorama X, which is effectively a database engine with a spreadsheet front end and lots of good reporting/ presentation tools. It’s currently on sale.
Agree learning a programming language (sql / Python / r) would be good.
In the short term, you could try something like a PowerPivot (excel, not sure what Google has) - the pivot will be read from an external file, keeping your main spreadsheet small and speedy - just a one-off refresh each time will take time.
