Back to Lab home

Generate email addresses and random passwords using Google Sheets custom functions

By Pascal Aubort — Aug 20, 2017 in Google Sheets

Back to School means a lot of work for IT Admins. Creating user accounts for the new students and the staff is time consuming and if not done properly, can cause an unnecessary overload of support requests for the IT staff. A few days ago I was provided with a Google Sheet containing the first and last names of 150 students for which G Suite accounts were to be created. After the creation of the individual accounts, the School administration was going to send out a letter to each student with their login credentials.

The username and when “Nüßli” becomes “Nuessli”

Being in Switzerland, many first and last names contain accents. The challenge here was to find a way to replace the accented characters by their equivalent so that the name still has the same meaning. For example, the german “ü” becomes “ue”. The same goes for the “ä” and “ö”. Even though we generally don’t use the sharp S (ß), this should also translate to a double “s”.

Add to that the hyphenated names like Anne-Marie, Jean-Pierre, etc, and it becomes quickly pretty clear that concatenating the first and last name to generate our email addresses wasn’t going to be enough and we needed to create something more elaborated.

Complex and unambiguous passwords

In order for the Admin staff to be able to provide the initial password to the students, it was required to define a default password when creating the accounts, and not let G Suite create it itself. Of course, the passwords should be:

  • Unique
  • Complex - contain letters, numbers and special characters
  • At least 8 characters
  • Unambiguous - doesn’t contain the letter “O” and the number zero “0”

There are quite a few examples such as this Stackoverflow answer about how to generate random passwords in Google Sheets, but here as well, it became quite clear that there should be a more convenient way to solve this problem without having to use formulas such as the one mentioned in the above Stackoverflow answer:


Custom Functions to the rescue

Let’s start by exploring Google Sheets Custom Functions. Google Sheets (like Excel) offers hundreds of build-in functions that can be used to execute calculations such as SUM(), AVERAGE(), MAX(), etc. The beauty of Google Sheets is that you don’t have to limit yourself to these existing functions, you can create your own to extend Google Sheets functionalities. Custom functions are written in Javascript and are used the exact same way as the built-in functions and providing JSDoc allows the user to get help when using your custom function.

In order to get started with custom functions and Google Apps Script in general - you will first need to create a new Google Sheets. You can click on this link to create a new sheet.

Once you have created your Google Sheet, in the menu go to Tools > Script Editor… in order to open the Script Editor.

Once the code editor has opened, start by pressing “Save” and provide a name to your project.

The PASSWORD() function

In the open file, there should be a default function that looks like this.

