URL Query String and CHAR() Function Helper Sheet

Kevin M.

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.

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

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.

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.

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

Paul Newcome

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

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

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

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

Paul Newcome

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

Genevieve P.

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

@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.

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

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.

@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

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

@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.