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:
But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?
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 long 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):
- Alphabetic: the function will generate a random string of alphabetic characters with both upper and lower case letters.
- Alphanumeric: the function will generate a random string of alphabetic and numeric characters.
- Alphanumeric + Characters: the function will generate a random string of alphabetic, numeric values along with characters.
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:
Error 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.
Warning
- There are no outer limits. You could make your character length any number of 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.
- 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
- Open up the Google spreadsheet you wish to add RANDALPHA function to.
- In the Menu Bar go to Extensions > App Script
- 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…
- In the Code section, delete out all existing code.
- From the code below in this post copy all the code.
- Paste the code in the Script Editor
- In the Script Editor, click on Save Project
- Close the Script Editor
- In your spreadsheet test the RANDALPHA custom function.
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
/** * Creates a string of random letters at a set length. * * @param {number} len The total number of random letters in the string. * @param {number} num What type of random number 0. Alphabet with Upper and Lower. 1.Alphanumeric 2. Alphanumeric + characters * @return an array of random letters * @customfunction */ function RANDALPHA(len, num) { var text = ""; //Check if numbers if(typeof len !== 'number' || typeof num !== 'number'){return text = "NaN"}; var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;"; var charStringRange switch (num){ case 0: //Alphabet with upper and lower case charStringRange = charString.substr(0,52); break; case 1: //Alphanumeric charStringRange = charString.substr(0,62); break; case 2: //Alphanumeric + characters charStringRange = charString; break; default: //error reporting return text = "Error: Type choice > 2" } // for (var i = 0; i < len; i++) text += charStringRange.charAt(Math.floor(Math.random() * charStringRange.length)); return text; } |
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
This was super helpful and really easy to understand! Thank you!
Glad you found it useful
I need to generate 2500 unique 3 digit alphanumeric codes. Only using uppercase A-Z and numbers 1-6. Is there a code to do this in google sheets?
Hello Melany,
There is not built-in function for this in Google Sheets. However, a little while ago I wrote a tutorial on how to do this with Google Apps Script.
Google Apps Script – Create Custom Unique IDs in Google Sheets
~Yagi
Hi Yagi, Great script and really clear instructions – many thanks for providing the source. I might be missing something but couldn’t Melanie just edit the charString and charStringRange in your example above to achieve what was requested ?
Great script ! Thanks !
Thanks for the kind words.
Hello! first of all, thanks for sharing! I see the post is a little old, but I decided to give it a try and write.
I’m always getting NaN as a result when trying to use your script, any ideas in how did I do wrong?
Regards!
Hi Juan,
It should still work fine. What is your input into the custom function?
~Yagi
Try this….
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)
very useful indeed! Thx!
Great to hear, Lucas. Thanks!
I know this is a long shot but here goes. I have a sheet that users input requests via a google form, and I want to assign a unique ID to each row. Is there any way to apply this function to a column using the arrayformula function so that each cell gets a unique value without havin to apply the function to each row individually?
Hi Pete,
Unfortunately, as you have discovered this custom function doesn’t not work across a range using ARRAYFORMULA and will update when you open the Google Sheet. Have a look at these two options. They may be closer to what you are after:
1. Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time
2.Google Apps Script – Create Custom Unique IDs in Google Sheets
Hope this helps.
~Yagi
Thank you very much for sharing! this works for me.
However, I have an user onboarding Sheet having the randomized password in it, the problem is, the password generated by function will change itself after some time, how can we stop that?
this Sheet is also used by powershell for onboarding automation, if it changes, the password is not right for future reference.
I didn’t find a way to stop it from changing yet, hope you can shed some lights
Hi Jack,
Unfortunately, as you have discovered this custom function doesn’t not work across a range using ARRAYFORMULA and will update when you open the Google Sheet. Have a look at these two options. They may be closer to what you are after:
1. Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time
2.Google Apps Script – Create Custom Unique IDs in Google Sheets
Hope this helps.
~Yagi
WOW, thank you very much for the hints Yagi! onEdit is a good way to do it.
I just added your function to onEdit and it works great, won’t change anymore!
function onEdit(e) {
var row = e.range.getRow();
var col = e.range.getColumn();
var as = e.source.getActiveSheet();
if(as.getName() == ‘OnBoardingSheet’ && col==5){
as.getRange(row,5).setValue(RANDALPHA(15,2))
}
}
Thanks for sharing! It is very helpful. I have just used this on one of my projects.
That’s wonderful to hear. Thanks, Esther.