1 min.
Url_decoding in bulk in Google Sheets, by using this simple App Script #
It's basically a wrapper for the JavaScript decodeURI function. Nothing special.
The function is convenient when working with a lot of decoded URLs / strings that you need to analyse for something, especially when dealing with accented characters (é,ü), spaces (%20), etc.
This script checks if the encoding function should be called, and if so, it executes it. This first check makes sure you don't use unnecessary cycles, and simply speeds things up.
The code #
Here it is:
/**
* URL_decode a string or range of strings
*
* @param {range} input The cell or range of cells to decode.
* @return The decoded output.
* @customfunction
*/
function decoder(input) {
if (input.map) {
return input.map(decoder);
} else {
return decodeURI(input);
}
}Usage #
Use it like this:
=arrayformula(if(regexmatch(A1:A,"%"),decoder(A1:A),""))Let's break it down:
The decoder() 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:
-
-
The
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! -
-
The
if(regexmatch(range;"%")-statement checks with a simple regex if a string in a cell in the range has a percent-sign -
-
If
TRUE:execute thedecoder()function -
-
If
FALSE: return an empty string (or something else, if you want)
Happy decoding :)