Automating the Sending of Form Once a Cell is Designated as Completed

Jamie Dent

Hi,

We would like to automate the process of sending a Form (survey) to a group once we have marked a project as "Completed" in a Grid/Sheet. In addition, we'd like the Form to prepopulate the name of the project for tracking purposes. I've scoured the boards and don't see any mentioned of this. Is this even possible?

Thanks,

Jamie

cibfit

@Jamie Dent Good afternoon. I was looking to solve the exact same scenario. I found this video really helpful and perhaps more directly answers your question. Good luck!

https://youtu.be/H7qw7gbGjZk?si=YK2dCR_5xHj4m01Z

Darren Mullen

@Jamie Dent Yes, you can do this. I assume you have a sheet with a list of project names and then you mark the status as complete?

You can add a column to concatenate the project name as a URL query string onto the form URL, then you'd use an automation workflow to "send an alert" triggered on the status change and include the column with the URL as a field in the message body.

Does that sound like it would work for you?

I have a video I made that might help you some if you aren't familiar with URL query strings in Smartsheet forms.

https://youtu.be/GFdDextsBUI

Jamie Dent

This may be way over my head. I'll come back to this when my mind is fresh one morning and see if I can make sense of it.

Darren Mullen

@Jamie Dent It's not really that hard, you just need to make sure you account for the special characters in the URL string.

Jamie Dent

I don't see how this applies as the video highlights designating a URL to determine where the submitter came from. That isn't relevant because these are targeted to employees within our institution. Here is more context.

When I mark a project as Submitted in the column labeled Progress_Status, I want the survey sent to the designee in a column labeled Email. The survey is a seperate From than the database/project tracker.

I hope this helps and thank you for taking the time to provide guidance.

Jamie

cibfit

@Jamie Dent Good afternoon. I was looking to solve the exact same scenario. I found this video really helpful and perhaps more directly answers your question. Good luck!

https://youtu.be/H7qw7gbGjZk?si=YK2dCR_5xHj4m01Z

Jamie Dent

@cibfit - You are amazing for thinking of me! Thank you so much.

So this is the solution I am working with at this time. I have created an additional column in our db to include a formula that pre-populates the feedback form. I have set-up an automation to send to the individual once the Progess_Status is marked Submitted. It works!

A new challenge I am having is this: I want to pre-populate the Title of the project. As you know, a title is a string if several individual words. It is awkward to format a title such as - I_want_to_do_something_amazing - when the entry is - I want to do something amazing. Is there a way to include in the formula to capture the spaces. In its current state, the only capture of the title is the "I" because it won't read the rest of the field. That also means if I use the "&" to include other columns the formula breaks and won't complete.

="https://app.smartsheet.com/b/form/...................................?Funder%20Name=" + [Prime_Sponsor]@row + "&Date%20Submitted=" + [Date_Submitted]@row + "&Name=" + [Faculty@row] + "&Title=" + [Title@row]

UPDATE: I figured it out by using this:

="https://app.smartsheet.com/b/form/................................?Name=" + Faculty@row + "&Funder%20Name=" + [Prime_Sponsor]@row + "&Date%20Submitted=" + [Date_Submitted]@row + "&Title=" + SUBSTITUTE([Title_of_Study]@row, “ “, “%20”)