Url_decode in Google Sheets

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.

The code

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);
  }
}

Usage

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:

  • 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 the decoder() function
  • If FALSE, return an empty string (or something else, if you want)
=arrayformula(if(regexmatch(A1:A,"%"),decoder(A1:A),""))

Happy decoding :)

What's next?

If you think this was useful to you, it might be for others also. Feel free to share: