Generate Short Random Codes or IDs
Learn how to generate short random codes or IDs using Google Sheets and Apps Script, ensuring uniqueness by checking for duplicates. This tutorial covers creating custom menus and functions for flexible code generation.
You want a short ID code, something like a few letters, maybe a number , maybe I'll show you how this, what I'm about to show you is gonna be very useful for any amount of characters, any num amount of numbers. We're going to start over here at Extensions app Script , and this is going to help us both generate the code and check for duplicates so we know that it's unique . In a previous video I showed you how to create a unique code using UUID, which is like a really long string of numbers and we just sort of generally know that this unique ID is unique . Um, but what about short codes? Well, even if we randomize creating these characters, picking one through 26 right? Or zero through nine, um, we don't really know if it's.
Exactly a duplicate unless we check. So we're gonna be checking in this video, so let's name our project. Generate short code. We're gonna be doing a few different things. So let me write out some, uh, pseudo code, sort of fake code just describing what I'm gonna do. And, uh, we'll do those in, not necessarily order, but we'll do them all. We're gonna create, um, custom menu so that our user, maybe us could have a button up here and generate that number . We're going to generate. A unique , sorry, not a unique one, just a code with X characters. So X number of characters, just a certain length. That could be 2, 3, 4, 5. It could be anything. And we're also gonna generate a code with Y numbers, so that could be one, number ,
two numbers, three numbers, whatever. It's gonna be a randomized thing. And then we're gonna come back up here, or we can put it anywhere really. And we're gonna generate a code and check for duplicates if there's any matches. So we can do that one at the end here. So let's create the custom menu . It's really simple because we can go to better sheets.co/snippets. Look for custom menu . We have a couple options here. Let's just use one function . We just copy and paste this. This is open to everyone, no membership needed. By the way, you can become a member of Better Sheets for nine bucks a month. But this is open to everyone Better sheets.co/snippets, and we're gonna create this function on open. It's a custom menu . We can name this, our menu, and we're going to have a user readable name here.
Create code. And we're just gonna call create code. So we don't need our function up here. We can start at the top, and this will be this last one here. So it'll be function , create code, and we'll have a code down here that we'll create a code. Let's get these two first done because we need to call them, uh, we need to generate a code of X character, number of characters, and a number of numbers. So let's do that. So let's call this function , generate characters, and we're gonna put some kind of count in here as a variable. We can use any number here, 1, 2, 3, 4, 5 of any number . We'll have Ks characters and those are gonna be 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. We are gonna let the result at first be nothing. Just two quotes here, two single quotes, and then four, we'll create
a four loop be I equals zero. We'll start out with, with and I is less than the count, and that means that it's just gonna go until we have this many numbers. An I plus plus means we're gonna iterate. Sorry, one time, uh, add one each time to this. I iterator. So our result, this plus equals means we're gonna take our existing result and we're gonna add to it. We're gonna add characters, which is one of these up here, and we're gonna pick character at. This is a function that says. Hey, just pick this character. If we put one, it would be the number zero, the first one, two as, et cetera, et cetera. But we want a random one. So we'll do math dot floor in parentheses.
We'll do math dot random parentheses, and we'll do times. Characters length. So what this means is we're taking a random, a random number we're generating is always between zero and one. It's like a a 0.0 1, 2, 3, 5, 6, 7. A bunch of random numbers here. We're multiplying it by however many we have. So in this case we have 10 numbers, but we could, we could put a 10 here. However, putting this character at length means that we could add characters to this. So I'm gonna separate numbers and and letters, but you don't necessarily have to. If it's like, Hey, we want a certain number of characters, like A and D to be randomized or E and F, maybe four characters.
We can put those here. Or we, we don't want any zeros. We can take delete zeros. Now you can edit this and customize this characters for whatever characters you want. Make sure at the end you have. Return result so that we actually give back when we execute this I, um, function , we actually give back something. And now we're gonna do exactly the same thing here with num uh, sorry. That was characters. That was supposed to be characters. So we can do a, B, C-D-E-F-G-H-I J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z. Now let's copy and paste this and do the same with numbers. Zero. 1, 2, 3, 4, 5, 6, 7, 8, 9. And again, if we don't want zeros, we're like, action.
Our IDs never have a zero in it. Delete the zero. So we have 1, 2, 3, 4, 5, 6, 7, 8, 9. Great, right? Or if you're like, Hey, a lot of numbers. This is actually a really interesting use case that I'm about to tell you is this will be an equal amount. Of 1, 2, 3, 4, 5, 6, 7, 8, 9. But if you're like, actually we want more one, twos and threes than other numbers. So you can write 1, 2, 3, 1, 2, 3, 1, 2, 3, and now you have four times the possibility of having the number one in your randomization. I think this is a really cool little feature that you might not, it might not be intuitive that you can do this, but having this times characters length means that we just change our character count. Change our characters for whatever we want.
Maybe we do want letters and numbers in the same thing. It's okay. All right. Let's create our duplicate checker and actually generate a code here. We can even test this out by the way, before we move on. We can test, test. We can save and we can say, Hey, this function can actually be inside of a cell. Now we can go equals generate characters. And I just need to put a number of characters here because that's gonna be our count. Hit enter. And that's five numbers. Oh, because we have, we need to change this to generate numbers, not generate characters. There we go. Now we have five unique characters here. Cool. So we know it works. If we add any errors, we could go through those errors. Maybe we spelled something wrong, maybe we have a capitalization wrong.
Make sure you capitalize math, for example, make sure you have this count here and the count. For example. All right, let's move on to creating the code, the total code with a specific size, right? Like maybe it's three letters, hyphen and a number . And I'm gonna put back zero here, but just wanted to show you that you can edit this characters, so we need to know where we are. And what we're gonna do is not create a function that we can put this inside of a cell. We wanna execute it from our menu. So we need to know where we are, which is variable. SS equals spreadsheet , app dock, get active spreadsheet , get active sheet . The active spreadsheet is the entire file, but the active sheet is the tab we're on. And let's find the row equals ss dot get active range.
Do get row so that we know, we know what row we're on. Let's get our let's code of characters. Which is generate characters and we want three characters. Variable number equals generate numbers. We only want one. These numbers within the parentheses is up to you. Whatever your ID is, syntax what? Whatever the syntax ID is. Now we need to know where to check. So we're gonna say variable codes to check equals ss dot get range . And for our purposes, they're all in the A column, get values flat just to get an easily searchable set of codes, which is again, this a column. We don't want a unique code.
Sorry. We do want a unique code. We don't want a code that already exists and our code is gonna be equal to code, which is this variable up here, which is only gonna be three random characters. Plus we're gonna have an hyphen, and in this case, this plus is concatenating this together. It's combining these strings. We're gonna get three characters here, plus one character of a number . And again, that could be numbers, it could be short code, it could, you could have a BC there as well. Up to you what you have available. Okay. Now how do we check? Well, we're gonna do something called a do while, so we're gonna have do this while Wow. And then we're having a true or false statement here. So we're gonna keep doing something while this is here, and then basically
we say, okay, make the code, and then if it includes a code, keep trying. Do it again. If it doesn't include the code, if this is false, we're done. Return code. So let's move our code here. So just formatting this a little bit differently, but we're gonna do our code, create that code, and if codes to check includes code. So this is saying, Hey, does this code exist in codes to check? And if it does, do this again. If it does not, we're gonna return the code. Pretty cool, right? And again, we don't want to reference this function inside of a cell. We have it up here in our original sheet. We do not have a custom menu yet. We need to save our script. We need to save our script and close our script and refresh
our sheet or open it again. Close it and open it again. When we do that, let's get rid of this accessibility menu. We have our menu, and there's only one thing here, which is called create code. It's gonna look at our row and find the row, and it should put a new code in the A column. So let's do that instead of returning the result, we could do this to test it out. Sorry. Returning this code. We don't wanna return the code. We want to do spreadsheet app, which is actually SS do get range . The row, we're on the first column set value code, so our result is going to be, we're gonna write that code to the A column, so our menu create code authorization will be required the very first time we run this. There we go. Then in the next one, create code there every time.
Now, what's really cool is this end result. We could do return code and then use this create code in say, a pending. Something like maybe append a row here with the new employee and the id. You could do it in a number of different ways, but setting that code could be up to you how you want to do it. But right now I'm gonna take pick a row, our menu create code. Even if I put it on the employee name, it's still gonna write to that first column. Oh, we missed Heather. Let's do that. Pretty cool. Right? And I just wanna show you how we can edit these characters. Like instead of having numbers at the end, if we wanted to include not a zero, so we'll delete zero and wanna say A B, C is also included and our
characters could include maybe, maybe we don't include letters like I. Or O, that could be mistaken for numbers, but we do put numbers that are not easily mistaken, like 2, 3, 4, 5, 6, 7, 8, 9. We'll save all of that, and we don't have to change our character length. We don't have to change anything else. We just say, here's the available letters or numbers. Maybe we say A, B, C, A, B, C to have three times the chance of getting those letters. So let's generate a code. There we go. We got some pretty funky codes now, right? But if you're like, Hey, actually our code has to end with M. Our number has to be the number one. Well, we can also change it over here in our create code. We can say, Hey, the number always has to be the number one one.
And the code has to be plus actually, no, no. Plus just M1. And our code up here generate characters. Maybe it's only two. We want unique codes. Let's do, let's delete these and start generating these codes. There ends with an M1. And it's two letters. Create a code and ideally it's not going to be the same. It's always gonna be unique . Sometimes as we get longer and longer, especially 'cause there's only two characters, it's probably is going to have some, uh, of the same, a lot. It might take longer and longer, a few moments instead of immediately to execute because it's checking all of these. That's one thing to look out for is the code might take a bit of time to go and with only two characters, if you're only generating
a few, a couple dozen, it's fine. But if you're trying to generate thousands with only two numbers, two letters difference, it's uh, you're gonna have 26 times 26, 676 possibilities, right? So that's your limit . You can't have that many, um, codes. But I think this is a pretty cool way to create short codes with very specific instructions, right? Very specific set of potential characters, numbers, and create those codes and check them with this. Do while. If you are struggling with keeping up with the app script , I do recommend my Udemy course spreadsheet Automation 1 0 1. Actually on Udemy, it's Master Spreadsheet Automation, but over on Better sheets.co. It's called Spreadsheet Automation 1 0 1. I go deep into exactly what all of this code means and how to read it so you can
even use this, plus use AI to actually generate code for you and know how to read it and know how to edit it all the time. So check that out. I'll put a link in the description. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.