Workflow automation - copy to another sheet sheet.

BullandKhmer

OK, so I've set up a task list sheet in Smartsheet. I've also set up a separate sheet called routines. The idea was that through automation (copy row), when a routine task was due to be completed it would automatically (and repeatitively) be added to my task list.

The routines sheet looks like this (task and assigned to columns redacted):

smartsheet screenshot 1.png

On this sheet the start date auto updates through a formula - updating each day at midnight for daily tasks, at midnight once a week for weekly tasks etc.

I have added an automated workflow, which looks like this:

Smartsheet Chat 2.png

Where once the start date for a row is reached the row should copy to the task list. But it doesn't, at least not repetitively, it seems to copy only once, i.e. the first time I run the automation.

So what am I doing wrong?

Andrée Starå

Hi @BullandKhmer

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BullandKhmer

I've copied screenshots of the relevant information into my original message Andree.

Paul Newcome

It may be that the TODAY function in your formula is not updating (as expected) until the sheet is activated. Insert a date type column and set up a Record A Date automation to run daily in this new column based on any column of your choosing not being blank. This will "force" the TODAY function in any formula to update which in turn should update the dates which should trigger the copy row automation.

BullandKhmer

@Paul Newcome

That's pretty smart and insightful. In essence you are saying that unless I have Smartsheet open the TODAY function may not activate and therefore the rows dont transfer as per automation.

But if I set up a 'record a date' automation in a helper column this might force the today function to operate. OK, i'll try it.

Thanks, will report back on if this works.

Paul Newcome

Essentially yes.

A little more detail:

The TODAY function only updates when the sheet has been "activated". This could be from a form submission, some type of automation making a change (record a date solution), update request, approval request, and of course the sheet itself actually being opened.

Since the sheet is not activated "today", then those dates could still be yesterday's date. When your daily automation runs, it finds no rows with today's date in it so it finds no row to copy over.

The Record A Date automation "activates" the sheet which in turn updates the dates, so as long as the Record A Date runs BEFORE the Copy Row then you should be good to go.

BullandKhmer

Hi All & @Paul Newcome

Nice idea Paul. Sadly, I tested it overnight and it didn't work.

I'm kinda lost now... if anyone has any ideas on what's going on here and what I could do different it would sure be appreciated.

I was thinking that I could try replace all TODAY() in my formula with {Record Date@row} reference. Thoughts on the likeliness of success here?

I've copied in full details of my work below.

• Formulas are updating to the correct date in the routines sheet

• I have a helper column for record date.

• All automation appears correct to my eye

Again, any ideas would be appreciated.

Cheers,

Adam.

Andrée Starå

@BullandKhmer

This might help.

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BullandKhmer

@Andrée Starå this is basically spam.

You're repeating a suggestion already made and implemented. Its not even clear you have read through the post.

Do you have any original suggestions?

Thanks.

Adam.

Andrée Starå

@BullandKhmer

Ok?

I just wanted to give you another option to help you get it working.

It seems like I misread the questions, and it didn't help.

Genevieve P.

Hi @BullandKhmer

I believe this may have to do with your Timezone - I can see in your workflow that the Timezone is set to Pacific/Auckland.

The TODAY() function is currently based in pacific time, which is why it may not be updating when you need it to... however automations will trigger at the time that you set it to, based on your timezone.

Because of this, I agree that if you reference the date that the workflow inputs into the sheet in your formula, this should likely help resolve the issue! You'd want to reference the cell [like this]@row though, not in a {cross sheet reference}.

Cheers,

Genevieve

Paul Newcome

@Genevieve P. I was thinking the same thing when I saw the triggers. Scrolled down a little further and saw that you had confirmed. Thanks!

@BullandKhmer The only "catch" to this is you are going to want to add another that records the date when new rows are added (triggered immediately) as well. If you add a new row with only the current record a date automation in place, you will not have anything in that date column until the next day/cycle. Even after adding another workflow for when a row is added, your formulas will still not be accurate until you save the sheet and trigger this second record a date.

BullandKhmer

@Genevieve P. @Paul Newcome you guys are awesome. Thanks, ill run a test tonight.

Paul Newcome

Happy to help. 👍️