Replacing all special characters in form query string parameters with a formula
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
