Guide
Fantasy football data in Google Sheets with Apps Script
Quick answer: In Apps Script, call UrlFetchApp.fetch() with your key in an x-api-key header (stored in Script Properties), make one /stats/{player_id}/2026 call per player to get weekly PPR points and snap share together, and write the rows with setValues. Add a Tuesday-noon trigger. A 15-player roster costs 15 requests a week, about 65 a month. /stats is a Basic endpoint ($9/month, 5,000 requests a month), so the sheet runs on Basic.
- Endpoints
- /stats, /snaps
- Language
- Apps Script
- Requests
- 15 a week
- Plan
- Basic
Why put fantasy data in Google Sheets?
Because the spreadsheet is where fantasy decisions actually get made. It's where your trade math lives, where your league-mates can see the numbers, and where you can sort, filter and chart without writing a front end. Copy-pasting box scores into it every Tuesday is the part nobody enjoys.
Google Apps Script fixes that. It's JavaScript that runs inside your spreadsheet, it can call any HTTPS API with UrlFetchApp, and it can run on a timer. In about 70 lines you'll have:
- a Roster tab where you type player IDs;
- a Usage tab that fills itself with each player's latest PPR points, snap share, snap trend and points per game;
- a custom function,
=GRIDIRON_SNAP("Rome Odunze#WR", 2), for any cell; - a weekly trigger that refreshes it all after Monday night's stats land.
It runs on Basic: /stats is not on the free plan. The budget math is below, because a spreadsheet that recalculates can burn requests faster than you'd think.
How do I set up the sheet and the key?
- Get a key on Basic ($9/month, 5,000 requests a month; see pricing) and copy it from your dashboard. A free key gets HTTP 403 on
/stats. - Create a spreadsheet with a tab named Roster. Put a header in A1 and one player ID per row from A2 down, in the API's
First Last#POSformat:Rome Odunze#WR,Aaron Jones Sr.#RB,Saquon Barkley#RB. - Open Extensions → Apps Script. In Project Settings, set the time zone to America/New_York, then under Script Properties add
GRIDIRON_API_KEYwith your key as the value.
Keep the key in Script Properties, not in the code. Anyone you share the spreadsheet with as an editor can open the script editor, and a key pasted into Code.gs is a key you've shared. Script Properties are only visible to the script's owner.
What Apps Script code pulls the data?
Replace the contents of Code.gs with this, then save:
const API = 'https://api.gridirondata.com/api/v1';
const SEASON = '2026';
function apiGet_(path) {
const key = PropertiesService.getScriptProperties().getProperty('GRIDIRON_API_KEY');
const res = UrlFetchApp.fetch(API + path, {
headers: { 'x-api-key': key },
muteHttpExceptions: true,
});
const code = res.getResponseCode();
if (code === 429) throw new Error('Daily limit hit: ' + res.getContentText());
if (code !== 200) throw new Error('HTTP ' + code + ' for ' + path);
return JSON.parse(res.getContentText());
}
// One /stats call per player: PPR points and snap share for every week.
function refreshUsage() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ids = ss.getSheetByName('Roster').getRange('A2:A').getValues()
.map(r => String(r[0]).trim()).filter(Boolean);
const rows = [['Player', 'Team', 'Last wk', 'PPR', 'Snap %', 'Snap trend', 'Season PPR/g']];
ids.forEach(id => {
const body = apiGet_('/stats/' + encodeURIComponent(id) + '/' + SEASON);
const weeks = Object.keys(body.weekly_stats || {}).map(Number).sort((a, b) => a - b);
if (!weeks.length) { rows.push([id, body.team || '', '', '', '', '', '']); return; }
const w = body.weekly_stats;
const last = w[weeks[weeks.length - 1]];
const snaps = weeks.map(k => w[k].snap_pct).filter(x => x != null);
const prior = snaps.slice(0, -1);
const trend = prior.length
? snaps[snaps.length - 1] - prior.reduce((a, b) => a + b, 0) / prior.length : 0;
rows.push([body.player_name, body.team, weeks[weeks.length - 1], last.points,
last.snap_pct ?? '', Math.round(trend), +(body.season_points / weeks.length).toFixed(1)]);
});
const out = ss.getSheetByName('Usage') || ss.insertSheet('Usage');
out.clearContents();
out.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
out.getRange('A1').setNote('Updated ' + new Date().toLocaleString() + ' · ' + ids.length + ' requests');
}
// Custom function: =GRIDIRON_SNAP("Rome Odunze#WR", 2)
// Cached 6 hours so a sheet recalculation doesn't spend your daily requests.
function GRIDIRON_SNAP(playerId, week) {
const cache = CacheService.getScriptCache();
const k = 'snap:' + playerId + ':' + week;
const hit = cache.get(k);
if (hit !== null) return hit === '' ? '' : Number(hit);
const body = apiGet_('/snaps/' + encodeURIComponent(playerId) + '?season=' + SEASON);
const row = (body.weekly_snaps || []).find(r => r.week === Number(week));
const val = row ? row.snap_pct : '';
cache.put(k, String(val), 21600);
return val;
}
// Run once: refresh every Tuesday at noon in the script's time zone (set it
// to America/New_York in Project Settings), after Monday night's stats land.
function installTrigger() {
ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
ScriptApp.newTrigger('refreshUsage').timeBased()
.onWeekDay(ScriptApp.WeekDay.TUESDAY).atHour(12).everyWeeks(1).create();
}
How it works:
apiGet_sends thex-api-keyheader and usesmuteHttpExceptionsso a 429 becomes a readable error ("Daily limit hit") instead of a stack trace. The trailing underscore hides it from the Run menu and from the sheet.refreshUsagemakes one/stats/{player_id}/2026call per player. That single response carries every week's PPRpointswithsnap_pctjoined on, plusseason_pointsand the player's current team, so it doesn't need a second request for snaps.- Snap trend is the latest week's snap share minus the average of the earlier weeks. A big positive number is a role growing before the points show it.
GRIDIRON_SNAPreads/snaps/{player_id}and returns one week'ssnap_pct. It caches each answer for six hours inCacheService, because Sheets re-runs custom functions whenever it feels like it.
What does the Usage tab look like?
These are the rows refreshUsage writes for a three-player roster, from a run on November 20, 2026 with 2026 data through Week 10 (the walkthrough video runs the same calculation in Python, request for request):
| Player | Team | Last wk | PPR | Snap % | Snap trend | Season PPR/g |
|---|---|---|---|---|---|---|
| Rome Odunze | CHI | 9 | 16.0 | 84 | −2 | 9.9 |
| Aaron Jones Sr. | MIN | 10 | 12.5 | 43 | −15 | 10.6 |
| Saquon Barkley | PHI | 9 | 8.2 | 59 | +24 | 8.4 |
Three players, three stories, one glance. Odunze has been a full-time receiver since Week 2 (84% to 99% of Chicago's snaps), so his trend is flat and his points are the whole question. Aaron Jones is the opposite: 73% to 87% of the snaps in Weeks 2 to 5, then 25% to 43% since, a shrinking role that his 12.5 points in Week 10 hide. Barkley's +24 is a role coming back: 59% in Week 9 after 15% and 32% the two weeks before. "Last wk" is the latest week the player has a stat line, so a player who sat out shows an earlier week. Run it on your publish day and you'll see the latest completed week instead, because the script always reads whatever the API has.
The same sheet, run again at the end of the season, turns into a season review: sort by snap trend and you'll see every role that grew or shrank across the year.
How many requests does the sheet use?
| Action | Requests |
|---|---|
refreshUsage, 15-player roster | 15 |
| Weekly trigger, whole season (18 weeks) | 270 total, 15 a week |
GRIDIRON_SNAP, per distinct player and week, per 6 hours | 1 |
| Basic allowance, per month | 5,000 |
The trap is the custom function. Twenty cells of =GRIDIRON_SNAP() are 20 requests the first time, and without the cache every recalculation would be another 20. Keep the CacheService lines. If you share the sheet with a whole league, have one owner run the trigger and everyone else read the Usage tab; the key is the owner's either way.
The per-minute limit matters too: Basic allows 30 requests a minute, and refreshUsage fires its calls back to back. A 15-player roster fits under it. A roster over 30 players can trip it on a fast run. If you see a "Daily limit hit" error mentioning rate_limited, add Utilities.sleep(2100) inside the forEach loop, which keeps the loop under 30 calls a minute.
How do I refresh it automatically?
Run installTrigger once from the editor (Run → installTrigger) and approve the permissions prompt. It schedules refreshUsage every Tuesday at noon in the project's time zone. Tuesday noon is deliberate: Sunday's stats land in the API about 11:00 am ET Monday, and Monday night's about 11:00 am ET Tuesday, so noon Tuesday is the first time the whole week is in.
Apps Script runs atHour(12) sometime within that hour, not at 12:00 sharp, and it emails you if a run fails. Re-running installTrigger deletes the old trigger first, so you never end up with two.
What else can the sheet pull?
- Matchup history:
/matchups/{player_id}/{opponent}returns games, average, high and low against one team. Add a column for this week's opponent. Our streaming guide shows how to rank candidates with it. - Projection accuracy: each weekly row on
/statsalso carriesdeviation, actual minus projected. Average it per player to see who keeps beating his number. - A snap-share chart: write
weekly_snapsfrom/snapsinto a grid (players down, weeks across) and insert a line chart. It's the fastest way to spot a committee turning into a workhorse. The snap share guide explains what to look for.
Frequently asked questions
Can Google Sheets call the GridIron Data API directly?
Yes. Apps Script's UrlFetchApp.fetch sends HTTPS requests with custom headers, so it can send your key in the x-api-key header and parse the JSON response with JSON.parse.
Where should I store my API key?
In the Apps Script project's Script Properties, read with PropertiesService. A key pasted into the code is visible to anyone who can edit the spreadsheet.
Why cache the custom function?
Sheets re-runs custom functions on recalculation, and each run is a request. CacheService keeps each answer for six hours, so a recalculation costs nothing.
When should the trigger run?
Tuesday around noon Eastern. Sunday's stats land about 11:00 am ET Monday and Monday night's about 11:00 am ET Tuesday, so noon Tuesday is the first time the whole week is available.
Does the sheet work on the free plan?
No. /stats is a Basic endpoint, so a free key gets HTTP 403 with reason basic_required; the free plan covers player lookups and this week's projections. Basic ($9/month) allows 5,000 requests a month, so a 15-player roster on a weekly trigger (about 65 a month) leaves plenty of room, and its 30 requests a minute covers a 15-player refresh run back to back.