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.
. Usually chrome is the culprit, but I havenāt taken the time trace down whatās causing it.
