How to Build a Personal Football Dashboard Using Free APIs and Google Sheets
Build Your Own Live Score Tracker Today
Most score apps stuff your feed with leagues you never watch, bury the teams you care about under tabs, and fire notifications for every goal in a tournament you ignored. You cannot filter the noise without paying for a premium tier that still shows ads. A Google Sheet fed by a free API flips that model. You decide exactly which clubs appear (say, Arsenal, Bayern Munich, and Boca Juniors) and nothing else. The data refreshes when you open the sheet or on a timer you set, not when a push server decides. No banners, no tracking pixels, no account required beyond the API key. Free tiers from API-Football and Football-Data.org allow hundreds of calls per day, more than enough for a personal fixture list. The sheet lives in Drive, so it opens on your phone, laptop, or a shared family link without syncing hassles. You own the layout, the colors, and the logic; nothing gets deprecated because a product team pivoted.
You need a Google account, about fifteen minutes, and zero coding experience. The tutorial covers three free API options: API-Football via RapidAPI, Football-Data.org, and API-Football direct. Each offers a free tier around 100 requests per day, plenty for tracking a handful of clubs. Only basic Google Sheets formulas appear: IMPORTJSON to pull the data, QUERY and FILTER to shape it, so you never open the Apps Script editor. If you can write =SUM(A1:A10), you can build this dashboard. The sheet stays in your Drive, updates on your schedule, and travels to any device where you sign in. No credit card, no server, no maintenance window.
Choose Your API and Get Your Credentials
Image: 4.2 - Trace the sleeve, by LaMenta3, CC BY-SA 2.0
API-Football on RapidAPI gives the fastest start — one marketplace account, 100 calls per day, and coverage of 800-plus leagues. Football-Data.org skips the credit-card step, enforces a gentle 10-calls-per-minute pace, and returns flatter JSON that parses cleanly in a Google Sheet. API-Football direct mirrors the RapidAPI catalog but lives on its own dashboard, so you manage keys separately. A typical Premier League matchday consumes roughly 20 calls if you pull fixtures, live scores, and basic stats for every game. For a first build, Football-Data.org removes the extra RapidAPI layer and keeps the quota math simple. Switch to API-Football when you need lineups, player heatmaps, or xG feeds that Football-Data.org does not expose.
Register at football-data.org, confirm the email, and open the dashboard to copy your X-Auth-Token. Paste this URL into a browser address bar: https://api.football-data.org/v4/competitions/PL/matches?dateFrom=2024-01-15&dateTo=2024-01-15. Add the header X-Auth-Token: YOUR_TOKEN using a tool like ModHeader or the built-in network inspector. A successful response returns JSON with a competition object and a matches array. Each match entry includes utcDate, homeTeam.name, awayTeam.name, and a score object containing fullTime.home and fullTime.away. Check the response headers for X-RateLimit-Limit and X-RateLimit-Remaining to confirm the 10-requests-per-minute ceiling. If you see a 403, the token is missing or malformed; a 429 means you hit the minute limit.
Pull Live Football Data Into Google Sheets
Google Sheets has no built-in JSON parser, so you need a one-time Apps Script install. Open Extensions > Apps Script, delete the starter code, and paste the IMPORTJSON function from Brad Jasper's GitHub gist. Save the project, return to the sheet, and authorize the script when prompted — this is a standard permission grant, not a security risk. The function signature is =IMPORTJSON(url, query, parseOptions). Use noHeaders in parseOptions to suppress the automatic header row. Example: =IMPORTJSON("https://api.football-data.org/v4/competitions/PL/matches?dateFrom=2024-01-15&dateTo=2024-01-15", "/matches", "noHeaders"). Custom functions only recalculate when their arguments change or the sheet is edited; they do not auto-refresh on a timer. Plan the next section around that limitation.
=IMPORTJSON(
"https://api.football-data.org/v4/competitions/PL/matches?dateFrom="&TEXT(TODAY(),"yyyy-mm-dd")&"&dateTo="&TEXT(TODAY(),"yyyy-mm-dd"), // URL with dynamic today's date
"matches[*].[utcDate,homeTeam.name,awayTeam.name,score.fullTime.home,score.fullTime.away]", // JSONPath query: extract date, home, away, home score, away score
"noHeaders" // parseOption: suppress header row
)
Wrap the IMPORTJSON call in QUERY to keep only finished fixtures, sort by kickoff, and expose clean columns: =QUERY(IMPORTJSON(...), "SELECT Col1, Col2, Col3, Col4, Col5 WHERE Col4 IS NOT NULL ORDER BY Col1", 0). Col1 is the UTC date string, Col2 home team, Col3 away team, Col4 home score, Col5 away score. Convert UTC to local time with an ARRAYFORMULA alongside the data: =ARRAYFORMULA(IF(LEN(A2:A), TEXT(A2:A+TIME(1,0,0), "ddd dd/mm HH:mm"), )) — adjust the TIME offset for your zone. Surface only the clubs you follow by filtering the result range: =FILTER(B2:F, REGEXMATCH(B2:B, "Arsenal|Liverpool|Chelsea") + REGEXMATCH(C2:C, "Arsenal|Liverpool|Chelsea")). The plus sign acts as OR logic across home and away columns. These three formulas — QUERY, ARRAYFORMULA, FILTER — turn a raw JSON dump into a personal fixture list that updates whenever the sheet recalculates, no Apps Script triggers required.
Automate Refresh and Polish Your Dashboard
Custom functions such as IMPORTJSON only recalculate when the sheet opens or when their inputs change, so the fixture list will stale until you intervene. The lightest fix is a five-line Apps Script that writes a timestamp to an out-of-the-way cell (Z1 works well), forcing every dependent formula to re-evaluate. Paste this into Extensions → Apps Script:
Save, then click the clock icon (Triggers) → Add Trigger → choose triggerRefresh, time-driven, hour timer, every hour. That yields 24 API calls per day, comfortably under the 100-call free tier on RapidAPI and the 10-per-minute ceiling on Football-Data.org. If you prefer zero script maintenance, a manual Ctrl+R (Cmd+R on Mac) refreshes the sheet on demand. Either approach closes the "live" gap without a single paid add-on or complex automation.
Create a league selector in cell A1 using Data → Data validation → List from a range, then type the six competition codes: PL, UCL, PD, BL1, SA, FL1. Rewrite the IMPORTJSON URL to read that cell so the dashboard switches leagues instantly:
Apply conditional formatting to the score columns (assume home goals in D, away goals in E). Select the fixture rows, choose Format → Conditional formatting → Custom formula is, and add three rules:
=$D2>$E2→ green fill (home win)=$D2<$E2→ red fill (away win)=$D2=$E2→ yellow fill (draw)
The colors update automatically as results arrive. Finally, share the sheet: File → Share → Anyone with the link → Viewer. For a public embed, use File → Share → Publish to web → Entire document → Link, then paste the iframe wherever you need it.
Keep Your Football Dashboard Running Long-Term
Image: DIY Suitcase Table, by Stacie Stacie Stacie, CC BY-SA 2.0
Free tiers shift without notice; Football-Data.org cut its daily allowance in 2023, so treat quota as a moving target. Each response carries headers that reveal the real-time state: X-RequestCounter-Reset tells you when the window rolls over, and X-Requests-Available shows the remaining balance. Capture those values in a hidden tab named _quota with a lightweight IMPORTJSON call pointed at the headers path, for example:
=IMPORTJSON("https://api.football-data.org/v4/competitions/PL/matches", "headers[X-Requests-Available],headers[X-RequestCounter-Reset]")
Schedule a daily trigger to append the result alongside TODAY(); a simple conditional format flags any row where available calls dip below twenty. If the ceiling drops again, swap the base URL to API-Football; endpoints mirror the same structure, only the auth header changes from X-Auth-Token to X-RapidAPI-Key. Keep a spare column in your league selector with alternative codes (PL, UCL, PD, BL1, SA, FL1) so the dashboard survives a provider switch with a single cell edit.
- Verify quota headers, test one endpoint, confirm conditional formatting renders, check timestamp updates, review favorite teams list.
- Monitor 429 errors, need live minute-by-minute updates, want player stats, or require 50+ leagues — then evaluate paid tiers.
The same IMPORTJSON pattern that powers your fixture list opens the door to richer context without new tools. Swap the endpoint to /competitions/PL/standings and the query path to standings[0].table[].[position,team.name,played,won,draw,lost,points] for a live table; use /competitions/PL/scorers with scorers[].[player.name,team.name,goals] to track the golden-boot race; or pull a single club’s recent form via /teams/57/matches?limit=5&status=FINISHED (replace 57 with any team ID from the Football-Data.org reference). Each call returns flat JSON that QUERY can reshape into clean columns, so you can stack standings, scorers, and form side by side on one sheet. The provider’s interactive docs at docs.football-data.org let you test every parameter before you paste the URL. Your dashboard now beats any app because it shows exactly what you care about — nothing more, nothing less.