function myFunction() {

In the editor, remove the default function, paste the following lines and save your project.

// Range of letters and numbers to include in the password
// Modify if needed to add or remove characters
var PASSWORD_CHARS_RANGE = "abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789!@#$%*+?";

* Return a randomly generated password of the given length. If no length is provided, defaults to 8.
* @param {number} length (Optional) The length of the password - Default is 8 characters
* @return A password
* @customfunction

function PASSWORD(length){
  var passwordLength = 8;
  var password = "";
  // Do some checks on the params
  if ((typeof length !== "undefined") && ((length >= 1) && (typeof length === "number") && Math.floor(length) === length)){
    passwordLength = length;
  // Add random characters to the password string
  for (var i = 0; i < passwordLength; i ++){
   password += PASSWORD_CHARS_RANGE.charAt(Math.random() * PASSWORD_CHARS_RANGE.length);
  return password;

Now you can go back to your spreadsheet and try out the function.

  • =PASSWORD() will return a default 8 characters long password
  • =PASSWORD(3) will return a 3 characters long password
  • =PASSWORD(15) will return a 15 characters long password

You can also customize which characters the password can contain by adding or removing characters in the PASSWORD_CHARS_RANGE variable.

The EMAIL() function

The generation of the email address uses the following parameters:

  • A First Name (at least 1 character) or empty if Last Name is provided
  • A Last Name (at least 1 character) or empty if First Name is provided
  • A Separator that will be used between the first and last name (default is “.”)
  • A Domain that will be appended to create the email address @domain

Let’s start with the function that will retrieve the email address based on the above parameters. We will take care of the special characters and accents at the next step.

In your code editor, add the default separator variable at the beginning of the file.

// The default separator between the first and last part of the email address

Now, after the PASSWORD function, add the EMAIL function.

* Return an email address based on a 2-part name, domain and separator
* @param {string} firstName The first name (can also be a single letter)
* @param {string} lastName The last name (can also be a single letter)
* @param {string} domain The domain of your email adresses. WITHOUT the "@" sign
* @param {string} separator (Optional) A character that will separate the first and last name. 
* @return A formatted email address
* @customfunction
function EMAIL(firstName, lastName, domain, separator) {
  var first = "";
  var last = "";
  var emailAddress = "";
  if(((typeof firstName === "undefined") && (typeof lastName === "undefined")) || (typeof domain === "undefined") || (domain === "")){
    return "Error - too few arguments"
  if(firstName != ""){
    first = firstName;
  if(lastName != ""){
    last = lastName;
  if((firstName == "") || (lastName == "")){
    sep = "";
  else if(typeof separator !== "undefined"){
    sep = separator;
  emailAddress = (first + sep + last + "@" + domain);
  return emailAddress.toLowerCase();

In your spreadsheet, add the following formulas to see the different results

  • =EMAIL("John", "Doe", "") =>
  • =EMAIL("john", , "") =>
  • =EMAIL(,"Doe" , "") =>
  • =EMAIL("John", "Doe", "", "_") =>
  • =EMAIL("Jo", "Nüßli", "", "_") => jo_nüß

Replace special characters (removing diacritics)

The first step in replacing characters that can’t be used in email addresses is to create a map that maps the special character to the letter(s) it should be replaced with. There are quite a few examples online and I based mine on this Stackoverflow answer, which I modified to take into account the German specific accents and letters.

Below the PASSWORD_CHARS_RANGE variable declaration, add the diacritics object and function following it.

* Credits to for the didacritics bits. 
* The original post can be found here
* I have made some adaptations so that it converts accented letters mostly used in german
* such as ä -> ae, ü -> ue, ß -> ss and so on.

var defaultDiacriticsRemovalMap = [
  {'base':'A', 'letters':'\u0041\u24B6\uFF21\u00C0\u00C1\u00C2\u1EA6\u1EA4\u1EAA\u1EA8\u00C3\u0100\u0102\u1EB0\u1EAE\u1EB4\u1EB2\u0226\u01E0\u00C4\u01DE\u1EA2\u00C5\u01FA\u01CD\u0200\u0202\u1EA0\u1EAC\u1EB6\u1E00\u0104\u023A\u2C6F'},
  {'base':'B', 'letters':'\u0042\u24B7\uFF22\u1E02\u1E04\u1E06\u0243\u0182\u0181'},
  {'base':'C', 'letters':'\u0043\u24B8\uFF23\u0106\u0108\u010A\u010C\u00C7\u1E08\u0187\u023B\uA73E'},
  {'base':'D', 'letters':'\u0044\u24B9\uFF24\u1E0A\u010E\u1E0C\u1E10\u1E12\u1E0E\u0110\u018B\u018A\u0189\uA779\u00D0'},
  {'base':'E', 'letters':'\u0045\u24BA\uFF25\u00C8\u00C9\u00CA\u1EC0\u1EBE\u1EC4\u1EC2\u1EBC\u0112\u1E14\u1E16\u0114\u0116\u00CB\u1EBA\u011A\u0204\u0206\u1EB8\u1EC6\u0228\u1E1C\u0118\u1E18\u1E1A\u0190\u018E'},
  {'base':'F', 'letters':'\u0046\u24BB\uFF26\u1E1E\u0191\uA77B'},
  {'base':'G', 'letters':'\u0047\u24BC\uFF27\u01F4\u011C\u1E20\u011E\u0120\u01E6\u0122\u01E4\u0193\uA7A0\uA77D\uA77E'},
  {'base':'H', 'letters':'\u0048\u24BD\uFF28\u0124\u1E22\u1E26\u021E\u1E24\u1E28\u1E2A\u0126\u2C67\u2C75\uA78D'},
  {'base':'I', 'letters':'\u0049\u24BE\uFF29\u00CC\u00CD\u00CE\u0128\u012A\u012C\u0130\u00CF\u1E2E\u1EC8\u01CF\u0208\u020A\u1ECA\u012E\u1E2C\u0197'},
  {'base':'J', 'letters':'\u004A\u24BF\uFF2A\u0134\u0248'},
  {'base':'K', 'letters':'\u004B\u24C0\uFF2B\u1E30\u01E8\u1E32\u0136\u1E34\u0198\u2C69\uA740\uA742\uA744\uA7A2'},
  {'base':'L', 'letters':'\u004C\u24C1\uFF2C\u013F\u0139\u013D\u1E36\u1E38\u013B\u1E3C\u1E3A\u0141\u023D\u2C62\u2C60\uA748\uA746\uA780'},
  {'base':'M', 'letters':'\u004D\u24C2\uFF2D\u1E3E\u1E40\u1E42\u2C6E\u019C'},
  {'base':'N', 'letters':'\u004E\u24C3\uFF2E\u01F8\u0143\u00D1\u1E44\u0147\u1E46\u0145\u1E4A\u1E48\u0220\u019D\uA790\uA7A4'},
  {'base':'O', 'letters':'\u004F\u24C4\uFF2F\u00D2\u00D3\u00D4\u1ED2\u1ED0\u1ED6\u1ED4\u00D5\u1E4C\u022C\u1E4E\u014C\u1E50\u1E52\u014E\u022E\u0230\u00D6\u022A\u1ECE\u0150\u01D1\u020C\u020E\u01A0\u1EDC\u1EDA\u1EE0\u1EDE\u1EE2\u1ECC\u1ED8\u01EA\u01EC\u00D8\u01FE\u0186\u019F\uA74A\uA74C'},
  {'base':'P', 'letters':'\u0050\u24C5\uFF30\u1E54\u1E56\u01A4\u2C63\uA750\uA752\uA754'},
  {'base':'Q', 'letters':'\u0051\u24C6\uFF31\uA756\uA758\u024A'},
  {'base':'R', 'letters':'\u0052\u24C7\uFF32\u0154\u1E58\u0158\u0210\u0212\u1E5A\u1E5C\u0156\u1E5E\u024C\u2C64\uA75A\uA7A6\uA782'},
  {'base':'S', 'letters':'\u0053\u24C8\uFF33\u015A\u1E64\u015C\u1E60\u0160\u1E66\u1E62\u1E68\u0218\u015E\u2C7E\uA7A8\uA784'},
  {'base':'T', 'letters':'\u0054\u24C9\uFF34\u1E6A\u0164\u1E6C\u021A\u0162\u1E70\u1E6E\u0166\u01AC\u01AE\u023E\uA786'},
  {'base':'U', 'letters':'\u0055\u24CA\uFF35\u00D9\u00DA\u00DB\u0168\u1E78\u016A\u1E7A\u016C\u00DC\u01DB\u01D7\u01D5\u01D9\u1EE6\u016E\u0170\u01D3\u0214\u0216\u01AF\u1EEA\u1EE8\u1EEE\u1EEC\u1EF0\u1EE4\u1E72\u0172\u1E76\u1E74\u0244'},
  {'base':'V', 'letters':'\u0056\u24CB\uFF36\u1E7C\u1E7E\u01B2\uA75E\u0245'},
  {'base':'W', 'letters':'\u0057\u24CC\uFF37\u1E80\u1E82\u0174\u1E86\u1E84\u1E88\u2C72'},
  {'base':'X', 'letters':'\u0058\u24CD\uFF38\u1E8A\u1E8C'},
  {'base':'Y', 'letters':'\u0059\u24CE\uFF39\u1EF2\u00DD\u0176\u1EF8\u0232\u1E8E\u0178\u1EF6\u1EF4\u01B3\u024E\u1EFE'},
  {'base':'Z', 'letters':'\u005A\u24CF\uFF3A\u0179\u1E90\u017B\u017D\u1E92\u1E94\u01B5\u0224\u2C7F\u2C6B\uA762'},
  {'base':'a', 'letters':'\u0061\u24D0\uFF41\u1E9A\u00E0\u00E1\u00E2\u1EA7\u1EA5\u1EAB\u1EA9\u00E3\u0101\u0103\u1EB1\u1EAF\u1EB5\u1EB3\u0227\u01E1\u00E4\u01DF\u1EA3\u00E5\u01FB\u01CE\u0201\u0203\u1EA1\u1EAD\u1EB7\u1E01\u0105\u2C65\u0250'},
  {'base':'b', 'letters':'\u0062\u24D1\uFF42\u1E03\u1E05\u1E07\u0180\u0183\u0253'},
  {'base':'c', 'letters':'\u0063\u24D2\uFF43\u0107\u0109\u010B\u010D\u00E7\u1E09\u0188\u023C\uA73F\u2184'},
  {'base':'d', 'letters':'\u0064\u24D3\uFF44\u1E0B\u010F\u1E0D\u1E11\u1E13\u1E0F\u0111\u018C\u0256\u0257\uA77A'},
  {'base':'e', 'letters':'\u0065\u24D4\uFF45\u00E8\u00E9\u00EA\u1EC1\u1EBF\u1EC5\u1EC3\u1EBD\u0113\u1E15\u1E17\u0115\u0117\u00EB\u1EBB\u011B\u0205\u0207\u1EB9\u1EC7\u0229\u1E1D\u0119\u1E19\u1E1B\u0247\u025B\u01DD'},
  {'base':'f', 'letters':'\u0066\u24D5\uFF46\u1E1F\u0192\uA77C'},
  {'base':'g', 'letters':'\u0067\u24D6\uFF47\u01F5\u011D\u1E21\u011F\u0121\u01E7\u0123\u01E5\u0260\uA7A1\u1D79\uA77F'},
  {'base':'h', 'letters':'\u0068\u24D7\uFF48\u0125\u1E23\u1E27\u021F\u1E25\u1E29\u1E2B\u1E96\u0127\u2C68\u2C76\u0265'},
  {'base':'i', 'letters':'\u0069\u24D8\uFF49\u00EC\u00ED\u00EE\u0129\u012B\u012D\u00EF\u1E2F\u1EC9\u01D0\u0209\u020B\u1ECB\u012F\u1E2D\u0268\u0131'},
  {'base':'j', 'letters':'\u006A\u24D9\uFF4A\u0135\u01F0\u0249'},
  {'base':'k', 'letters':'\u006B\u24DA\uFF4B\u1E31\u01E9\u1E33\u0137\u1E35\u0199\u2C6A\uA741\uA743\uA745\uA7A3'},
  {'base':'l', 'letters':'\u006C\u24DB\uFF4C\u0140\u013A\u013E\u1E37\u1E39\u013C\u1E3D\u1E3B\u017F\u0142\u019A\u026B\u2C61\uA749\uA781\uA747'},
  {'base':'m', 'letters':'\u006D\u24DC\uFF4D\u1E3F\u1E41\u1E43\u0271\u026F'},
  {'base':'n', 'letters':'\u006E\u24DD\uFF4E\u01F9\u0144\u00F1\u1E45\u0148\u1E47\u0146\u1E4B\u1E49\u019E\u0272\u0149\uA791\uA7A5'},
  {'base':'o', 'letters':'\u006F\u24DE\uFF4F\u00F2\u00F3\u00F4\u1ED3\u1ED1\u1ED7\u1ED5\u00F5\u1E4D\u022D\u1E4F\u014D\u1E51\u1E53\u014F\u022F\u0231\u00F6\u022B\u1ECF\u0151\u01D2\u020D\u020F\u01A1\u1EDD\u1EDB\u1EE1\u1EDF\u1EE3\u1ECD\u1ED9\u01EB\u01ED\u00F8\u01FF\u0254\uA74B\uA74D\u0275'},
  {'base':'u','letters': '\u0075\u24E4\uFF55\u00F9\u00FA\u00FB\u0169\u1E79\u016B\u1E7B\u016D\u00FC\u01DC\u01D8\u01D6\u01DA\u1EE7\u016F\u0171\u01D4\u0215\u0217\u01B0\u1EEB\u1EE9\u1EEF\u1EED\u1EF1\u1EE5\u1E73\u0173\u1E77\u1E75\u0289'},

var diacriticsMap = {};
for (var i=0; i < defaultDiacriticsRemovalMap .length; i++){
  var letters = defaultDiacriticsRemovalMap [i].letters;
  for (var j=0; j < letters.length ; j++){
    diacriticsMap[letters[j]] = defaultDiacriticsRemovalMap [i].base;

Now you can also add the function that will do the actual work of replacing the diacritics from a string passed as a parameter.

* Removes and replaces special characters in a string to make them
* acceptable for an email address
* @param {string} str The string to work on
* @return A formatted string without any special characters
function removeDiacritics_ (str) {

  // Replace all special characters in Latin basic set
  // See this page for a detailed list of codes 
  str = str.replace(/[\u0000-\u002F\u003A-\u0040\u005b-\u0060\u007B-\u007E]/g, '');
  // Replace all accented characters based ont the dictionary
  return str.replace(/./g, function(a){ 
    return diacriticsMap[a] || a; 

Last but not least, you now need to replace these lines in the EMAIL function

if(firstName != ""){
  first = firstName;
if(lastName != ""){
  last = lastName;

by these lines

if(firstName != ""){
  first = removeDiacritics_(firstName);
if(lastName != ""){
  last = removeDiacritics_(lastName);

You can now save and go back to your sheet and take a look at the cell where you had entered =EMAIL("Jo", "Nüßli", "", "_"). If all goes well, you should now see the special characters removed and a properly formatted email address like this

Putting it together

Now that our custom functions are ready to be used, we can start populating our spreadsheet with all our names and add the formulas PASSWORD and EMAIL in the right columns to generate our list of users.

The full code example can be found on this GitHub Gist. The Custom Email and Password Functions Demo Spreadsheet is also available publicly. Just open it, make a copy and start playing around.

G Suite Admins

If you are a G Suite Admin, this template is compatible with the bulk-upload of users in the Admin Console. Just download it as a CSV and import it into G Suite.

Note: For some reason, when uploading to G Suite in bulk, the first and last names that contain accents are not converting properly, so there is some edit work to do afterwards.


This is a first step into the vast possibilities that Google Sheets and more generally Google Apps Script offer. There are for sure areas for improvements in this script such as splitting the code into separate files. You can do that to keep your code clean, for example by creating a new script file for each custom function. Also take a look at versioning within Google Apps Script in order to keep track of the changes you made to your code.

Now it’s your turn, play around and make improvements to the code if you wish!