Creating a Form that populates data from a Sheet

Lou Candiello

Is it possible to create a form that autopopulates information from an existing sheet? As a step one in my process, I have individuals filling out a form. For example, information such as name, email, location, etc. For step two, I'd like to pull answers from those questions to autopopulate the next form that needs to be filled out.

SSFeatures

Hi @Lou Candiello, you can use query parameters to prefill default values on a form.

Here's an article that explains how to do it:

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

I just created an example with Name, Email, and Location.

I created a "Form URL" column where I use a formula to generate me the fully filled out URL based on the values in my Name, Email, and Location column.

="https://app.smartsheet.com/b/form/a9c4c482cdbb44018c12f69d6afefc98?Name=" + 
SUBSTITUTE(Name@row, " ", "%20") + 
"&Email=" + 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Email@row, "@", "%40"), " ", "%20"), "+", "%2B"), "&", "%26") + 
"&Location=" + 
SUBSTITUTE(Location@row, " ", "%20")

• The "https://app.smartsheet.com/b/form/ part is my Form URL.
• Then it has "?Name=" which starts the Name query parameter.
• Then I perform substitutions, then set the Email and Location values.

The reason I had to use substitutions is because certain characters are not allowed in a URL. For example, if the name was "Nathan Braun", then that would break the URL, because URLs are not allowed to have spaces. So I replaced the space with the character "%20", which is allowed in a URL and tells the URL that this is actually a space. Similarly for email and location.

This type of substitution is called "URL Encoding".

Best!

Adrian Mandile CHESS

Good solution. The only improvement I’d make is to use only one SUBSTITUTE function and embed the entire calculated text string within it to replace any spaces.

Andrée Starå

https://community.smartsheet.com/discussion/139561/creating-a-form-that-populates-data-from-a-sheet

Hi,

I hope you're well and safe!

To add to Nathan's excellent advice/answer.

- Another option could be to use an Update Request instead.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Lou Candiello

This is not exactly what I am looking for. I have a form that is filled out with unique person data (first name, last name, email, phone number, etc.). This populates a sheet. The next step in the process is for someone to call the person and answer some questions. I would hope to pull the core data from the sheet to populate a new form with the additional questions below this. So, building from data that is on one sheet to continue to the next step. The additional data would either be placed on additional rows on the original sheet, or if necessary, populate a new sheet.

I am not looking to have a standardized form for Bill, Jane, or Andre to fill out, so they don't need to continually add that. I am looking for person data on separate rows to populate the header data for the next form to be filled out about them.

Doesn't seem this is easy to pull off. Feels like I need to have a developer's mind.

Darren Mullen

@Lou Candiello

You can do what you are describing with URL Queries. If a visual example helps, see my video below. You'd expand on that and create a clickable link to the uniquely generated URL on each row in the sheet with a column formula. Something I have done in several production solutions to do exactly what you are describing.

Otherwise, I've covered this in much more detail in Smartsheet Guru Elite sessions. I've got a promo to get the 1st month for $1 and you can get all the previous sessions that covered this. https://smartsheetguru.com/onedollar

https://youtu.be/GFdDextsBUI

Andrée Starå

https://community.smartsheet.com/discussion/comment/486070#Comment_486070

Then you should use the URL-Query feature that Nathan described earlier in this thread. (The first answer)

Make sense?

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