Where do google sheet calculations occur?

My app has a complicated set of queries, index/filters, and arrayformulas to calculate invoices for student lessons and process if they are paid or not. I observed one of my users rapid fire entering 11 lessons into the app. As soon as the records started coming into the sheet, the calculations started. It took about 10 minutes for the sheet to pull in 10 of the lessons and one did not make it at all. I watched the sheet sit and spin through calculations for about 20 minutes before it finished. I will need to go through all my formulas and try to make them more efficient. Not sure where to start since I have no way to debug the sheet and see where the hangup is. I guess I have some work to do.

This brings me to my question. Where do calculations take place in a google sheet? Some stuff I read online alluded to the fact that my computer handles calculations. Is it true that the computer will take the load off of google servers if the sheet is open? Would I have better results by keeping the sheet closed and allowing the calculations to occur on google servers? Just wondering if anybody has any deep level understanding of how google sheets handles the processing of calculations with and without the sheet open on my computer.

1 Like

The information and link here look like interesting reading.

@George_B I think I found that same link earlier. Seems like there are 2 opinions in the answers. The first one says that it uses local resources where the second answer says that it’s server side. I could totally see google offloading as much as it could. How do you think it works in your personal opinion? If calculations are happening locally, then do you think it’s fair that google servers take over when the sheet is closed? If so, then processing may very well happen much faster. I just happen to keep the sheet open often to check things for reference. I’ve notice the fan in my computer spinning up on occasion, but I also have around 75 tabs open too :wink: . Usually chrome is the culprit, but I haven’t taken the time trace down what’s causing it.

In either case, my best bet is to work on restructuring my formulas to make them more efficient. Do you have any opinions on speed using query vs index/filter? I have an initial suspicion that index/filter is a culprit. I have a sheet that is built off of a query. Then I use Index/Filter in 3 columns to get additional values, along with additional queries in a few more columns. Any time one lesson is added, this entire sheet rebuilds. After the sheet rebuilds, I load it into another sheet using unique to compress the info down. It’s very convoluted, but the only user that uses this portion of the app has given over 1200 lessons in just over 6 months, so it’s only gonna get worse. My other thought is to completely offload the invoicing and billing calcs into a separate spreadsheet, then use importrange and scripting to move data back and forth. This would at least fix the data transfer lag between Glide and Google and prevent lost data.

Wish me Luck!!!

Yeah good luck. I don’t have anything that complex and both my computers are pretty fast. It seems obvious that if your computer is turned off and an update to the sheet is made by Glide or by running a timed script, it has to use Google servers to do the spreadsheet calc. However it can’t hurt to look into what may be slowing things down. I’m sure you have mastered Google searching to get info about optimizing but here is a page I found anyway. https://www.benlcollins.com/spreadsheets/slow-google-sheets/ . This guy is a true sheets guru, makes me look like a novice.

1 Like

Thank you sir!

Hey Jeff,

In my experience with the biggest sheet I’ve built for a client, when I have it actively open in a tab, the calculations take longer than when it’s closed. So my best guess is that Google server side is faster.

Also another tip, in my experience as regards resource intensity:
Index/filter > query > pivot tables.

See if you can use pivot table for some data “massaging” and if that helps with performance.

One last thing, for all ranges in your formulas that don’t have to be max.range but can be fixed.range, try to use fixed.range instead.
Ex: VLOOKUP(A:A, Sheet2!A:B, 2, 0) where the search_key is only in A2, and the actual range is always A2:B100.
This might help aswell.

Good luck with the task ahead and please keep us updated as to your findings!

1 Like

@Karim Thank you for your response. It’s good to see that someone else has experienced a difference with the sheet open and closed. I’ll look into your suggestions for improvements.

For my fixes so far, I have two columns that were using queries to get dates. These dates were just a copy of another column that had the same two dates that were concated together. I switched the queries to just use an arrayformula query and split to obtain each individual date. I also had three columns that were also using queries, but I switched them to SUMIFS. That seamed to help a lot. I’m still fighting with queries and sumifs that need the formula copied down instead of using arrayformula. I need to come up with a better solution there. Currently my sheet is processing queries and sums on about 1000 rows. I have the formulas copied down to 2000 rows as it will grow over time. It’s a little better, but will probably take some rethinking of my structure to make it more efficient.

Again, Thanks for your help.

1 Like

I went through the link by Ben Collins that @George_B posted: it’s top notch content.
All the tricks I was using and so much more.

I bookmarked it for future use!

1 Like