IMPORT SPORTS DATA IN GOOGLE SHEETS WITH SYNCWITH

  • January 27, 2022
  • Chris Reid

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

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
1 Install the Syncwith Addon
2 Get an API Key from API-SPORTS if you don’t already have one
3 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 endpoint


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:


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

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:

Leagues Sheet:
The leagues sheet fetches all the leagues that API-SPORTS has data on (there are over 900 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.



Fixtures Sheet:
Get fixtures for the leagues and seasons you want, choose the leagues and seasons:


Hit refresh on the connection:


Get all the fixtures:


In this example above you’re returning nearly 6000 rows for all the fixtures across 4 years and 3 leagues/cups.

Get the Template:
- Open the sheets template
- Install the Syncwith Addon
- Open the addon
- Update the api key in both connections (Get an API Key from API-SPORTS if you don’t already have one)
- Use the spreadsheet

TOMORROW'S FIXTURES AND PREDICTION TEMPLATE SPREADSHEET
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 Fixtures Sheet:
This sheet is set on a schedule, every day it pulls all the matches for the next day across all 900+ leagues and cups (about 1800 fixtures at the time of writing).


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 Fixtures 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:


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:


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:


Get the Template:
- Open the sheets template
- Install the Syncwith Addon
- Open the addon
- Update the api key in both connections (Get an API Key from API-SPORTS if you don’t already have one)
- Use the spreadsheet

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.

This tutorial is taken from syncwith.com and written by Chris Reid.