I couldn't find a url_decode
function in Google Sheets, so I wrote a small snippet that does it for me. It's basically a wrapper for the JavaScript decodeURI
function. Nothing special.
The function is convenient when working with decoded URLs / strings that you need to analyse for something, especially when dealing with accented characters (é
,ü
), spaces (%20
), etc.
Here it is:
/**
* URL_decode a string or range of strings
*
* @param {range} input The value or range of cells to decode.
* @return The input.
* @customfunction
*/
function decoder(input) {
if (input.map) {
return input.map(decoder);
} else {
return decodeURI(input);
}
}
The function either accepts a single cell (e.g.: A1
) or a range of cells (e.g.: A1:A43652
).
I try to only use it when a string actually needs to be decoded; not on all strings in a given range. For example, I first check in a range for a percent sign in a string in a cell. If there is one, we should decode the cell. Otherwise, don't call this (slow) function.
So if I have a range of 43652 cells and about 100 of them have encoded strings, then I should only call this function for these 100 cells.
This function does the following:
arrayformula(range)
function makes sure that I only have to write this function once: it will execute it for the whole range of cells that are given to the function. No need to copypaste it everywhere!if(regexmatch(range;"%")
-statement checks with a simple regex if a string in a cell in the range has a percent-signTRUE
, execute the decoder()
functionFALSE
, return an empty string (or something else, if you want)=arrayformula(if(regexmatch(A1:A,"%"),decoder(A1:A),""))
Happy decoding :)
If you think this was useful to you, it might be for others also. Feel free to share: