I'm trying to create a URL to a form and ALSO prefill some of the form fields based on a sheet

Sandra Martin · 08/18/21

I've added a column on my master for the hyperlink to the form.

Everytime I try to enter the info at the end of the URL to pull data from the sheet into the form fields I am getting the error - Invalid URL.

As a example - I want to prefill several fields from the row in my spreadsheet onto the form so the user doesn't have to enter this every time they fill out the form.

Store Number, Name of User, Address, City, State, etc....

Andrée Starå · 08/18/21

Hi @Sandra in SC

I hope you're well and safe!

Have you looked at the URL Query documentation?

More info:

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

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sandra Martin · 08/18/21

I can create the URL and specify a field to pre-fill in the form if I do this as an individual field with specific data entered.

For Example: If I want the address to prefill in the form, and the address is : 301 El Camino Real, My URL reads:

https://app.smartsheet.com/b/form/1791ce19078b4697a3a026c043d95843?Address=3011%20El%20Camino%20Real

This fills in the Form correctly.

The issue I'm having is: When the user clicks on the URL, I'd like for SEVERAL fields to prefill on the form for them so they don't have to type them in each time. I want the fields to prefill based on information that is pulled from the row in the sheet.

So, For example, I want the Address to pull in from the column in my sheet:

The instructions I was following to do that tells me to enter this in my URL:

https://app.smartsheet.com/b/form/1791ce19078b4697a3a026c043d95843?Address="+SUBSTITUTE([Address]@row," ","%20")

This returns Invalid URL for me.

Is there an option to build this formula into the URL? Do I have the formula portion wrong? I'm fairly new to all of this

Steve Gebbie · 12/08/21

https://community.smartsheet.com/discussion/comment/301956#Comment_301956

@Sandra Martin Did you by chance figure this out? Looks like this is the exact same issue I am having. Spaces necessary for the correct formula do not work in a URL format and cannot be eliminated from what I can tell to preserve as a proper URL.

Penny Dunning · 10/02/22

@Sandra Martin - curious if you found a solution.

Monster Man · 12/21/23

I wrestled with this myself. Here is the answer. You are in essence building the actual URL by appending text from a cell reference. So the problem with our URL is that you need to add the =" to the beginning.

wrong:

https://app.smartsheet.com/b/form/1791ce19078b4697a3a026c043d95843?Address="+SUBSTITUTE([Address]@row," ","%20")

right:

="https://app.smartsheet.com/b/form/1791ce19078b4697a3a026c043d95843?Address="+SUBSTITUTE([Address]@row," ","%20")

The " after Address=" is the close quotes from the beginning of the cell. The command after the " will be evaluated when you press enter as with any other formula.

Again, this stumped me until I ran across this youtube video https://www.youtube.com/watch?v=H7qw7gbGjZk which triggered my AHAH moment. Thank you to Prodactive Product Solutions for this video.

Hope this helps.

-Mons

Trang Turtletraxx · 02/13/24

I came up with a solution for this for my approval process by using logic. The original form has hidden fields for the Approvers. When a form is submitted, I have a defaulted check box "Submitted" that does 2 things:

1) sends the automated notification to the Approvers with the URL to the pre populated form;

2) because the "Submitted" box is now checked, it triggers logic to open up the additional "hidden" fields we want the approvers to complete.

I created a URL query to pre populate the form fields and pre populate some of the fields specific to the approvers. This process also allows for that row to be "updated" vs creating a duplicate form with a different URL which causes a duplicate row to be submitted with the added approval fields.

Hope this helps!

I'm trying to create a URL to a form and ALSO prefill some of the form fields based on a sheet

Answers

Categories