The Scenario
You are a sports data journalist. You are writing a piece on dynasty-level dominance in college football: Alabama, Ohio State, Georgia, and Michigan across the 2010–2024 seasons. Your angle is Elo ratings — a measure of team strength that updates after every game and accounts for opponent quality.
You need fifteen seasons of Elo ratings for four programs in a single Google Sheet. That is sixty data points if you pull season-end ratings only, more if you want weekly snapshots.
It is Monday morning. The piece publishes Friday.
The bad version:
- You find the Elo endpoint, pull it for one team and one year
- You realize you need to loop across four teams and fifteen seasons
- You write the loop, hit the API sixty times, aggregate the results
- The column format differs slightly between the year the API was updated and earlier years
- You spend Tuesday fixing the format inconsistencies instead of writing
- You file a shortened version on Friday that only covers 2015 onward.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI handles the multi-team, multi-season loop and normalizes the format.
Open the SheetXAI sidebar and type:
Fetch Elo ratings for Alabama, Ohio State, Georgia, and Michigan for every season from 2010 to 2024 and write year, team, and Elo rating into this Google Sheet.
SheetXAI calls the API for each team-year combination, normalizes the output, and writes sixty rows into the sheet. Sorted by year and team. You are building the chart by Monday afternoon.
What You Get
A clean table with sixty rows:
- Year — 2010 through 2024
- Team — Alabama, Ohio State, Georgia, or Michigan
- Elo rating — end-of-season strength rating accounting for schedule and results
The table is chart-ready. Add a line chart with year on the x-axis, Elo on the y-axis, one line per team, and the dynasty story is visible immediately.
What If the Data Is Not Quite Ready
Longitudinal Elo analysis usually surfaces follow-up questions. SheetXAI handles them.
When you want FPI ratings alongside Elo
The piece uses both Elo (historical) and FPI (ESPN's power index) to triangulate dominance.
Pull Elo and FPI ratings for each team in column A of my sheet for the seasons listed in column B and write the ratings into columns C and D.
When you want to add national rank per season
The raw Elo number is less intuitive than the rank. You want to know where each program sat relative to the rest of college football each year.
For each row in this sheet, calculate the national Elo rank for that team in that year — how many FBS teams had a higher Elo rating at season end. Write the rank into a new column called "National Rank." Then add a second column showing whether the team was in the top 5 (yes/no).
When you want to extend the analysis to all CFP semifinalists
Your editor wants to expand the piece to cover every team that made a CFP semifinal from 2014 to 2024, not just the four you started with.
Fetch end-of-season Elo ratings for every team that appeared in a CFP semifinal from 2014 through 2024. Write team, year, Elo rating, and CFP round reached (semifinal or final) into a new tab called "CFP Programs." Sort by year descending, then by Elo descending within each year.
When you want a rolling 5-year average Elo to smooth out outlier seasons
A single bad season can distort an otherwise dominant run. You want a smoothed metric for the narrative.
In this Google Sheet, add a column for 5-year rolling average Elo for each team (using the current row and the four prior years). If fewer than 5 years of data exist for a team at a given row, use whatever years are available. Label the column "5-Yr Rolling Avg Elo."
The pattern: the sixty-row pull is the foundation. Every analytical question builds from it without another API trip.
Try It
Get the 7-day free trial of SheetXAI and ask it to pull Elo or FPI ratings for any set of programs across any span of seasons. 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.