Americano Tournament Template With Four Automations

Intermediate

About this Tutorial

Created a tournament template with 4 automations. So tabs can be copied by a laptop, ipad, or mobile.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

SpreadsheetApp Utilities

Apps Script methods used

.activate() .addItem() .addToUi() .copyTo() .createMenu() .getA1Notation() .getActive() .getActiveSheet() .getActiveSpreadsheet() .getSheetByName() .getUi() .formatDate()

Featured Formulas

SORT() SUM() SUMIF() UNIQUE()

Video Transcript

<div>00:00 So I'm creating a community sort of sheet for my community, a Google sheet for my community, where I'm going to be organizing Americano tournaments. 00:09 These are tournaments where eight people play and have seven matches. We have to play with each individual other player and against every other player twice. 00:18 So once with everyone and twice against everyone.

And there are apps that do this, but one weird thing that I wanted to do was as these tournaments unfolded like once a week, I wanted to capture the score each time. 00:33 And so capturing the score actually, I was like, oh, let's just do it in a Google sheet and I have all the name and the score, I'm going to use unique . 00:40 To grab all the names and I'm going to sort with the sort formula , right?

Automated scoring system, keeping track of every name here. 00:50 And I was like, wait, to get the scores though, I have to type the scores here from an app result. 00:56 So I was like, well, let me just make the tournament itself score. So this is for paddle, it's a sort of like a mini, not a mini tennis, it's sort of a different style of tennis and squash and stuff, like always played in doubles. 01:09 So you see court one here, court one here, round one is this player versus this player.

Now I have filled this with four different automations, one which is just formulas, two ah which is a ah custom menu up here, three it's a button, an image with a button here, and four I have on the settings a checkbox 01:30 that does the automation as well.

And I will explain why I do each and every one of these because ah they are all for different reasons, different people are using this kind of sheet all the time. 01:40 So one, I'm on my laptop, or rather a desktop computer, right, a laptop style computer, and I can do all of these. 01:48 Not every version of Google Sheets can do all of these automations, so I will explain which ones do which. Uhm, but first I want to walk you through the sheet, so, and the formula automation. 02:00 And this, you might not think is automation, but is pretty darn magical in Sheets.

So I have created a place where we create player names here, and we just add the player. 02:11 Just say, here's Andrew, here's Betty, here's Carl, and I don't know if you can see over on the right, Edna, Frank, Gary, and Hector. 02:25 But here on the right, all of these names are changing. So I do not have to copy-paste these names anywhere, I don't have to create the matchups. 02:31 The matchups are already created, here's Andrew and Betty, here's Andrew and Hector, here's Betty and David, they're all here.

I have quote-unquote automation. 02:40 I automated this with these formulas. And this is just formula references to the cells . That is it. I have created each of these rounds. 02:48 I did actually get a, not a random generator, but I did find an Americana generator.

I was working on this for quite a while, trying to generate these matchups myself with some JavaScript, and it just wasn't working so well. 03:00 So what I did is I just went onto the app, created one tournament, and then whatever format that was, I hardcoded each of these in. 03:08 And so now we can, but we can randomize this by taking this range here and go up to data and randomize range . 03:16 And now these cell references are going to stay.

But it's this range that's going that is randomizing and you see the matchups are now different. 03:24 But everybody plays with everybody once and against everybody twice. So you see Edna, David Hector, and you can always double check this. 03:32 It's always seven rounds if it's eight players.

With nine players it is one person is resting or out each round, but it is still a number of around nine rounds in fact this time. 03:42 Um, but again, everybody plays with everybody once and against everybody twice, but you also rest one of the ah rounds. 03:50 Well, we are creating these tournaments once a week or even twice a week maybe, and I didn't want to create as many tabs as we could. 04:00 We knew we would have, we would just create them on the fly, and that's where the automation comes in really handily. 04:05 I added an image where I inserted image over cells , and I just drew this, I actually created this from two shapes, a circle and a plus sign to have this thing. 04:16 And in my Apps Script , I have already created ready new tournament eight, which takes today's date, and it creates a copy of the eight template eight and adds new and then the date, so that we always have the date automatically there. 04:33 That function also is in this menu, actually I just added it two more, so let's refresh.

