URL Query String and CHAR() Function Helper Sheet

Ke6n_USHV

Fantastic work everyone! No telling how many hours saved

Kevin M.

Thank you, @Ke6n_USHV

I'm currently working on a companion sheet that will help build the substitute formulas (and URL strings)—checkbox tick the characters you want to include, specify your root URL and column references, and voila!

Unfortunately, I've discovered that you can run into formula character limits pretty quickly, so I've been trying to optimize it as best I can before releasing it here.

Essentially, one will need to SUBSTITUTE from the text/column reference any character that might appear in a legitimate URL (i.e., ":", "?", "/", etc…) on EACH queried field, and then wrap the entire string in another SUBSTITUTE that swaps out all the other characters (so that the majority only happens once, vs. being repeated for every field).

What I wouldn't give for a LET() or LAMBDA() function where we could toss the pre-text SUBSTITUTE formula part and the post-text formula part into two separate fields on the Sheet Summary and then just call them up as needed.

Anyhow, this companion tool is a work in progress that I tinker with when I have the time and remember that it exists, but I hope to have it ready to go before too long.

Paul Newcome

@Kevin M. This is definitely possible to build. I built something out about a year and half ago where I can drop in the base form URL, and specify what fields I want pre-filled and with what, various validation points such as multi-select dropdown options, phone numbers, emails, etc..

Then I have a column that contains everything I could find that would need percent encoded. I have one checkbox column that looks at the various fields and whatnot to see if there are definitely any (like automatically percent encoding open and closed parenthesis, the plus symbol, and hyphens if I have phone validation on a field) and automatically checks the box for those symbols. Then I have another column where I can manually select additional symbols that I want encoded.

It eventually outputs the appropriate formula that I can then copy/paste into my sheet.

It was a bear to build and required some creativity, but it is definitely doable. Don't give up on it.