How to get Automation Email to work

Andrew McCrae · 01/24/23

Hi,

Ive been trying to get an automated email to work since last September and had to give up back then, although thanks to Andree I made some progress and thought I had it fixed.

I enabled the automation for the first time this month after a couple of trials to myself and it doesn't work again. Smartsheet support are no help and I doubt from my experience that they will be.

Attached is what I am trying to do. Hopefully somebody can see where I am going wrong. I did change the method of calculating the cell that I look at, moving away from a complicated Excel style formula to something more intuitive but fail to see how that could influence it.

The Owners Column is setup as a Contact Column. This is an export as it's the only way I can see of sending a Smartsheet to somebody. I have redacted all the other recipients which are setup correct and we all receive emails from other automations in Smartsheet.

Calculate Dates of Risk Council Meetings.xlsx

SS Automation 1.JPG

SS Automation 2.JPG

Genevieve P. · 01/27/23

Hi @Andrew McCrae

Thank you for clarifying the sheet set-up! I understand the columns a little more, however we still need to know what you want the workflow to do and what exact formulas you're using.

Without knowing those details, these are my suggestions:

1. Update Column L Formula

Update Formula in Column L ("Sole Entry Days to Next Mtg") to ensure it's a number. This should help your Condition Block recognize that cell as a number, in case this is what's blocking the alert.

For example, if you're simply referencing the cell in Column K ("Meeting Intervals"), use a VALUE function around your formula to make sure it's being pulled as a number:

=VALUE([Meeting Intervals]@row)

If this hasn't helped, please copy/paste the Smartsheet formulas you're using in both Column K and Column L into your response so we can take a look

2. Check what row you're sending

If your Excel document is an accurate representation of your Smartsheet Sheet, then the formula in Column L ("Sole Entry Days to Next Mtg") is only in one cell (the Top Row).

This means your workflow is only ever going to send out the top row to the person listed in the "Owner" column for that one row.

Is this what you want it to do? Or is your Smartsheet set up differently, with more data in the L column?

3. Check who you want to receive the alert

If you do only want to send out the top row (using it as a constant alert email, versus for the contents of the row) review the following:

Ensure all of the people you want receiving the alert are listed in a multi-select Contact cell for that row
OR, change the Alert to send to Specific People and list out their contacts within the Alert itself

We'd be happy to help further if you wouldn't mind explaining what it is exactly you'd like the workflow to do!

Cheers,

Genevieve

Paul Newcome · 01/24/23

Are you able to provide a screenshot of the sheet with sensitive info redacted?

Andrew McCrae · 01/24/23

https://community.smartsheet.com/discussion/comment/358333#Comment_358333

Hi Paul, Thanks for replying. The Excel attachment illustrates the sheet exactly.

Genevieve P. · 01/26/23

Hi @Andrew McCrae

Can you clarify by writing out exactly what it is you're wanting to do with the workflow, and how you're getting there currently?

From what I can see, it looks like you have a Record a Date workflow set up to always bring in Today's Date on a daily basis. Then I presume you have a formula in the column "Sole Entry Days to Next Meeting" using Today's Date and another date so you can use that number as the condition in your workflow. Is that correct?

If so, what's the formula that's being used? It sounds like perhaps the output is not being recognized by your workflow as a number "7" which is why it's filtering out your rows and not sending.

Keep in mind that you also only have the number "7" in one cell / one row. This means that's the only row that would be included in your Alert, sending to the one Owner of the row. In your excel file, I can only see XXX as the "Owner" for the row with the number at the top.

If you want the sheet to read through ALL owners, then you'll want to set that "Sole Entry Days to Next Meeting" formula as a column formula instead, so the number appears on all rows. Does that make sense?

I do agree that seeing the actual sheet set up and formulas in your sheet in Smartsheet would be helpful, if you can block out sensitive data. 🙂

Cheers,

Genevieve

Andrew McCrae · 01/27/23

Thanks Genevieve

Ok. to go through the columns. As it exported to Excel, I'll refer to the Excel column Letter.

Columns A-I as you see are various Formula Columns that calculate a date based upon the criteria in Col. C. Column C has the Today(), Month(), Year() formulas in the Month and Year Cells in order to calculate This Month, Next Month, Last Month automatically. This year my meetings are the 4th Friday of every Month. Last year was 4th Wednesday.

Column I is the dates again calculated in these cells formaated as Date.

Column J not used. All of the above works perfectly for me.

Column K is Column I - today(). First Cell is the primary cell the sheet is used to create.

Column L references Column K cell 1 in order to remove any chance of noise from any other cells in the column.

Column M is created by the first workflow in order to refresh the sheet so that all the dates are updated. Not referenced anywhere else.

Column N not used anywhere (I use today()) in the formulas

Column O formatted as contact list with emails of regular smartsheet users including myself and a department colleague who are regular users also. I receive automated emails from another smartsheet when it updates so I dont believe any blocks are in place. I've redacted this list of people.

Hope this all makes sense.

Genevieve P. · 01/27/23

Hi @Andrew McCrae

Thank you for clarifying the sheet set-up! I understand the columns a little more, however we still need to know what you want the workflow to do and what exact formulas you're using.

Without knowing those details, these are my suggestions:

1. Update Column L Formula

Update Formula in Column L ("Sole Entry Days to Next Mtg") to ensure it's a number. This should help your Condition Block recognize that cell as a number, in case this is what's blocking the alert.

For example, if you're simply referencing the cell in Column K ("Meeting Intervals"), use a VALUE function around your formula to make sure it's being pulled as a number:

=VALUE([Meeting Intervals]@row)

If this hasn't helped, please copy/paste the Smartsheet formulas you're using in both Column K and Column L into your response so we can take a look

2. Check what row you're sending

If your Excel document is an accurate representation of your Smartsheet Sheet, then the formula in Column L ("Sole Entry Days to Next Mtg") is only in one cell (the Top Row).

This means your workflow is only ever going to send out the top row to the person listed in the "Owner" column for that one row.

Is this what you want it to do? Or is your Smartsheet set up differently, with more data in the L column?

3. Check who you want to receive the alert

If you do only want to send out the top row (using it as a constant alert email, versus for the contents of the row) review the following:

Ensure all of the people you want receiving the alert are listed in a multi-select Contact cell for that row
OR, change the Alert to send to Specific People and list out their contacts within the Alert itself

We'd be happy to help further if you wouldn't mind explaining what it is exactly you'd like the workflow to do!

Cheers,

Genevieve

Andrew McCrae · 01/27/23

Thanks Genevieve,

I'll review at my first opportunity. I greatly appreciate it.

Andrew

Andrew McCrae · 01/30/23

Hi Genevieve,

Thank you greatly for your help.

I don't believe this is the issue but put the Value converter in anyway.
I believe this was the issue and I now realise Smartsheet works on a row by row basis. I was only sending to the top row but erroneously thought that selecting the column in the workflow would send to the column.
This was the solution I followed by adding in all recipients to the top row cell using a multi-contact cell

Andrew

Genevieve P. · 01/30/23

Hey @Andrew McCrae

Adding in the VALUE function, even if it's not needed, can't hurt 🙂

I'm glad you found a solution!

How to get Automation Email to work

Best Answer

1. Update Column L Formula

2. Check what row you're sending

3. Check who you want to receive the alert

Answers

1. Update Column L Formula

2. Check what row you're sending

3. Check who you want to receive the alert

Categories