URL Query String and CHAR() Function Helper Sheet

Kevin M. · 02/03/23

Greetings all,

Because I grew tired of spending an inordinate amount of time piecing together URL query strings, I've compiled all of the UTF-8 character codes (that I came across) into an easy to use reference sheet.

I've also added in a column for the corresponding CHAR() function codes and conditionally formatted the sheet to highlight which characters are URL-safe (and thus do not need encoding in a query string) and which CHAR() codes fail to work with Smartsheet.

The sheet includes built-in shared filters to view by character category (letter, numbers, punctuation, etc.) and usability.

Feel free to use this for your own purposes.

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

If you would like to save a copy of this into your own account, fill out the form below and I'll share Commenter access.

UTF-8 and CHAR() Encoding Reference Share Request (smartsheet.com)

**I make no claim that this list is free from errors or omissions, but I believe it to be correct and complete. If you find any mistakes, please let me know and I'll update the sheet promptly.

-Kevin

Genevieve P. · 02/03/23

Hi @Kevin M.

Wow, what an absolutely fantastic resource - thank you for sharing! I've moved this over into our "Best Practice" area so more users can find/bookmark this.

Cheers!

Genevieve

Kelly Moore · 02/03/23

Hey @Kevin M

Thank you! And building on your great collection: here's another resource that offers the characters in many formats. I use this so much it's a bookmark in my favorites bar.

https://www.w3schools.com/charsets/ref_html_ascii.asp

Kevin M. · 02/03/23

https://community.smartsheet.com/discussion/comment/360363#Comment_360363

Thanks @Genevieve P. I hope that many folks can get some use out of it. When I have more time on my hands, I'm going to see if I can cobble together some more utilities to take the pain out of these URL query strings. Since I discovered them, I've found them to be so incredibly useful!

Kevin M. · 02/03/23

@Kelly Moore Thank you for sharing that here! I used w3schools when I put this together. It is an excellent resource!

Paul Newcome · 06/13/23

Here's one I am having trouble with... Line breaks in a multi-select dropdown.

I know the built in delimiter in multi-select dropdowns is line breaks.

On the form it is set to show all options and allow the user to just check whatever boxes they want.

I have my url string generated with no issues bringing in multiple selections from the multi-select dropdown (within the string itself).

I have tried "%0A", "%0D", and "%0D%0A".

Yet it basically creates a new option within the form and still outputs it aa a single string when the form is submitted instead of checking the boxes for the two options.

How do I get it to check the appropriate boxes. The client wants this pre-filled based on the row data but wants to still be able to update it if needed prior to submitting the form.

I noticed that it is (for some unknown reason) putting a line break even before "Option 1".

%0DOption%201%0DOption%202

But manually removing that from the URL still produces the same results within the form.

When I do just a SUBSTITUTE function outside of the URL in a cell in the sheet, it doesn't put that there.

I am guessing I need to figure out how to pass a "true" value, but then I also need to figure out how to get it to show "true" for the appropriate box. I know I can reference the field name, but does the text "Option 1" also count as an editable field?

@L_123 and @Andrée Starå I have also seen both of you making some posts/comments about URL strings, so I am hoping that someone might be able to help. I hate getting stumped like this.

L_123 · 06/13/23

Ok.... this one bothered me. I tried the basic

?Multi%20Select=Option%201&Multi%20Select=Option%202

and it didn't work. I started down the same rabbit hole as you did, decided to check the forum and found this

https://community.smartsheet.com/discussion/82515/forms-url-query-string-with-multi-select-dropdown

@Genevieve P.

and when i tried it again it worked...

L_123 · 06/13/23

https://app.smartsheet.com/b/form/49a4562bae4049199b809218f44d6165

https://app.smartsheet.com/b/form/49a4562bae4049199b809218f44d6165?A=A&A=B

Paul Newcome · 06/13/23

@L_123 and @Genevieve P. Lifesavers! I'm not sure how I missed that post when searching.

Genevieve P. · 06/13/23

Haha I wish I could tell you how I discovered that information in the first place but I had completely forgotten about it! Good find, @L_123

L_123 · 06/13/23

@Genevieve P. Maybe that could get added to the smartsheet article on query strings?

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

I can't seem to find this in any of the normal documentation.

Genevieve P. · 06/13/23

Good call - we could update the TIP in that article when it talks about including multiple fields to clarify that this can be the same field with multiple selections. Thanks!

Steve North · 08/04/23

Hello,

Please excuse my ignorance, but is the idea you have to use a substitute formula for each character you are needing to encode?

Thanks.

Kevin M. · 08/04/23

@Steve North

Yes, but you can nest the substitute functions like so:

SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( Column@row, " ", “%20"),"#", "%23"), "$", "%24"),"&","%26"),":", "%3A"), """, "%27"),",","%2C")

This will substitute %20 for spaces, then %23 for #'s, then %24 for $'s, and so on, starting from the center of the nest out.

Steve North · 08/04/23

Hi Kevin,

Thanks for your reply. Thats a shame, I was hoping someone would tell me about a function that I'd missed.

I've done that using this (with xxxx being my column name):

=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(SUBSTITUTE("xxxx", "%", "%25"), " ", "%20"), "&", "%26"), "'", "%27"), "+", "%2B"), ",", "%2C"), "/", "%2F"), ";", ":"), ":", "%3A"), "=", "%3D"), "?", "%3F"), "@", "%40"), "[", "%5B"), "]", "%5D"), "{", "%7B"), "}", "%7D"), "|", "%7C"), CHAR(10), "%0D"), ".", "%2E"), "!", "%21"), ">", "%3E"), "<", "%3C"), "*", "%2A"), "-", "%2D"), "(", "%28"), ")", "%29")

The problem I have got is that somehow someone is entering text into my form that contains a character that isn't visible, and when I return it back out to pre-populate another form, the URL when clicked isn't passed all the data it was sent. When I hover over the URL in the column in smartsheet it is clear that half of the value is underlined and the other half isn't.

Anyone have any idea how I can identify what this character is that could be causing this? As you can see I'm already checking for carriage returns with char(10).

Thanks in anticipation.

Steve.

Paul Newcome · 08/04/23

@Steve North The URL is going to tell you. Where does it stop being a URL and start being plain text? That's exactly where the issue is going to be.

URL Query String and CHAR() Function Helper Sheet

Comments

Categories