Pre-Filled Forms Using URL Queries: Tips and Tricks

Paul Newcome

Using URL queries to pre—fill forms seems to be catching on a bit more lately. There are a lot of resources out there, but they can be kind of spread out, so I figured I would start a thread that has some of the lessons learned, tips, and tricks that I have discovered along the way.

Tip #1

The first one that I find makes things more efficient is that for most of the special characters that need to be encoded, you can wrap the whole thing in a SUBSTITUTE function instead of using separate SUBSTITUTE functions for each field for the same character(s).

One of the most common is a space which needs to be swapped out with “%20”. Instead of swapping this out for all of the fields I want pre-populated individually, I can manage all of them at once using something along the lines of

=”form_url?” + SUBSTITUTE(“Field Name 1=” + [Field 1]@row + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20)

You can do this for multiple special characters by nesting SUBSTITUTE functions like so:

=”form_url?” + SUBSTITUTE(SUBSTITUTE(“Field Name 1=” + [Field 1]@row + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20), “@”, “%40”)

Tip #2

There are a few special characters though that you don’t want to swap out of the entire string. We want to keep =, %, and & in place because they are part of how a URL query works. To accommodate these special characters, we do need to wrap each specific field in a SUBSTITUTE function, but we only need to do this for fields that will potentially have these.

Using the above example, if we want to remove “=” from [Field 1], would use an individualize SUBSTITUTE function that works like this:

=”form_url?” + SUBSTITUTE(SUBSTITUTE(“Field Name 1=” + SUBSTITUTE([Field 1]@row , “=”, “%3D”) + “&Field Name 2=” + [Field Name 2]@row , “ “, “%20), “@”, “%40”)

Tip #3

One of the other quirks I have found was with date. One of the unintended consequences that came from the recent form updates was that dates had to be loaded into fields a very specific way. I think I read somewhere that it was fixed, but just to be on the safe side, I use a bit of a formula within a formula to fill my date field with the date in the “yyyy-mm-dd” format. This has worked very consistently so far. That piece ends up looking like this:

"&Date Field=" + YEAR(Date@row ) + "-" + MONTH(Date@row ) + "-" + DAY(Date@row )

Here is also a link to a published sheet that anyone can use. Instructions are across the top row and highlighted in the first column. Any questions, challenges, or feedback is welcome.

https://app.smartsheet.com/b/publish?EQBCT=f664d3a2680f458882453b691e2b8d7f

Below is a list of a good bit of the special characters and their percent encodings.

Character	% Encoding		Character	% Encoding		Character	% Encoding
space	%20		•	%95		Ê	%CA
line break	%0D%0A		–	%96		Ë	%CB
%	%25		—	%97		Ì	%CC
&	%26		˜	%98		Í	%CD
=	%3D		™	%99		Î	%CE
!	%21		š	%9A		Ï	%CF
#	%23		›	%9B		Ð	%D0
$	%24		œ	%9C		Ñ	%D1
'	%27		ž	%9E		Ò	%D2
(	%28		Ÿ	%9F		Ó	%D3
)	%29		¡	%A1		Ô	%D4
*	%2A		¢	%A2		Õ	%D5
+	%2B		£	%A3		Ö	%D6
,	%2C		¤	%A4		×	%D7
-	%2D		¥	%A5		Ø	%D8
.	%2E		¦	%A6		Ù	%D9
/	%2F		§	%A7		Ú	%DA
:	%3A		¨	%A8		Û	%DB
;	%3B		©	%A9		Ü	%DC
	%3C		ª	%AA		Ý	%DD
	%3E		«	%AB		Þ	%DE
?	%3F		¬	%AC		ß	%DF
@	%40		®	%AE		à	%E0
[	%5B		¯	%AF		á	%E1
\	%5C		°	%B0		â	%E2
]	%5D		±	%B1		ã	%E3
^	%5E		²	%B2		ä	%E4
_	%5F		³	%B3		å	%E5
`	%60		´	%B4		æ	%E6
{	%7B		µ	%B5		ç	%E7
|	%7C		¶	%B6		è	%E8
}	%7D		·	%B7		é	%E9
~	%7E		¸	%B8		ê	%EA
€	%80		¹	%B9		ë	%EB
‚	%82		º	%BA		ì	%EC
ƒ	%83		»	%BB		í	%ED
„	%84		¼	%BC		î	%EE
…	%85		½	%BD		ï	%EF
†	%86		¾	%BE		ð	%F0
‡	%87		¿	%BF		ñ	%F1
ˆ	%88		À	%C0		ò	%F2
‰	%89		Á	%C1		ó	%F3
Š	%8A		Â	%C2		ô	%F4
‹	%8B		Ã	%C3		õ	%F5
Œ	%8C		Ä	%C4		ö	%F6
Ž	%8E		Å	%C5		÷	%F7
‘	%91		Æ	%C6		ø	%F8
’	%92		Ç	%C7		ù	%F9
“	%93		È	%C8		ú	%FA
”	%94		É	%C9		û	%FB
						ü	%FC
						ý	%FD
						þ	%FE
						ÿ	%FF

Helpful links:

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

Ker9

If you are using Excel to submit data to a Smartsheet form, use Named Ranges for your field data and you will not need to use Substitute.

Example: (inserted returns to make it easier to read)

=HYPERLINK("https://app.smartsheet.com/b/form/
YourFormID?
YourFieldName01="&YourNamedRange01&"&
YourFieldName02="&YourNamedRange02&"&
YourFieldName03="&YourNamedRange03&"&
YourFieldName04="&YourNamedRange04&"&
YourFieldName05="&YourNamedRange05&"&
YourFieldName06="&YourNamedRange06,
"Your Link Name")

Dates must be formatted as text : =TEXT("12/31/2029" ,"MM/DD/YYYY") OR =TEXT(A2,"MM/DD/YYYY")

(My field names are one word each using the help text option to provide context.)