Automation - Lock and Unlock rows (force TODAY() update)

Jimmy_B

Hi community

I have added in an automation on multiple sheets that should, on the first of the month, lock and then unlock all the rows on the sheet. This is to force all formulas utilising TODAY() to update.

On viewing some sheets today I have noticed that many have locked rows. I have manually run the automation and it only does one action or the other i.e. lock if the rows are unlocked and unlock if they are locked. I have created the automation as per the advice within the Help and Learning pages (although I found this workaround in the community first) which can be found here - https://help.smartsheet.com/articles/2482767-automatically-update-today-in-formulas. I also attach a screen shot of my automation, maybe I have gone wrong but as it is so simple I'm not sure how.

Can anyone help please? Don't really want to try and set another automation to unlock an hour after locking, seems really excessive. (Ideally TODAY() would update without the need for this.)

Paul Newcome

You are going to need to set up a second automation that unlocks an hour later if you want to keep with the lock/unlock method.

I personally prefer to just insert a date type column and then set up a Record A Date automation to record the date in this new column daily at 12:00am.

Paul Newcome

"Just to confirm, on the Record a Date option, this will update all the formulas that use TODAY() in the sheet? I don't need to point at that date instead of using TODAY() do I?"

That is correct. You can maintain your TODAY references. You do not need to reference the [today's date] column.

Paul Newcome

You are going to need to set up a second automation that unlocks an hour later if you want to keep with the lock/unlock method.

I personally prefer to just insert a date type column and then set up a Record A Date automation to record the date in this new column daily at 12:00am.

Jimmy_B

https://community.smartsheet.com/discussion/comment/397870#Comment_397870

Thanks @Paul Newcome appreciate the reply. Unfortunately I have a number of sheets to apply it to but I think I may just suck it up and crack on. Short term pain for long term gain.

Just to confirm, on the Record a Date option, this will update all the formulas that use TODAY() in the sheet? I don't need to point at that date instead of using TODAY() do I?

I'll have a look at that help page from Smartsheet as to whether I can provide feedback as their advice is flawed based on my experience and your response/confirmation of my fears.

Paul Newcome

"Just to confirm, on the Record a Date option, this will update all the formulas that use TODAY() in the sheet? I don't need to point at that date instead of using TODAY() do I?"

That is correct. You can maintain your TODAY references. You do not need to reference the [today's date] column.

fhill

@Paul Newcome

I set up the today() refresh with a 'Records a Date' column, however I still can't seem to get this working. Any ideas? I confirmed the automation is running successfully at 4am, but I still have to open the Sheets every morning for them to refresh? Notes / Screenshots below...

• SC1 - Portal View of the Report this morning and still has last weeks data
• SC2 - Not until I OPEN the Sheet do the values refresh (and then the Portal updates afterwards)
• SC3 - Last screen shot is the automation, that SmartSheets says is running successfully every day at 4am

Jimmy_B

https://community.smartsheet.com/discussion/comment/402208#Comment_402208

Hi @fhill

I have a condition in my automation to record the date where the date "Program Loigc_Ignore" is in the past (or blank). This is what is stated in the help link I shared initially and my associated reports are updating ok.

Hope this helps

James