Workflow - Not running when sheet is not open

Ana Guilherme

Hello,

I'm using the JIRA connector and when a sprint is closed and another one is opened, I'm tracking that change on a smartsheet sheet.

I would like to have a workflow to perform a few actions upon that change when that change occurs. The sprint does not start always at the same or at the same time of the day, that's why I would like to track when that happens to take an action.

However the workflow is not running when the sheet is not open. Is there any workaround to have this working?

Thank you in advance.

Paul Newcome

Are you able to provide screenshots of both the sheet and the automation?

Ana Guilherme

I have that column Sprint Position that changes when I have a new JIRA complete date.

When sprint is closed will fill that row 17 with the final date of the sprint (pulled from the JIRA connector), and the sprint position column will adjust. Previous will be Sprint 17, Current will be Sprint 18 and Next will be Sprint 19.

When this occurs I would like to save the status of other columns I have in the sheet, but yesterday the sprint closed and has I wasn't with this sheet opened the cells were not copied to a new sheet.

If it's necessary more information, let me know @Paul Newcome. And thank you (as always) for the support

Paul Newcome

What formula are you using to populate the Sprint Position column? Are you using the TODAY function?

Ana Guilherme

I'm not using TODAY in that formula.

=IF([Hidden Sprint ID]@row = COUNT([Hidden Sprint JIRA Complete Date]:[Hidden Sprint JIRA Complete Date]), "Current", IF([Hidden Sprint ID]@row = (COUNT([Hidden Sprint JIRA Complete Date]:[Hidden Sprint JIRA Complete Date])) - 1, "Previous", IF([Hidden Sprint ID]@row = (COUNT([Hidden Sprint JIRA Complete Date]:[Hidden Sprint JIRA Complete Date]) + 1), "Next", "")))

the workflow is also based on a value (sprint position) that is not a vlookup has I know that triggered workflows do not work properly with vlookups

Thank you for the support

Paul Newcome

How is the [Hidden Sprint JIRA Complete Date] column being populated?

Ana Guilherme

Indeed with an Index Match

=INDEX({Sprint Complete Date}, MATCH([Hidden Sprint Name]@row, {Sprint Name}, 0))

Paul Newcome

Are new rows being added regularly? It seems to me that using your formula in the [Sprint Position] column, that would never move unless new rows are being added or rows are being deleted regularly. Can you describe the overall process?

Ana Guilherme

The Sprint Position only moves when there is a new item on the [Hidden Sprint JIRA Complete Date], this item is pulled from a sheet where the JIRA connector stores the information from JIRA fields I have selected.

The overall process should be, if a sprint is closed in JIRA, then in the sheet JIRA database (smartsheet) I know that that field is filled with this formula:

=INDEX({Sprint Complete Date}, MATCH([Hidden Sprint Name]@row, {Sprint Name}, 0))

{Sprint Complete Date} is the date from the completed sprint in JIRA filled in the database, based on the sprint name [Hidden Sprint Name]

the cell in the [Hidden Sprint JIRA Complete Date] is then filled when the sprint is closed

Those are the vlookups I have to fill the [Sprint Position] column

I should only have that column [Hidden Sprint JIRA Complete Date], being added with new items every two weeks

Ana Guilherme

Also to add:

I would like to have this process, as I need to save the first instance of data (JIRA data) when the sprint is initiated, therefore it's not usual to use workflows to run at a given fixed hour, because a sprint can be closed at any time during the sprint closure date (that also might vary)

Paul Newcome

Since your trigger is (in a round about kind of way) referencing a cross sheet reference, it will not work (which is what you are running into now). You will have to set the trigger to run daily and include the sprint position as a condition. You can set up multiple workflows to run daily all at different times to get it to appear to run hourly if needed, but unfortunately that is going to be the only way to accomplish what you are wanting.

I would suggest including a new checkbox column (can be hidden after setting up) with a cross sheet formula that looks for that sprint/position combo on the sheet you are copying rows to that automatically checks the box if that sprint/position has already been copied then include a condition of the box being unchecked. This will keep the same rows from being copied over multiple times even when there is no change.

Ana Guilherme

Thank you Paul, that makes sense why is not working!

I will apply that solution! its feasible! thank you so much!

Paul Newcome

Happy to help. 👍️