CODEWITHSUNDEEP
if-statementgoogle-sheetsspreadsheetgoogle-sheets-formulaarray-formulas
MBearnstein37
MBearnstein37

Reputation: 75

Optimizing / speeding up calculation time in Google Sheets

I have asked a few questions related to this personal project of mine already on this platform, and this should be the last one since I am so close to finishing. Below is the link to a mock example spreadsheet I've created, which mimics what my actual project does but it contains less sensitive information and is also smaller in size.

Mock Spreadsheet

Basic rundown of the spreadsheet:

  1. Pulls data from a master schedule which is controlled/edited by another party into the Master Schedule tab.

  2. In the columns adjacent to the imported data, an array formula expands the master schedule by classroom in case some of the time slots designate multiple rooms. Additional formulas adjust the date, start time, and end time to be capped within the current day's 24-hour period. The start time of each class is also made to be an hour earlier.

  3. In the Room Schedule tab, an hourly calendar is created based on the room number in the first column, and only corresponds to the current day.

I have tested the spreadsheet extensively with multiple scenarios, and I'm happy with how everything works except for the calculation time. I figured the two volatile functions I use would take some processing time just by themselves, and I certainly didn't expect this to be lightning-fast especially without using a script, but the project that I am actually implementing this method for is much larger and takes a very long time to update. The purpose of this spreadsheet is to allow users to find an open room and "reserve" it by clicking the checkbox next to it (which will consequently color the entire row red) allowing everyone else to know that it is now taken.

I'd like to know if there is any way to optimize / speed up my spreadsheet, or to not update it every time a checkbox is clicked and instead update it "manually", similar to what OP is asking here. I am not familiar with Apps Script nor am I well-versed in writing code overall, but I am willing to learn - I just need a push in the right direction since I am going into this blind. I know the number of formulas in the Room Schedule tab is probably working against me yet I am so close to what I wanted the final product to be, so any help or insight is greatly appreciated!

Feel free to ask any questions if I didn't explain this well enough.

Upvotes: 0

Views: 418

Answers (1)

player0
player0

Reputation: 1

to speed up things you should avoid usage of the same formulae per each row and make use of arrayformulas. for example:

=IF(AND(TEXT(K3,"m/d")<>$A$1,(M3-L3)<0),K3+1,K3+0)

=ARRAYFORMULA(IF(K3:K<>"", 
 IF((TEXT(K3:K, "m/d")<>$A$1)*((M3:M-L3:L)<0), K3:K+1, K3:K+0), ))

=IF(AND(TEXT(K3,"m/d")=$A$1,(M3-L3)<0),TIMEVALUE("11:59:59 PM"),M3+0)

=ARRAYFORMULA(IF(K3:K<>"", 
 IF((TEXT(K3,"m/d")=$A$1)*((M3-L3)<0), TIMEVALUE("11:59:59 PM"), M3:M+0), ))

0

Upvotes: 1

Related Questions