I use this function, TODAY(), to calculate days to an event or past said event.

I've noticed that you have to open the sheet to update or probably more accurately get the TODAY function to be active and calculate.

I use this day counts in a Dashboard and to be accurate, as mentioned, I need to open that particular worksheet.

Is there a work-around where the TODAY function or another method where the worksheet doesnot need to be opened?




The sheet only needs to be made "active". It doesn't necessarily have to be "opened". You can use form entries and update requests to activate the sheet. I keep getting busy and forgetting to test whether or not Automations will "activate" the sheet. I need to remember to do that.


Otherwise... There is no real workaround for the TODAY() function.


I am not positive, but there may be a way to "ping" the sheet with a 3rd party app such as Zapier or through the API, but I am not completely sure.


In reply to by Paul Newcome

Zapier has a free plan, and it will be enough for the TODAY workflow. Another option could be to have a daily update request sent that you trigger each day.

Hope that helps!

In reply to by Andrée Starå

Thanks. I'll have to check it out. Unfortunately the update request option wouldn't work for me. I have already built out so much avoiding the TODAY() function, that going back through and taking advantage of being able to use it wouldn't be worth the effort.


My managers had me put together an almost fully automated (95%) workspace for them with all kinds of mess built in. I've been working on it for more than a year (almost 40 hours a week at that), and it still isn't done. I am most definitely not going to go back and do a major rewrite on 163 sheets, reports, and dashboards just for the convenience of the TODAY() function and update requests. Haha.


So... Zapier testing it is. yes


In reply to by Paul Newcome

Have you seen the Neverending Story? This sound like the Neverending Project! wink

163 sheets are nothing, and you can get that done over a weekend! cool

Zapier sounds like the better choice. yes

In reply to by Andrée Starå

Neverending Project is right! Just as I was wrapping up all of the little tweaks and links and tiny little details... BAM!


"Here's a few "minor" changes we need to make. How long will that take you?"


"Ummm... About 3 months..."


"But it's just removing a few lines and adding a few others!!"


"Those "few lines" are part of the single main driving factor of 90% of ALL automations within this entire workspace. That's why we spent so much time making sure these few lines were good to go, and why I said that any changes to these few lines would require a major overhaul."


"But it's just a few lines!"


"Those few lines automatically populate your project plan, which in turn is linked to the departmental rollup. That rollup is used for all of the YTD tracking you asked for which happens to be more than 3,000 points of data for just 6 people going from a very broad executive level dashboard all the way down to individual stats broken down by week, by person for each of the 16 different types of projects, tracking historical data, current data, and projections.

Not to mention the excessively detailed "build manual" you asked for (that's over 1,000 pages long and only 2/3 of the way done) that is going to need updated to the point of being almost an overhaul in and of itself.

So yes. 3 months is an optimistic time frame considering I still have the job I was hired to do that you want made a priority over getting all of this set up in SS."


"... Oh... ... ... ... Ok. Well just see what you can do."


It may not seem like a lot of pages to you, but I was hired to implement new clients. I had never even heard of SS until a little more than a year ago. Now I have SS 40 hours a week on top of my actual job.


At this point... It may be more efficient in the long run to integrate Zapier, and I may start doing that moving forward once my workload lightens up a bit. But right now "If it ain't broke, don't fix it." is going to be the main driving factor in letting the current sheets stay how they are. Haha


Hopefully I'll get a chance to play around in Zapier here in the next few weeks to see if I can make it work how I need it to. I've also been trying to study up on API's, but I swear I feel like I don't even know how to read. Haha



Sorry for the rant. 2 hours of sleep before coming into a mess at work has me a little irritable. Hahaha.

In reply to by Andrée Starå

No response yet. I haven't been able to pick up on a pattern either. Earlier a basic "Happy to help! yes" post was even blocked.


Haha. I once got blocked when I was trying to submit a support ticket for getting blocked. surprise

Whenever I get blocked, I have to change it up at least a little bit before it will go through.


123456 might get blocked and will keep getting blocked no matter how many times I try it, but 12345 6 will go through. It seems random. I still haven't heard back from them about it yet either. I'll definitely keep you posted.