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!!

pris

Hi @C Eiring, I was attempting to use the formula above and my form is not populating with any information. I would appreciate your time and assistance to get this up and running, thanks in advance for your help!

I attended Engage and was very excited to apply to our form but something is preventing it from running correctly.

="https://app.smartsheet.com/b/form/9858be5e35cc4de288dec6cf32c565bc?" + 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("Row ID=" + [Row ID]@row + "&Training Name=" + [Training Name]@row + "&Trainer Name=" + [Trainer(s)]@row + "&Partial Credit Permitted=" + [Partial Credit ]@row + "&Total Hours Attended=" + [CEU Total]@row, "%", "%25"), " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "||", "&"), "=", "%3D"), "|^", "="), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C"), CHAR(10), "")

C Eiring

@pris

Happy to help! First, If the fields that you are trying to use for your query string will not ever contain special characters, there is no reason to use this crazy formula. Regardless, it should work. I think you missed a little twist in the formula. If your fields may contain = or &, then a substitute of those characters break the = and & that are required in the query string itself. Because of this, you need to replace your = with |^ and & with || in your formula. I chose these characters because these are character patterns that are not likely to show up in the real world.

Try this:

="https://app.smartsheet.com/b/form/9b2b3b6943574582995174c62ffef22c?" + 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("Row ID|^" + [Row ID]@row + "||Training Name|^" + [Training Name]@row + "||Trainer Name|^" + [Trainer(s)]@row + "||Partial Credit Permitted|^" + [Partial Credit]@row + "||Total Hours Attended|^" + [CEU Total]@row, "%", "%25"), " ", "%20"), "#", "%23"), "$", "%24"), "&", "%26"), "||", "&"), "=", "%3D"), "|^", "="), ":", "%3A"), "<", "%3C"), ">", "%3E"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "+", "%2B"), "@", "%40"), "/", "%2F"), ";", "%3B"), "?", "%3F"), "|", "%7C"), "~", "%7E"), "'", "%27"), ",", "%2C"), CHAR(10), "")

pris

Thank you, that worked!