0

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.

player0
  • 124,011
  • 12
  • 67
  • 124
MBearnstein37
  • 75
  • 8
  • How can we access the code of the script you are running? Is it entirely functions in the cells of the spreadsheet? What are the important bits to look for? – jered Oct 11 '19 at 00:17
  • There is no script, only functions, although if I knew how to write a script I would want to since I think it would be better suited for what I'm doing. The most important parts are the formulas in the Room Schedule tab. All of them are the same except for differing cell references. Plus cell A3 in the Master Schedule tab I believe uses the NOW() function which I'm sure takes a bit to recalculate every time a checkbox is clicked in the Room Schedule tab. – MBearnstein37 Oct 11 '19 at 01:04
  • Have a look at this excellent write up: https://www.benlcollins.com/spreadsheets/slow-google-sheets/ It will tell you to try to avoid volatile functions, such as `now()`, plus provide lots of tips. – a-burge Oct 11 '19 at 09:16
  • Looking at your spreadsheet, I would guess that the conditional formatting is slowing it down severely. – a-burge Oct 11 '19 at 09:22
  • Surprisingly, the conditional formatting barely makes an impact. I just tested a few scenarios: My full actual spreadsheet takes 4:45 to populate upon opening or refreshing, and 1:12 to update for each checkbox that is ticked in the calendar view. Removing all conditional formatting speeds it up by 10 seconds and the checkbox time is unchanged. Removing all the formulas from the hourly calendar view allows the spreadsheet to populate upon opening in only 18 seconds, which lets me know that the volatile `NOW()` function is not a big deal. – MBearnstein37 Oct 11 '19 at 22:38
  • If I can somehow speed up the hourly calendar portion, I'll be golden. I don't know if implementing it via a script would help with performance, and maybe someone could shed some light on that. If I don't go the script route, which I'd have no idea where to start but would love some advice it it seems like a viable option, the only other option I can think of is "freezing" the values until the user manually updates the view, either with a button that I attach to a script or with a pulldown menu. That way all of the checkboxes should be processed at the same time instead of one after the other. – MBearnstein37 Oct 11 '19 at 22:43

1 Answers1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you, player0. I will implement that right now, although that is not where the slowdown appears to be stemming from. As I stated in my comment above, those formulae take less than 20 seconds to recalculate which I am more than okay with. It is all of the cells in the **_Room Schedule_** tab that take a long time, regardless if there is conditional formatting applied to them or not. – MBearnstein37 Oct 12 '19 at 01:12
  • Actually, the formula you provided just repeats the first calculated value in every cell of the array. – MBearnstein37 Oct 12 '19 at 01:55
  • Ah, I see my error. I figured it was on my end. Do you think using an array formula in the calendar tab, if it's even possible, would help speed that up? That's where my main problem lies. – MBearnstein37 Oct 12 '19 at 14:23
  • I would dare to say that your most nasty formula is in Master Schedule!A3 which has the major impact of the sheet performance – player0 Oct 12 '19 at 21:02
  • That's what I thought too until I isolated each section of the spreadsheet. Importing the data only takes about 10-15 seconds, and even if I paste values instead of importing them with a formula, the calendar tab still takes a very long time to populate. I realize that there will be some delay since every cell in the calendar tab contains it's own formula, so I was just curious if anyone knew how to optimize it or stave off recalculation until whoever is using the sheet chooses to do so manually. – MBearnstein37 Oct 13 '19 at 19:22