Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@onyxfish
Last active January 2, 2023 14:37
Show Gist options
  • Star 43 You must be signed in to star a gist
  • Fork 6 You must be signed in to fork a gist
  • Save onyxfish/db112abb8c1d8a5018e5 to your computer and use it in GitHub Desktop.
Save onyxfish/db112abb8c1d8a5018e5 to your computer and use it in GitHub Desktop.
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
Copy link

Edo78 commented Jun 30, 2015

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

@hupili
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
Copy link

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

@robpataki
Copy link

@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
Copy link

@imsamthomas
Copy link

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
Copy link

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

@slayer49
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
Copy link

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

@waheed-gk
Copy link

waheed-gk commented May 8, 2020

This should work for both single cells and arrays of cells including within the ARRAYFORMULA

function slugify(input) {
  if (input.map) {            // Test whether input is an array.
    return input.map(slugify); // Recurse over array if so.
  } else {
    let slug = '';
    slug = input.toLowerCase();
    slug = slug.replace(/[^\w\s-]/g, '');
    slug = slug.replace(/\s+/g, '-');
    return slug;
  }
}

@thaobn20
Copy link

06:07:20 Error TypeError: Cannot read property 'length' of undefined slugify @ slugify.gs:7
Debug error. i can't run it

@thaobn20
Copy link

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2);" ";"-");"á";"a");"à";"a");"ạ";"a");"ả";"a");"ã";"a");"ă";"a");"ắ";"a");"ằ";"a");"ặ";"a");"ẳ";"a");"ẵ";"a");"â";"a");"ấ";"a");"ầ";"a");"ậ";"a");"ẩ";"a");"ẫ";"a");"ú";"u");"ù";"u");"ụ";"u");"ủ";"u");"ũ";"u");"ư";"u");"ứ";"u");"ừ";"u");"ự";"u");"ử";"u");"ữ";"u");"ó";"o");"ò";"o");"ọ";"o");"ỏ";"o");"õ";"o");"ô";"o");"ố";"o");"ồ";"o");"ộ";"o");"ổ";"o");"ỗ";"o");"ơ";"o");"ớ";"o");"ờ";"o");"ợ";"o");"ở";"o");"ỡ";"o");"é";"e");"è";"e");"ẹ";"e");"ẻ";"e");"ẽ";"e");"ê";"e");"ế";"e");"ề";"e");"ệ";"e");"ể";"e");"ễ";"e");"í";"i");"ì";"i");"ị";"i");"ỉ";"i");"ĩ";"i");"ý";"y");"ỳ";"y");"ỵ";"y");"ỷ";"y");"ỹ";"y");"đ";"d"))
i'm using for vietnam charactor

@akobashikawa
Copy link

In Google Sheets, I use this: =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(LOWER(A2), "á", "a"), "é", "e"), "í", "i"), "ó", "o"), "ú", "u"), "ñ", "n"), "[^\w]", "-"), "--+", "-"), "-$", "")

@maixuandiep810
Copy link

@thaobn20

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2);" ";"-");"á";"a");"à";"a");"ạ";"a");"ả";"a");"ã";"a");"ă";"a");"ắ";"a");"ằ";"a");"ặ";"a");"ẳ";"a");"ẵ";"a");"â";"a");"ấ";"a");"ầ";"a");"ậ";"a");"ẩ";"a");"ẫ";"a");"ú";"u");"ù";"u");"ụ";"u");"ủ";"u");"ũ";"u");"ư";"u");"ứ";"u");"ừ";"u");"ự";"u");"ử";"u");"ữ";"u");"ó";"o");"ò";"o");"ọ";"o");"ỏ";"o");"õ";"o");"ô";"o");"ố";"o");"ồ";"o");"ộ";"o");"ổ";"o");"ỗ";"o");"ơ";"o");"ớ";"o");"ờ";"o");"ợ";"o");"ở";"o");"ỡ";"o");"é";"e");"è";"e");"ẹ";"e");"ẻ";"e");"ẽ";"e");"ê";"e");"ế";"e");"ề";"e");"ệ";"e");"ể";"e");"ễ";"e");"í";"i");"ì";"i");"ị";"i");"ỉ";"i");"ĩ";"i");"ý";"y");"ỳ";"y");"ỵ";"y");"ỷ";"y");"ỹ";"y");"đ";"d"))
i'm using for vietnam charactor

Đ is not changed D

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment