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

I just wanted to update on what I have been doing to speed up my app. As I mentioned in the first post, I have several queries, vlookups and other formulas that were causing my sheet to spend several minutes recalculating and processing the new data. I had since restructured some of my sheets and formulas to reduce the amount of records that needed to be processed. This definitely helped, but there were still issues with multiple inputs coming from Glide while the sheet was still calculating. This would occasionally cause new records to disappear. I think this was due to google not accepting new updates from glide due to it being in the middle of processing. In the meantime, Google was sending data back to Glide and since the google sheet didn’t contain the newest data yet, some data just got lost somewhere.

With all of that, I had the idea to create a second google sheet to handle the heavy back-end processing. I started by creating a new spreadsheet separate from the spreadsheet that’s connected to Glide. Using several ImportRange formulas, I am pulling the necessary sheet tabs (that are needed for the calculations), from the original spreadsheet into the new spreadsheet. I then perform all of the calculations that are necessary in the new back-end sheet. I’m not concerned about the processing time, so it can take as long as it needs to without affecting the availability of the original sheet to accept changes coming from Glide. I then have an ImportRange in the original sheet to pull back the calculated data from the new sheet to the original sheet.

To describe a little bit what what the app looks like when this is happening, here is a screen where the user can enter their lessons for the day using the Add Lesson form button. Once they are done, they can tap on Invoices to view invoices for billing the students.

Once in the Invoices screen, the user can set up Billing Cycles. This allows them to set a date range for the bill that they want to send to a student.

When you open up one of the billing cycles, it will list the total amount due from all students…for that range of dates. Picking a student will further allow the user to view an invoice for each student for that date range. As you notice, I have an ‘* As of:’ section below the list of students. What I am doing here is creating a column that joins the max date and record count from the Lessons table in the original sheet and compares it to the max date and record count from the Lessons table in the new backend sheet.

View when selecting a student.

When the comparison of last date and count match on the Lessons sheets in both the original and the backend sheet, then I display the max date of the last lesson that was entered. This value indicates that the all of the data from the original sheet has made it to the backend sheet, finished processing, and made it’s way back to the original sheet. Everything displayed in the app should be current and calculated as of the last lesson that was entered.

I am still experimenting if I need to use the Pro feature that automatically reloads the sheet every few minutes. It seems fine to only reload the sheet on edit as google will eventually push updates back to Glide. I think the only advantage might be slightly quicker updates. I don’t need immediate updates as I know the processing takes time. At least I have a way to indicate to the user that invoices are currently processing or have finished processing. I’m still experimenting, but it seems to be working.

I hope the back-end sheet idea might be useful to any of you someday.