Bulk decode URLs in Google Sheets by using this simple Google Apps Script

I couldn't find a url_decode function in Google Sheets, so I wrote a small snippet that does it for me.

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

  • -

    If FALSE: return an empty string (or something else, if you want)

Happy decoding :)

→ Call to action ←

Sounds interesting?

Then let's make the virtual gap between us a little bit shorter!