IMPORT FOOTBALL DATA TO GOOGLE SHEETS

  • July 18, 2020
  • Ana from mixedanalytics.com

This guide will show you how to pull major and minor league football stats into Google Sheets, using the API Connector add-on for Sheets. Please note that this API is freemium, and requires a credit card to access, though you won’t need to pay anything unless you go over the limits.

Part 1: Subscribe to the API-Football API
Part 2: Create your API Request URL
Part 3: Pull API data into Sheets
Part 4: Notes and Expansions

PART 1: SUBSCRIBE TO THE API-FOOTBALL API ON RAPIDAPI

1 - If you haven’t already, create an account and log in to your RapidAPI account. While logged in, navigate to the API-Football API located at https://rapidapi.com/api-sports/api/api-football



2 - Click Subscribe to Test



3 - You will now see a list of pricing plans. Click Subscribe on the free Basic plan, and enter your credit card details.



4 - Once you’re subscribed, you’ll be directed back to the main API page.

PART 2: GET YOUR API REQUEST DETAILS FROM RAPID API

1 - Available endpoints are listed in the lefthand sidebar. For this example, let’s choose GET predictions.



2 - Connection information for your request will now populate the window. We’ll be copying code snippet values into API Connector, so choose (Shell) cURL format from the dropdown, as you’ll be able to copy and paste the URL directly (some code snippet formats display query strings separately from the base URL, which is less convenient for our purposes).



PART 3: PULL FOOTBALL API DATA INTO SHEETS

We can now enter our values into API Connector and import football API data directly into Google Sheets.

1 - Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
2 - In the Create Request interface, enter the Request URL provided by the cURL snippet above (https://api-football-v1.p.rapidapi.com/v2/predictions/157462)



3 - Under Headers enter the headers displayed in the code snippet. They will be key-value pairs like this:
x-rapidapi-host api-football-v1.p.rapidapi.com
x-rapidapi-key YOUR_API_KEY

Replace YOUR_API_KEY with the API key shown in the code snippet. These values are also available in the Header Parameters section in the main pane. All together, it should look like this:



4 - Create a new tab. You can call it whatever you like, but here we’ll call it Predictions. While still in that tab, click Set to use that tab as your data destination.
5 - Name your request. Again we’ll call it Predictions
6 - Under Output options, select Compact report style. This is not strictly necessary, but for this request it makes the response a bit easier to read.



7 - Click Run and a moment later you’ll see a list of football predictions in your Google Sheet:



PART 4: RAPIDAPI + GOOGLE SHEETS NOTES AND EXPANSIONS

1 - Play around with the endpoint list in the left pane to retrieve other information like leagues, teams, events, players, statistics, etc. Each time you click an endpoint, the new URL will appear in the code snippet box, where you can copy and paste it into API Connector.
2 - You can also play around with the parameters listed in the main window. As before, when you make an update, it will automatically be reflected in the URL that you copy and paste from the code snippets section.



3 - If you’re looking for a more dynamic, ticker-like experience for live scores (the /fixtures/live endpoint), check out the API Connector custom function IMPORTAPI() for faster refresh rates.
4 - Now go build! Send over a link if you make something cool ?

IMPORTANT SECURITY NOTE
Anyone with Owner or Edit access to your Google Sheet can view all the information you’ve saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

This tutorial is taken from mixedanalytics.com and written by Ana.