I haven’t been able to find anything about this when I search but I don’t think I’m the first to have this problem.
I have a Glide App that uses a Google Sheet to display a number that changes every day. The number is calculated automatically (and correctly) by the Google sheet but the Glide App won’t update the number (it will display yesterdays number) until the Google Sheet is opened by me. Or in other words, it looks like the Google Sheet doesn’t perform any calculations until you open it. Which makes sense.
I can trick it and get it to work by having a button in the App (I call it “Wake Up”) that writes a value to a cell in the Google Sheet. If I press the button, the number will update after a few seconds without me opening the Google Sheet.
I would rather not use the Google Sheet but I haven’t been able to get the formula to work in a Glide Table… but that’s another problem for another time. lol
What I want to know is, is there a better way to wake up the Google Sheet or is there a way to automatically write a number to a cell in a Google Sheet everyday at a certain time to get it to perform the calculations without me pressing the button?
I’m up for the challenge - what should the output look like, and how does the progression of days affect that?
Short answer is no, I’m afraid. At least not easily. What you might be able to try is have something like Make change a value in your Google Sheet at a scheduled time each day. That might work.
I thought you might say that. lol
Thanks very much for giving it a shot… or at least looking at it.
This may be a bit long but I tried to break it down as much as I could to help you out.
I have a task that needs to be done every workday by a team of two people. The task is not performed on weekends or holidays.
I have a group of people, at the moment they number 34 but it could be any even number. I split them up into teams, always two people per team, so 17 teams. I need each team to perform the task once before starting the order again and I would like the order to be consistent each time. Team 1,2,3,…16,17,1,2,3… and so on.
I need to display todays team member’s names on the main page each day in the morning before the start of the shift.
At the moment:
I have all of the people on separate rows in the Users table in Glide linked to the Google Sheet.
I have First Names, Last Names, and Full Names in three separate columns and other data as well (not really important but…).
I have a manually assigned Team Number (1 to 17 twice) in another column.
I have a Calculations table in Glide linked to the Calculations in the Google Sheet.
On the Main Page table in Glide I use a single value lookup to get todays team number from the Calculations table. I use a relation to get the peoples names that match todays team number. From there I have a few IF statements and Template Statements to display the people’s names on workdays and no names on Saturdays, Sundays and Holidays.
I’ve tried doing this just using Glide a few times (in different ways) but I kept running into a problem when using Round, Ceiling, Truncate, or Floor on the Users table. I would only get results (TodaysTeamNumber) for a couple of days a week. The rest of the days were blank. If I didn’t have to worry about weekends or holidays this would probably be easy.
At the moment in Google Sheets:
I use Named Ranges for some of the variables.
The holidays I am using:
2023-01-02
2023-02-20
2023-04-07
2023-05-22
2023-06-30
2023-08-07
2023-09-04
2023-10-09
2023-11-10
2023-12-25
2023-12-26
Getting the formula to work is more important than including the holidays so if you want to leave them out for now to make it easier, that’s okay.
The Holidays I am using are actually on a separate sheet but the following should work.
A2 = StartDate - 2023-01-09 - a fixed date
B2 = TodayDate - todays date - a variable
C2:C12 = Holidays - a column of 10 dates
D2 = ExactWeekdays - the number of weekdays in a week not including Saturdays, Sundays, and Holidays - calculated
E2 = Teams - the number of teams of 2 people - 17 at the moment but could be any number, counted from the number of people in the Users Sheet
F2 = DaysByTeams - the exact number of times all teams have performed the task since StartDate
G2 = FloorOfDaysByTeams - the exact (whole… no decimal) number of times all teams have performed the task since StartDate
H2 = ZeroFix - the current team number displayed as a decimal and getting rid of the 0 in any calculation by changing it to a 1
There may be an easier way to do this but this is the solution I came up with and it seems to work with Excel and Google Sheets but not with Glide.
I don’t have the Glide version of the formula anymore. I deleted it after I gave up. lol I can probably recreate it if you need me to but it’s pretty much the same as above with a few more calculations to get the exact “number of days worked” since the StartDate.
I’m not entirely sure I know what you mean by “fetch the values”. I searched the Documentation for “fetch” and it replies with “Experimental Code”.
I should also mention that I’m using “Normal” Sync Mode. I haven’t tried it but I think if I switch to “Extra” I’ll probably burn through my syncs in a short period of time.
This should be a link to a stripped down version of the original. I think it has everything that’s needed. I can probably make a stripped down copy of the Glide App too if you need/want it, although I’ve never done that so it might take more than a few minutes.
Cool, I think I can work with that. I’m out today, but I should be able to have a play with it later tonight or tomorrow.
No need for a copy of the App, but a few screen shots would be useful to provide some additional context.
Wow Darren! Thanks very much for all your trouble. You are a genius but that must have taken more than a few minutes.
Me and my crazy calculations. Haha
I figured there would be an easier way to get the number I was after but it’s been 45 years or so since I was in a math class and I honestly only remember the basic stuff. I do remember the pretty girl that sat next to me though.
I’ll give this a go later on today and let you know what happens.
Well, it actually took about 15 mins to study your sheet and figure out what was going on, and then about 10mins to build it out. But, this is a technique I’ve used many times, so I didn’t have to think about it too much
btw… another way to do this would be with a single JavaScript column - which could replace that whole Helper Table - but I tend to avoid JavaScript unless it’s really necessary.
