Use triggers from Smartsheet?

Udy Gold

Working on a new workflow.

Today's workflow is manual and based on emails. I'd like to use the webform to collect the required data (done) and then, based on specific conditions in the sheet fields trigger actions in other system - from generating email to open a service ticket etc.

I watched the movie of the docusign - very nice, but not applicable here since we do not have docusign to every employee.

Any basic templates available?

Travis

Hi Udy, What exactly are you trying to do? It may be possible it can be done with built in features. If Smartsheet cannot do what you are looking for, third party apps such as Zapier and Azuqua integrate with Smartsheet and work great for automation, workflows, and triggers. 

Udy Gold

Thank you. Here is the scenartio I try to accomplish:

As it is today, if I want to promote or give a raise (or bonus) to employee I fill in a form, give it to my boss to sign, to the HR, and to the CEO.

I can have a web form which I will enter the same information I enter to the paper form and it will resides as a record in smartsheet (or be an entire sheet for that purpose).

What I want is that once I filled in the information, the workflow will trigger my boss to an actiob (approve/reject) of my details. If my boss approves, it will automatically go to HR and then to the CEO.

I dont know how to trigger anything to get to my boss to review and approve that.

Was that clear or confusing?...

J. Craig Williams

Udy,

 

You could probably accomplish this using simple Alerts (Notification type).

 

The Notification can be set to send certain people an e-mail when certain things happpen. Your boss gets an email when the form is first submitted or a field only you change. To approve, the boss selects a checkbox "Approved by Level 1 Manager" or somesuch thing and that Alerts HR and so on.

If you need only either-or decisions, then two columns for the workflow ("Approved" or "Reason not approved") would send the alerts down to paths. More and I would consider looking into Zapier or Azuqua, but sounds like a few columns will be all you need.

It would also be a simple thing to track where it was in the pipeline (that is who has the next action) but harder to explain. 

 

Be aware that Alerts are for a whole column, not by a specific row, but workflows as you described are typically one time through and you likely aren't editing the same row and the workflow has passed to the end.

 

Hope this helps,

 

Craig

 

 

 

 

Udy Gold

Yes, this makes sense and it helps, but still I have missing points -

1. How can I make the sheet generate an email to the next person who needs to approve

2. Can I create a form which will update ONLY the approved checked box ?  In other words, I'd like the email to my boss present the line that is waiting for approval and allow him to to either click on the check box right there, or have a webform which present the information and have the check box for him. I'd rather that than have him login into smartsheet and look for the sheet and the record. This is more work and more room for errors.

J. Craig Williams

Udy,

 

To answer your first question, the Notification feature is likely what you are looking for.

 

The notification here:

 

 

 

will send Craig Williams (the person with the next action) when anyone other than Craig* changes the "Approved by Level 1 Manager" column.

 

If Sandra is the Level 1 Manager, for example, and approves the bonus, she selects the checkbox. Craig gets an email.

 

*Note that Notifications do not trigger for your own actions to send you an email. So if Craig clicks the "Approved by Level 1 Manager", he won't get an email.

 

Each link in the approval chain can be done the same way.

 

I'd suggest playing around with the feature and check out the emails you get. The right-away feature is good for this. Because of the note above, you'll need someone to help you trigger the change - or use another user's account to check the emails.

 

Hope this helps.

 

Craig 

 

Mike Bazinet

Is there any way to get the Contact List Column functionality from Reminders into the Notification syntax for when to email?

 

I'd like to send a Notification to an Approver in a specific column any time a new submission to the sheet is made, but it feels like the functionality I want is split between Notifications and Reminders...anyone else looking for the same?

J. Craig Williams

Mike -

 

What is the triggering event for a new submission? 

 

Here's something that might work.

Create a new column (CheckBox type) New_Submission.

Also add the System column "Created" (date) if it isn't already shown.

 

For existing rows, use this formula (for row 23)

 

=IF(DATEONLY(Created23) <= TODAY(), 1, 0)

 

This will have the checkbox checked because the date part of the Created column and compare to today. Anything in the past will be true (checked).

All existing rows should now have this.

 

When a user adds a row (from the sheet or WebForm), the AutoFill feature will copy the formula from the rows above or below the new row. And it will then check the New_Submission checkbox.

 

And you can trigger your Notification from there.

 

Hope this helps.

 

Craig

 

 

Mike Bazinet

Hi Craig,

 

