Thanks for coming back for Part 2 of Automating Sports Data with Google Sheets. In Part 1, we went over our Google Sheet template and what each tab was for, built our player selection tool and set up our sheet to pull in the game log of any selected player. In Part 2, we’ll take the data from the game log we now have access to and build out our Player SOG Consistency Chart.


First, let’s build out our Year to Date (YTD) stats. While on the Dashboard tab, select cell D6, and enter the following formula:

=COUNTIF(Data!$B$7:Data!$B$90, “>=1”)

TIP: Each NHL season has 82 games, but in the formula above, we’re looking at up to 84 rows of data (many rows will be blank, and that’s not an issue). While it’s unlikely any player would play more than 82 games, it is possible with a mid-season trade at the right time that a player could see an extra game or two.

The COUNTIF function allows us to count if a cell matches a particular condition. In this case, we want to know how many games our selected player has played in this season. The first column of our imported data is Game #, so any cell located in column B, between rows 7 and 90, that contains a number larger than 0 indicates a game played for our selected player. The “>=1” part of our formula checks if the Game # is greater than or equal to 1.

Next, we’ll move to cell E6, and in the formula bar, enter the following. Note: Google Sheets may ask you if you want to autofill your formula – we do not want to do that here.

=COUNTIF(Data!$I$7:Data!$I$90, “>=2”)/D6

This formula works basically the same as our Games Played formula, except this time, we’re counting the shots in column H from the data tab, and dividing it by the total number of games played. In cells F6, G6 and H6, copy and paste the same formula into the formula bar, but you’ll want to change the “>=2” to match the number of the column you’re pasting it in (>=3 in 3+, etc). And that’s the first row done!

When we used the QUERY function earlier on, we had it sort the imported data by most recent game, so the top 10 games of the data tab are the player’s 10 most recent games (rows 7 through 16). We’ll use the same formula that we used for the YTD row.

=COUNTIF(Data!$I$7:Data!$I$16, “>=2”)/10

Once again, do the same for the cells F-H, where you’ll want to change the “>=2” to match the number of the column you’re pasting it in (>=3 in 3+, etc). Do the same with the L5 row, using the formula below. That should be two more rows done!

=COUNTIF(Data!$I$7:Data!$I$11, “>=2”)/5

Next, we’re going to look at how the player performs in Home and Away situations. Select cell D9, and enter the following in the formula bar.

=COUNTIFS(Data!$B$7:Data!$B$90, “>=1”, Data!$J$7:Data!$J$90, “=HOME”)

In D10, do the same thing, but switch =HOME with =AWAY. COUNTIFS counts two conditions in two different data ranges. The first half is the same as what we did for our YTD Games Played column. The second half looks at whether it says HOME or AWAY in column J of our Data tab. COUNTIFS returns the total number of rows that match both criteria. This provides us with the total number of games played, both at home, and on the road.

 

The last thing we need to do is build out our Home/Away SOG consistency tabs. Select cell E9, and enter the following into the formula bar:

=COUNTIFS(Data!$I$7:Data!$I$90, “>=2”, Data!$J$7:Data!$J$90, “=HOME”)/D9

This formula counts every row where column J equals HOME and where column I, our Shots column, is greater than or equal to 2, and divides it by the total number of Home games. To finish the row, copy the formula in cells F9-H9, increasing the “>=2” by one in each cell.

Finally, we’re going to repeat everything we did in the last step for away games. Paste the following formula in cells E10-H10, increasing “>=2” to match the column heading.

=COUNTIFS(Data!$I$7:Data!$I$90, “>=2”, Data!$J$7:Data!$J$90, “=AWAY”)/D10

And that finishes our Player SOG Consistency Chart. To see this chart for a different player, all you need to do is select a different player using the player selection tool.

I hope this series has given you an overview of some key concepts that you can use to build out your own cheat sheets. If you have questions, or suggestions to improve this article, drop them in the comments below.

If you found this interesting, but would prefer to use existing tools rather than make your own, there are a ton of options in the hockey space (my favourites are Props.cash, Pine Sports and Linemate).