Replacing all special characters in form query string parameters with a formula

C Eiring

Hello everyone!

I am excited to be speaking at the "Leveraging the Query String in Forms" session at Engage 23!

I wanted to share a formula that a customer and I developed to replace any special characters that could show up in cell when you are building a URL to a form with query string parameters using a formula. This would typically be used if you were trying to pass a free text cell such as notes or description to a form.

A unique part of this solution is that if = or & show up in a cell that you are referencing for the value of a query string parameter, you would be replacing those values with the encoded value in values, but those values are also needed to properly format the string. I have used || to represent the & character and |^ to represent the = in the formula, the properly replacing them with SUBSTITUTE after any & or = have been replaced.

="<form URL>?" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(<query string formula>, "%", "%25"), " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "||", "&"), "=", "%3D"), "|^", "="), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C"), CHAR(10), "")

<form URL> would be the URL of the form itself. Like https://app.smartsheet.com/b/form/1a2dsfs8343

<query string formula> would be something like “Project Number|^” + [Project Number]@row + “||Project Name=” + [Project Name]@row

I hope to see many of you at Engage this year!

Chad

SteyJ

Hi @C Eiring, thank you for sharing this. The & fix is fantastic!

ro.fei

I attended a session at ENGAGE last year where I believe you or perhaps a colleague of yours talked about this. I thought it was so intriguing & now I finally have the opportunity to use it in a solution I'm building! Came to find this post as soon as I started & it helped SO much. Can't thank you enough for posting things like this here!!