Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

randalpha examples = Google Sheets

Google Sheets, Google Apps Script

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

RANDBETWEEN Google Sheets

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

 

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

The RANDALPHA function.

The RANDALPHA function is a custom function that you can call up just like a normal function in your Google Sheets (once you have added it to your sheet).

As the name suggests, RANDALPHA can generate 3 different types of random codes at a character length of your choice. To create a random code you simply input the length and choose from one of three different randomisation options.

Input is as follows:

=RANDALPHA(character length, type of random number)

or:

=RANDALPHA(10, 0)

Resulting in a 10 character length random string of Alphabetic Only text:

QtqjsyWsjj

Type of Random Number

There are three types of random numbers that you can add to the second argument of the RANDALPHA function (0,1 or 2):

  1. Alphabetic: the function will generate a random string of alphabetic characters with both upper and lower case letters.

Random Alphabetic Google Sheet

  1. Alphanumeric: the function will generate a random string of alphabetic and numeric characters.

Random Alphanumeric Google Sheets

  1. Alphanumeric + Characters: the function will generate a random string of alphabetic, numeric values along with characters.

Random Alphanumeric and Characters - Google Sheets

Purely Uppercase or Lowercase Characters

If you just want to make your random code uppercase or lowercase you can simply add the Google Sheet Functions UPPER or LOWER and wrap RANDALPHA inside it like this:

RANDALPHA UPPER LOWER - Google SheetsError Handling

If you make an error by adding a non-numerical value to one of the two arguments or attempt to choose a type of random number greater than or equal to 3 then you will get the following errors.

RANDALPHA Error Handling - Google Sheets

Warning

  1. There are no outer limits. You could make your character length any number or characters in length, but if you create enormous codes like something crazy like 10^100, it’s probably going to break or put your spreadsheet in limbo.
  2. Custom functions run from the server-side not the user-side therefore they will be slower to generate the function values.

Adding RANDALPHA to your Spreadsheet

  1. Open up the Google spreadsheet you wish to add RANDALPHA function to.
  2. In the Menu Bar go to Tools > Script editor
  3. In the top right-hand corner there will be a title for the Project. Rename it to RandAlpha or the title or your spreadsheet or whatever you want…
  4. In the Code section, delete out all existing code.
  5. From the code below in this post copy all the code.
  6. Paste the code in the Script Editor
  7. In the Script Editor, go to File > Save 
  8. Close the Script Editor
  9. In your spreadsheet test the RANDALPHA  custom function.

RANDALPHA installation

The Code

 

 

2 thoughts on “Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions”

Leave a Reply