URL Query String and CHAR() Function Helper Sheet
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.
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
Comments
-
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
-
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.
-
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!
-
@Kelly Moore Thank you for sharing that here! I used w3schools when I put this together. It is an excellent resource!
-
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.
-
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
and when i tried it again it worked...
-
-
@L_123 and @Genevieve P. Lifesavers! I'm not sure how I missed that post when searching.
-
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
-
@Genevieve P. Maybe that could get added to the smartsheet article on query strings?
I can't seem to find this in any of the normal documentation.
-
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!
-
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.
-
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.
-
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.
-
@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.