And when it opens, it will be new. 04:44 So, first I created this on open menu here, but this on open menu does not show up on mobile, and these images do show up on mobile, but these images do not have the assigned script and you actually can't click up an image , but if you say option, actually command, And, and just assign script here, you 05:02 can actually see it happening, we hit command, and then hit assign script, it's new tournament 8.

So, it's gonna run that function anytime it's clicked, and it's clicked and it did it right here. 05:15 But, this image shows up in mobile, but the button is not clicked. Clickable, so what is a clickable button to run an automation? 05:23 So, already we've automated the uh rounds, we can randomize these rows, but now we create a new tournament. On mobile, we may have to duplicate this, or uhm you know do those things. 05:40 Those steps by hand, rename it, put in the date, but uh we don't really want to.

We want to have an automation that just does it for us. 05:48 So what I did, is I created a settings page that has a checkbox , and now a checkbox is checkable on mobile. 05:57 And so, someone who wants to create a tem- uh copy of the template , and run this 8-player tournament, they can go to this checkbox , it checks, it will duplicate the template , rename it, and it will uncheck the box. 06:15 So we can look at our Apps Script again and see that. It's name already, this new, uh it will not, uh rename it. 06:46 But we can also, you can see, let's uh delete it or rename it.

If we rename it, it will happen. 06:54 But how this works is we use if statements to sort of gatekeep to sort of say, hey, if the active range is B2, meaning the, the, the checkbox we're checking, if it's B2 on the settings page, making sure the sheet spreadsheet dot app dot get active sheet dot get name is equal to settings, if it is, and 07:16 B2, then do this, which is going to be copy the input. Template eight, and then also uncheck the value here. 07:23 Active range . If it's B4, we're going to actually create template nine.

We're going to duplicate template nine, and then again, set value to false, so we're unchecking it. 07:33 This also is, you can do uncheck here. Think that'll work as well. Uncheck. So let's double check that the uncheck works. 07:46 It will create our copy, and uncheck it. Yep, and then say this was actually, you know, a week ago, or on the 15th, and now we're going to create a nine one, nine player one. 07:58 We click the button, it will create the copy, and rename it, and uncheck it. So we can use uncheck as well, or set value to false. 08:04 Either one works.

If you are a BetterSheets member watching this on BetterSheets, down below is this exact sheet with all of these functions , all the Apps Script waiting for you. 08:16 You can use this. It's really cool. Uh, I will walk through. One of these, let's say we have scores, just put the scores over here. 08:24 Maybe you have a scores out of 16, so it's like 12, 12, 4, 4, 12, 12, and 4, and 4, or 10, 6, sorry, 10. 08:36 You have to put the score for each individual person. It's just the way that I set it up.

So, it's a bit of doubling, but as you can see, as I type it, the leaderboard is um automatically sorted, right, and these scores are automatically calculated. 08:52 So, if we get to the end of the tournament, let's just put these randomly here, we see, oh, Gary wins. 09:00 So, let's see, can Gary, let's give him a little different score, there we go, by one point.

Gary wins by one point. 09:10 Uh, now, we can take either the leaderboard or this one, doesn't matter the order, and put it in monthly, just copy paste values , and now we automatically, these are, uh, if it's different people or it's the same folks each time, you can put their scores each time, and it will automatically sort , find 09:32 all the unique players, and then automatically sort them.

So, a lot of cool automations in this sheet, makes it super easy to use, again, I made it very easy very clear to be used on desktop, laptop, and mobile, maybe on iPad as well, if you can click the image buttons, great, if you can't click the  09:50 image buttons, we got you, we got you on the settings, we can create a new template , we can copy this template just by checking the box, that's really cool, and I hope you enjoyed this video. 10:00 This Americana tournament with four automations.</div>