It’s the holiday Christmas season and I just thought it would be really cool to create a giveaway in a Google Sheet.
Now, however you determine to get names or usernames may be Twitter handles into a Google sheet. That's up to you. You can use a Google form. You can copy and paste if it's something like people who are commenting or retweeting. But yeah, any way you get them in here.
We have a list. We want to get a random person from this list. We want to get a winner.
One way you could do this is by doing a formula with the Rand function.
I'm not going to go into it in this one because I found a really cool piece of code in Apps Script that can shuffle an array. I wanted to show you how to do that in Google Script or Apps Script.
Here we have the whole code:
I want to walk through this and share with you how you can use this yourself. You can literally get this code in this sheet. We just grab the active sheet, we make sure we get the sheet name, which is the Sheet1 tab.
The active sheet gets the last row that there is data. In this tutorial, it'll be row 10.
Now what it does is it then gets all the values and it creates what, what we call a shuffleArray.
Using Math.random in Google Apps Script
This is the code that I found online, which is really cool. It's a pretty simple little for loop that uses that Math.random, which is exactly the same as that Rand formula. But here we're doing it in Google Apps Script, this Math.random.
It’s creating a random array here, in the part where it says:
j = Math.floor(Math.random() * (i + 1) );
The temp part in this script is where we move that person out there and replace it with someone else. Then we put in temp and we return the entire array. Now it’s shuffled.
Basically, each person's name here that we're going to get 1 through 10 here is put into an array and then shuffled. Then we get back the array.
Then from that array, the only thing we need to do is get the first name, which is this bracket notation.
We use zero because in an array, zero is the first item in the array.
We return back just the first item in the shuffled array. So that could be the first person, right? The first person here absolutely could be the second person or the third person.
It is literally random as to the placement of who is the first person on the shuffled array.
The getWinner function
What we need to do is just call this getWinner function.
So if you grab this script from the sheet or you copy the sheet and then you enter all of the names here, the only thing you need to do is this formula:
You will get this red line that says unknown function, but just hit enter. Make sure you have a capital W when you type in "getWinner."
And here we go. We got Camden Hall!
What if you want more than one winner?
We need to have a header, like “Names/Entrant.”
So what we're looking at is a range or a list of names starting on the second row in the first column that's as large as there are rows minus one. And then one column.
So that’s good. Let’s double check. Enter the getWinner formula.
Maybe you have some kind of entry or giveaway or a raffle that you can give away multiple prizes so you don't take anyone out. That’s fine.
So we have four winners here, right in that order.
Where else can I use this?
And so this getWinner formula allows you to shuffle those names and get a winner. And I think there's a pretty cool thing. We can also use this in giveaways.
We can do this in even a task management. Sometimes we want to do a task or we have a large array of task or to-do list that's quite long and we're like, “Oh, where do we start?” I think that would be a fun implementation.
Also, you can put a list of names of people who need to do tasks or a priority queue if you have things coming in and you don't want to do them in the order that they're coming in. You can pick a random person to take each one.
That might randomize things a little bit more instead of giving the first task to the first person, the second task to the second person, third task to the third person. Get a random one.
I think this is pretty cool. I hope you liked this tutorial and have gotten something out of it. I hope you have a better sheet after reading this. Don't make sheets. Make Better Sheets.
Watch the video for this tutorial:
Learn more fun Google Sheets tutorials:
Get more Google Sheets Tutorials at BetterSheets.co
We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets. For only $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.