Bulk decode URLs in Google Sheets

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 #

/**
 * 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 the decoder() function
-

If FALSE, return an empty string

Happy decoding :)

Bulk decode URLs in Google Sheets

The code #

Usage #

Want more?