SportsGoogle Sheets

API Sports Google Sheets Overview

Chris Reid

Chris Reid

API To Get Sports Data into Google Sheets with API Sports

If you’re looking for API addressable live scores, standings, events, line-ups, player stats, pre-match odds or statistics for a range of sports API Sports has a suite of APIs that cover Football, Baseball, Basketball, Formula 1, Hockey, Rugby, Handball and Volleyball.
Each sport has its own set of documentation and api endpoint with slightly different functions available. Football for instance has a wide array of functions you can call, including: Leagues, Teams, Venues, Standings, Fixtures, Injuries, Predictions, Coaches ,Players, Transfers, Trophies, Sidelined, etc.
For example here’s spreadsheet we created that shows the games from yesterday through the next week for the English Premiere League:
 
In this spreadsheet we’re using the v3.football.api-sports.io endpoint for football (soccer) data. The api setup is pretty straight forward in the Syncwith Addon:
  • enter your api key
  • enter a league
  • enter a season
notion image
Now in the example we’re doing a little extra
  • Using cell references to specify the league and season to the connector
  • The connector is set to pull the data daily so the spreadsheet is always up to date
  • We used the sheets image() function to turn the image URLs into nice logos for each team
  • We’ve used some date filtering to only look at games from the last 7 days through the next 14 days
  • But getting the data into a Google Sheet is trivial
  • Conditional formatting to show the winners in green

How to Get Started with API Sports and SyncWith

  • Open the addon and type API Sports in the search api-sports to see all the API Sports Offerings

Example Hockey Standings

  • We can choose API Sports Hockey
  • Choose the Standings endpoint
  • Name our connection NHL Standings
  • Syncwith specify the required fields for the end point:
notion image
notion image
  • We’ll require the season and the league, which means if we want NHL standings we’ll need to know api-sports ids for all the leagues. You can find this with the leagues endpoint, the API Sports Hockey API has 15 different endpoints. If you used the leagues endpoint to pull all the leagues and their IDs you’d find that the NHL league ID is 57.
  • Now we can take a look at the 2021 season Standings
  • Like most APIs API-Sports passes back a JSON response, looking something like:
notion image
  • SyncWith will let you quickly turn this data into a table format, keeping only the data you want with the sort order you prefer. This is accomplished by previwing the data and using our JSON to table visual extraction
  • The video below shows how you can quickly expand through a nested json tree just by clicking the objects you want and deleting the ones you don’t
  • From here you click insert and Syncwith will persist the connection and json transform, you can easily see all the connections you’ve created and edit them

Things you can do with API Sports

API Sports has tonnes of data for 8 different sports so we’re not going to provide an exhaustive list here. We’d love to hear what you’re doing with SyncWith and API Sports thought.

Demo Spreadsheets

Here are some demo spreadsheets that do some basic things. You can create a copy of these which will will copy the spreadsheet and the api connections we created to help you get started.

Leagues & Fixtures Template Spreadsheet

The leagues and fixtures template is a basic spreadsheet to get football fixtures for your league and seasons of choice. It features two tabs:
Leauges Sheet:
The leagues sheet fetches all the leagues that API Sports has data on (there are over 800 including cups and leagues such as UEFA Youth League, FIFA Club World Cup, Premier League, etc). There is also a way to filter the leagues by country and type (cup or league) in order to easily find the league and league ID.
notion image
Fixtures Sheet:
  • Get fixtures for the leagues and seasons you want, choose the leagues and seasons:
notion image
  • Hit refresh on the connection:
notion image
  • get all the fixtures:
notion image
  • in this example above you’re returning nearly 6000 rows for all the fixtures across 4 years and 3 leagues/cups
Get the Template:
  • Install the syncwith Addon
  • Open the addon
  • Use the spreadsheet

Tomorrow’s Fixtures and Game Prediction

The template was designed as a way to look at tomorrow’s games across all leagues, filter the games to the ones you want, eg only world cup, fa cup, or games from a certain country and then pick a game to view a prediction:
Tomorrows Matches Sheet
  • This sheet is set on a schedule, every day it pulls all the matches for the next day across all 800+ leagues and cups (about 1800 fixtures at the time of writing)
notion image
  • This is just a raw data dump but a nice pivot table on the sheet with slicers lets you filter the data easily on a sheet named Tomorrows Matches Pivot & Analysis
Tomorrows Matches Pivot & Analysis Sheet
  • This sheet lets you sort through all the fixtures with two slicer drop downs, eg if we look just at fixtures in England we get:
notion image
  • The game ID is provided for all the matches which you can then use to get a prediction
  • Enter the game id into the box and refresh the prediction data:
notion image
  • A bunch of prediction data is loaded and analyzed but displayed nicely on a seperate sheet called Prediction Summary
  • The prediction data is not the easiest to parse and but this sheet at least shows an example of how they can be parsed to show the underlying data
  • We’re only using some of the prediction data but there is a lot more in there
Prediction Summary
  • Designed to show the prediction summary as well as an analysis of each teams strengths and the previous fixtures they’ve had against each other, for example:
notion image
Get the Template:
  • Install the syncwith Addon
  • Open the addon
  • Use the spreadsheet

Collaboration

We’re interested in collaborating with newsletters, bloggers and sports communities who might be interested in putting together some useful sheets templates that leverage API Sports data, we were hopeful that the above would serve as a starting point. Reach out to us at support@syncwith.com and maybe we can help with something you’re working on.