Not sure if it’s better than your approach, but I usually have a time sheet specifically for cases like this. I have a start week date and end week date with multiple weeks filled in advance, then an arrayformula to determine if the row is “current week”, all in the sheet.
Then in Glide, I use lookup and relation to bring over the current week’s start and end dates. If the timestamp is before start of the current week or after end of the current week then False, else True.
That’s a neat idea! Does this use a Today() formula, and if so how do you get Glide to update at midnight? For me, sometimes it does, sometimes not…that’s why I’m trying to get it all in GDE.
I would go with what Joe suggested to you above, use a script to insert some random value somewhere in your sheet. That will force an update, I can confirm.
Thanks Thinh, I will say it has always been a tried and true result for me but Thinh’s idea sounds interesting and may be faster.
Yes, I will definitely do that if this GDE method doesn’t cover all the bases (reduce delay, update at midnight). Will update once I test at midnight
This method worked, but I would have to do this across my entire app. I realized I’m triggering a script to write data to the file every night anyways, so that should update the google sheet around midnight.
Now you should be able to create a math column and calculate weeknum(today) - weeknum(date) and if it’s zero, then it’s ‘this week’. All within Glide. No google sheet formulas or scripts.
Oh wow, I didn’t know they released weeknum! Very exciting!
I’m guessing they added it especially for you!