URL Query String to Populate a Form - Issues with " \ % characters

MCorbin

I'm playing around with the URL Query String to populate a form with data from one sheet to add it to another sheet (essentially, to copy rows, but only the columns I want).

The problem I'm having is that at least 1 of the fields is a description field and can be quite long and have a gazillion special characters in it.

I'm using Substitute to swap out most of the special characters, but I get an unparseable error if I try to use Substitute with a quote mark " , backslash, or percent sign. The ASCII characters are available in the help table, but they don't seem to work with the Substitute function. Any suggestions?

Here's my formula so far... for your entertainment:

="https://app.smartsheet.com/b/form/redacted" + "?Reference%20Number=" + [Project / Task ID]@row + "&Project%20Name=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Project/Task Name]@row, " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "=", "%3D"), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C") + "&Description=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Description@row, " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "=", "%3D"), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C"), CHAR(10), "%13") + "&Type=" + Type@row + "&Initiative=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Initiative@row, " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "=", "%3D"), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C")

Kelly Moore

Hey @MCorbin

The ASCII code for the quote sign is CHAR(34)

The backslash is CHAR(92)

percent sign is CHAR(37)

You may find this post helpful.

cheers

Kelly

Kelly Moore

Hey @MCorbin

The ASCII code for the quote sign is CHAR(34)

The backslash is CHAR(92)

percent sign is CHAR(37)

You may find this post helpful.

cheers

Kelly

MCorbin

Yes! Perfect! thank you!

Kevin M.

Coming in a couple of years late, but I figured others would land here when looking for answers.

I put together a reference sheet with UTF-8 and CHAR() codes and have published it for public use. Hope someone finds it useful!

See my post, here: URL Query String and CHAR() Function Helper Sheet — Smartsheet Community

-Kevin