Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Spreadsheets script to generate slugs from a range of cells

This script for Google Spreadsheets allows you to generate slugs for your data such as might be used for creating unique urls.

Use it like this!

# A B C
1 a b slug
2 foo baz bing =slugify(A2:B4)
3 bar BAZ
4 FOO baz-bing

And you get this!

# A B C
1 a b slug
2 foo baz bing foo-baz-bing
3 bar BAZ bar-baz
4 FOO baz-bing foo-baz-bing-2
function slugify(values) {
/*
* Convert the the vs in a range of cells into slugs.
*/
slugs = [];
for (var row = 0; row < values.length; row++) {
var bits = [];
for (var column = 0; column < values[row].length; column++) {
var value = values[row][column];
value = value.toLowerCase();
value = value.replace(/[^\w\s-]/g, '');
value = value.replace(/\s+/g, '-');
bits.push(value);
}
var base = bits.join('-');
var i = 1;
var slug = base;
while (slugs.indexOf(slug) >= 0) {
i++;
slug = base + '-' + i;
}
slugs.push(slug);
}
output = [];
for (var s = 0; s < slugs.length; s++) {
output.push([slugs[s]]);
}
return output;
}
function test() {
/*
* Test the slugify function.
*/
var TESTS = [
['foo', 'baz bing'],
['bar', 'BAZ'],
['FOO', 'baz-bing']
];
var RESULTS = [
'foo-baz-bing',
'bar-baz',
'foo-baz-bing-2'
];
var output = slugify(TESTS);
for (var t = 0; t < TESTS.length; t++) {
var test = TESTS[t];
var result = RESULTS[t];
Logger.log("[" + test + "] == " + result);
Logger.log((output[t][0] == result) + " ... " + output[t][0]);
}
}
@Edo78

This comment has been minimized.

Copy link

Edo78 commented Jun 30, 2015

it should be nice if it works also with a single cell instead of an interval ...

@hupili

This comment has been minimized.

Copy link

hupili commented Jul 29, 2015

@Edo78 , a quick upgrade would be to add following section at the beginning:

  // work with single cell
  if (!Array.isArray(values)) {
    values = [[values]];
  } 
@abneralvarado

This comment has been minimized.

Copy link

abneralvarado commented Sep 29, 2015

Hi @onyxfish could you explain step by step how to implement this code in a new google spreadsheet?

@robertpataki

This comment has been minimized.

Copy link

robertpataki commented Jan 5, 2016

@Edo78 +1. Probably you found your solution already, but I thought I put mine here, as I needed a solution to generate slugs from single cells. =REGEXREPLACE(LOWER(REGEXREPLACE(A2, "[| $]", "-")), "[().]", "") I hope it helps :)

@AndreiRailean

This comment has been minimized.

Copy link

AndreiRailean commented Jul 15, 2016

⭐️

@imsamthomas

This comment has been minimized.

Copy link

imsamthomas commented Oct 5, 2016

Filter Vietnamese strings, you should add

  value = value.replace(/[á|à|ạ|ả|ã|ă|ắ|ằ|ặ|ẳ|ẵ|â|ấ|ầ|ậ|ẩ|ẫ]/g, "a");
  value = value.replace(/[Á|À|Ạ|Ả|Ã|Ă|Ắ|Ằ|Ặ|Ẳ|Ẵ|Â|Ấ|Ầ|Ậ|Ẩ|Ẫ]/g, "A");
  value = value.replace(/[ó|ò|ọ|ỏ|õ|ô|ố|ồ|ộ|ổ|ỗ|ơ|ớ|ờ|ợ|ở|ỡ]/g, "o");
  value = value.replace(/[Ó|Ò|Ọ|Ỏ|ÕÔ|Ố|Ồ|Ộ|Ổ|Ỗ|Ơ|Ớ|Ờ|Ợ|Ở|Ỡ]/g, "O");
  value = value.replace(/[é|è|ẹ|ẻ|ẽ|É|È|Ẹ|Ẻ|Ẽ|ê|ế|ề|ệ|ể|ễ|Ê|Ế|Ề|Ệ|Ể|Ễ]/g, "e");
  value = value.replace(/[ú|ù|ụ|ủ|ũ|ư|ứ|ừ|ự|ử|ữ]/g, "u");
  value = value.replace(/[Ú|Ù|Ụ|Ủ|Ũ|Ư|Ứ|Ừ|Ự|Ử|Ữ]/g, "U");
  value = value.replace(/[í|ì|ị|ỉ|ĩ|Í|Ì|Ị|Ỉ|Ĩ]/g, "i");
  value = value.replace(/[ý|ỳ|ỵ|ỷ|ỹ|Ý|Ỳ|Ỵ|Ỷ|Ỹ]/g, "y");
  value = value.replace(/[đ]/g, "d");
  value = value.replace(/[Đ]/g, "D");
  value = value.replace(/[ç]/g, "c");
@TylerFisher

This comment has been minimized.

Copy link

TylerFisher commented Dec 1, 2016

Added a complete list of accented character replacement here: https://gist.github.com/TylerFisher/ebcacf009f45ce0b701c7b70b296fe3f

@slayer49

This comment has been minimized.

Copy link

slayer49 commented Jan 18, 2017

Check for a dash surrounded by spaces to eliminate triple dashes:

eg: "Book - Dealer" becomes "book---dealer" instead of "book-dealer"

value = value.replace(/\s-\s/g, ' ');

@Ortizgrafael

This comment has been minimized.

Copy link

Ortizgrafael commented May 8, 2018

Not the most elegant solution.. but worked for me to generate slug from one single cell, on Collun E.
Obs. I had trouble with the ' " ' caracter. So I put it in the aux cell G1.

=(SUBSTITUTE(SUBSTITUTE(trim(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(regexreplace(regexreplace(regexreplace(regexreplace(REGEXREPLACE(REGEXREPLACE(lower(E2);" : ";"-");",";"");"ç";"c");"à";"a");" - ";"-");"õ";"o");"ô";"o");"ã";"a");"ê";"e");"ú";"u')");"ó";"o");"í";"i");"é";"e");"?";);",";);"'";);$G$1;);":";);")";);"(";);"º";);"ª";);"á";"a");"--";"-");"=";));" ";"-");"/";))

Hope it helps

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.