The Scenario
You are a football analytics coach at a small program. Your defensive coordinator just watched the 2023 College Football Playoff national championship game and wants to know what Georgia's offense actually ran in the first half — down-and-distance distributions, play types, yards-per-play by quarter. He wants the data by tomorrow morning's staff meeting.
It is 5 PM. The staff meeting is at 8 AM.
The bad version:
- You find the play-by-play endpoint in the CollegeFootballData.com API docs
- You realize you need the game ID, which you have to look up separately
- You find the game ID, hit the endpoint, get 170+ plays back in nested JSON
- You write a script to extract down, distance, play type, yards gained, and offensive team
- The play type field has 15 different values — "Rush," "Pass," "Scramble," "Sack," "Penalty" — and you need to normalize them for analysis
- It is midnight. Your coordinator has the data but no analysis. You present nothing useful at 8 AM.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI finds the game ID, pulls the plays, and normalizes play types in one go.
Open the SheetXAI sidebar and type:
Fetch all plays from the game with ID in cell B1 using College Football Data and write down, distance, play type, yards gained, and offense team into this Google Sheet.
If you do not have the game ID, ask first:
Look up the 2023 College Football Playoff national championship game ID from College Football Data and put it in cell B1. Then fetch all plays from that game and write down, distance, play type, yards gained, and offense team into column A through E starting at row 2.
SheetXAI looks up the game ID, pulls all plays, and writes the full play log. 170 plays, clean columns, ready for the morning analysis.
What You Get
A play-by-play log with one row per play:
- Down and distance — the decision context for every play
- Play type — normalized categories (Rush, Pass, Penalty, Special Teams, etc.)
- Yards gained — the result of each play
- Offense team — for filtering to one side of the ball
The raw play log is analysis-ready. Pivot by down to see first-down play-call tendencies. Filter by quarter to see how the game plan evolved. Sort by yards gained to find the explosive plays.
What If the Data Is Not Quite Ready
Play-by-play data is granular and full of edge cases. SheetXAI handles them.
When play types are too granular and need grouping
The API returns "Rushing Touchdown," "Pass Incompletion," "Pass Reception," "Sack," and "Scramble" as separate categories. You want four buckets: Run, Pass, Penalty, Other.
In this Google Sheet, add a column called "Play Category" that groups the play type into four categories: Run (Rush, Rushing Touchdown, Scramble), Pass (Pass Reception, Pass Incompletion, Passing Touchdown, Sack, Interception), Penalty (Penalty), and Other (everything else). Then calculate yards-per-play by category in a summary tab.
When you want to filter to first-half plays only
Your coordinator's specific question is about first-half offensive tendencies.
Filter this play-by-play sheet to plays in quarters 1 and 2 only. Copy those rows to a new tab called "First Half." In the First Half tab, show the percentage of Run plays vs. Pass plays on first down, second down, and third down using a summary table.
When you want to add PPA alongside each play
The play-type and yards-gained columns tell you what happened. PPA tells you whether it was actually effective.
Fetch all plays from the game in cell B1 with PPA included for each play. Write quarter, down, distance, play type, yards gained, and PPA into this Google Sheet. Then in a summary tab, show average PPA by play type and average PPA by down.
When you want to compare two games from the same team
Your coordinator wants to compare the championship game to Georgia's semifinal — same offense, different defensive opponent — to isolate scheme versus execution.
Fetch all plays from the 2023 CFP semifinal game for Georgia from College Football Data. Write them into a tab called "Semifinal" with the same columns as the championship tab. Then in a third tab called "Game Comparison," calculate yards-per-play by play type for each game and show them side by side.
The pattern: the play log is the raw material. Every coaching question is a filter or a pivot on the same data. Ask SheetXAI to do both at once.
Try It
Get the 7-day free trial of SheetXAI and ask it to pull play-by-play data for any game ID. The CollegeFootballData.com integration is included in every SheetXAI plan. For more, see how to export game-level PPA metrics or the CollegeFootballData.com in Google Sheets overview.