I wanted to automate our paper submission form for Employee Recognition.  Here's what I am attempting to do:

 - Created Web Form - Employee Recognition request data entered into form

 - Submission Date to trigger an email Notification to Approver 1 (maybe Manager, HR or Finance) to approve (Yes, Hold, No)

 - If Yes - trigger email Notification to Approver 2 (maybe Manager, HR or Finance)

 - If Hold, No - trigger email to HR Administrator

 

I can get the Submission event to trigger email Notification but the cannot get the email Reminders to trigger for Approvers.

 

Does this make sense?

 

Thanks,

Mike

J. Craig Williams

Mike,

 

Yes, it makes sense.

 

First, to clarify:

Notifications are based on changes (any) by someone else.

Reminders are based on dates.

 

I assume the "Submission event to trigger" is a Notification based on the Submission date. 

[Aside: If your WebForm is asking for the user to enter this, you could instead use the System column "Created" (Date) and save your users typing]

 

This is doable, but it will take me a little time to document it.

Are you OK with adding hidden columns to get the functionality you want?

 

The sticking points are:

1. getting the system to send a Reminder (date based) because you want to you a contact list, not shared users. 

2. getting the system to only send that Reminder once

 

I'm almost done with a prototype but may get called away before I finish for a few hours.

 

Craig

 

 

 

 

 

J. Craig Williams

Mike,

 

First  pass not a success. My hurdlle is testing the Reminder functionality, since it queues between midnight and 5am. And then getting it to turn off after it has sent.

 

So I went back to the easy way:

 

 

The shaded columns have calculations. 

Whoever gets the Notification from the Submission_Date change will change the Approval_Dropdown to Yes, Hold, or No.

That changes the Dates in the next two columns.

(If the Reminders won't work, these can be check boxes or text, as the date is not needed)

Then key the Notification off the change in those rows.

 

I was also trying to solve the problem of who is getting those emails (email addresses) but that requires the Reminder functionality too. 

It feels (like on the tip of my tongue) that Reminders are the way to go for various reasons and I can ALMOST see the solution, but I don't have it yet.

 

If it comes to me, I'll update here.

 

If the image is not readable, here's the two formulas for row 23

 

=IF(Approval_Dropdown23 = "Yes", TODAY(), "")

 

=IF(OR(Approval_Dropdown23 = "No", Approval_Dropdown23 = "Hold"), TODAY(), "")

 

Hope this helps.

 

Craig

Mike Bazinet

Thank you so much!  I'm going to work on this over the weekend and let you know how I do!  

 

Agreed however, if SS combined the functionality of Notifications and Reminders into one item, I think it could be done just from that alone

Bob Andrews

Mike, I have a few ideas for this.

 

1. Zapier can do this. Zapier can look to column and send row information to someone via email based on a trigger. For example, if somone selects "Yes" to approve a request, it could send an email to either a predetermined email address or an email address located in the row with the contents of that row. Or if "No" is selected, email the contents of the row to HR.

 

2. Another option is to add a couple Date columns to your sheet with formulas that will show submission date plus one day and *tomorrows* day if a condition is met ("Yes"), then set a reminder on that column.

 

Examples:

 

Trigger an email based on submission date to Approver 1

 

Add this formula to a Date column:

 

=[Submitted Date]1 + 1

 

Set reminder based on this Date column and the Approver 1 Contact List column.

 

Trigger email based on approval:

 

Add this formula to another Date column:

 

=IF(Approved1 = "Yes", TODAY(1))

 

And add this to another Date column:

 

=IF(Approved1 = "No", TODAY(1))

 

Now, create reminders based on these Date columns and the Contact List if Yes (Approval 2) and manaully select HR for the "No" Date column.

 

 

A few caveats with this method...

 

-Everyone receiving the reminder emails will need to be shared to the sheet

-Entering a name in a web form, will not link it to a contact in Smartsheet. This means the approver information will need to be added manually after the web form is submitted or the emails will not be sent

-These reminders are set to send *tomorrow* and will keep sending each day until the Dates are removed from the Date columns

 

 

Another option is to create a report for each option and each employee who needs to approve or view the request. (show me all rows where Yes was selected and X is the manager who need to approve). These reports can be sent out on a recurring basis to the appropriate party as a PDF but will be blank if there are no rows that meet the criteria. 

ALAIN-STEPHANE

Any update about this concern in SMARTSHEET ? 

Andrée Starå

Hi ALAIN-STEPHANE,

What do you need/want to do?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting