Automation - Send Email based on date in a table

[Nathan Unsworth]

I have a table with a list of contract review dates.  I want to setup an automation to review the page every day and send an email to the tagged individual on the row if review date=TODAY.  If I were to write the table in Markdown, it would look like this:

|App Name|Next Review Date|App Owner|

|MyApp|3/31/2024|@person|

The date element is used in the Next Review Date field, and the App Owner has been tagged.

How do I setup the automation to key off of that review date?

[Stefan Salzl]

Hi @Nathan Unsworth  - welcome to the community,

does this table already exist? Is this a common table within a confluence page?

Best
Stefan

[Nathan Unsworth]

Yes, it's a table that does exist using the standard Table element in confluence.

[Stefan Salzl]

Further question:

Are those lines from the table (contracts) coming from jira tickets? Or is this table filled manually?

If filled manually personally I don‘t an easy/straight forward way to iterste over each line and filter out the review date.

Is automation4jira the only possibility or dou you have scriptrunner installed?

With automation i guess it would still need some more (and also kind of scripting) effort.

Didn‘t verify this but what I could imagine is to send a webrequest against the confluence page to get the content and then iterste through each line of the table and compare the date. (unfortunately I don‘t know exactly if and how this can be done)

@Bill Sheboy  may I ask if you have any further or even other idea?

Best
Stefan

[Nathan Unsworth]

I setup an automation to look at the entire page and if there is a task with a due date past due, send an email to the defined group.  It works well, just not as focused as I'd like.

[Bill Sheboy]

Hi @Stefan Salzl and @Nathan Unsworth 

Well done finding a workaround, Nathan!

Stefan, I believe searching / filtering the table requires:

• grabbing the table content,
• creating a dynamic regular expression,
• splitting the tables rows and using match() with the dynamic regEx, and
• parsing the results to get the App Owner.

Coincidentally, I am writing an automation article on this solution pattern as I have seen it asked more and more lately :^)

Kind regards,
Bill

[Stefan Salzl]

Hey @Nathan Unsworth 

great to know you found a solution.

Would you be so kind to share your automation rule here? I would love to understand the details and it might also help other users. post it as a separate answer so it can be accepted and the question be marked as solved.

Best
Stefan

[Nathan Unsworth]

There's one more issue I'm trying to solve:  how do I sent one email per page?  There are many contracts expiring at the same time and I don't want it to flood our inboxes.

[Stefan Salzl]

Hi @Nathan Unsworth 

without knowing your rule in detail it sounds like a "for each" branch could solve this. So if you have a list of pages that should be worked on you could collect those pages and do the tasks (searching for dued tasks and send an email) for each page.

Would be easier to help if we see the details of your rule.

Best
Stefan

[Nathan Unsworth]