I’m a keen FPL player, and use Fantasy Premier League’s API and Google Sheets to make it easy to analyze player data.

You might be frustrated on having to rely on other people’s sporadic data drops (into the Fantasy Premier League Reddit, for instance), and want to do your own analysis.

Using the Fantasy Premier League API and a spreadsheet tool we can load player data into a Google Sheet and filter for differential picks, total score, points per match and more.

The great thing about spreadsheets is that almost everyone knows how to use them in some capacity, and it’s really easy to use an API with Google Sheets with a couple of very small modifications to Google Sheets, so it’s not a problem if you’re not technical.

The next step after this would really be to load the data into a tool like Tableau Public to evaluate the data, but for now we just want to get our hands on the data and start to have a play with it. If there’s enough demand for the Tableau Public tutorial I will write that as well, so please clap and/or respond if you’d like to see that.

Set Up Google Sheets

The first thing that we are going to do is to set up a new sheet in Google Sheets. It’s important to use Google Sheets rather than something like Excel, as Google Sheets is scriptable and generally better at working with things that interact with the internet like APIs. Excel wasn’t really designed for a world where it would ever have to talk to the Internet.

I’ve used a couple of technical terms here so, for the uninitiated, an API is a common method for accessing data from a database (the API we will be using is used by FPL to produce their player info screens) and JSON is a standard format for outputting that data. Technologists like JSON because it’s supported by lots of different platforms, it has simple markup, and when formatted it is fairly easy to read and query.

Google Sheets doesn’t support APIs or JSON out of the box though, so we need to add a script that can take an API URL (Fantasy Premier League’s data feed) and returns the data that we want in JSON format, and which formats it as a spreadsheet that we can read.

Doing this is really easy.

Go to Tools > Script Editor

You should then see a screen like this:

First, you want to delete everything in the Code.gs file, so it is blank:

Next, we’re going to rename the file:

And we’re going to give it the name “ImportJSON”

Your screen should now look like this:

Next, go to this pastebin, copy everything there, and paste it into your ImportJSON file. The screen should now look like this:

We’re done here now, it’s time to go back into our Google Sheet and load in the data. Click on File > Save to save our function.

What we’ve just done is essentially add a new function called ImportJSON to this Google Sheet, which we’re going to use now.

Go back to your spreadsheet, which should be open in the other tab.

Pulling the Fantasy Premier League API data using ImportJSON

Now we’re going to pull down the data from the Fantasy Premier League API using the function we’ve just created.

In your spreadsheet, copy this formula into cell A1:

=ImportJSON(“https://fantasy.premierleague.com/drf/bootstrap-static“)

Important: Before hitting enter, copy the last quote mark (after bootstrap-static), and replace the curly quote before https:// , otherwise you will get an error. Medium automatically converts quotes into curly quotes, which Google Sheets does not recognize.

N.B. This API isn’t officially documented or supported by FPL in any way, and it is subject to change and break as FPL updates its site over time. However this particular API endpoint has worked well for the last couple of seasons, so hopefully this one continues to work. If I find that its broken, I’ll come in and update this post.

The amended formula should look like this:

You will know it has worked because the formula will have turned green. You should then hit enter. If you see anything other than what is above (such as inverted commas after the formula), Google Sheets has mangled the formula and you should paste in the formula again.

The spreadsheet should then display “Loading” in the first cell. Don’t worry that it says ERROR when you hover over, Google Sheets is just having a think:

If it has all worked, the data should have loaded into your spreadsheet, sorted by each player’s numerical ID. Each player holds onto their ID throughout the season and it can be very useful for cross-referencing with other data sources, so I wouldn’t delete the “Elements ID” field.

Data analysis in Google Sheets / Excel

We’re almost there, but there are a couple of important steps to take before we dive into analyzing the data.

The first thing we need to do is copy this data into a static spreadsheet. Although ImportJSON is really good at pulling data from APIs, it tends to have trouble with sorting and filtering data.

ImportJSON also has a nasty tendency to keep on trying to refresh the data, making it a hassle to work with and which will have you hitting API limits before long. It’s much easier (and faster) to work with a static document which isn’t constantly trying to pull down the data.

Aside: Helpfully though, you can go back into the Google Spreadsheet (keep this one in your Google Drive) after each Gameweek and it will pull the latest data. I wouldn’t advise doing this while a Gameweek is in progress, as we all know the problems FPL seems to have while a Gameweek is in progress.

The easiest way to get a static sheet you can work with, though, is to simply download the data as an Excel Spreadsheet. That way, you get to use Excel for what it’s good at – processing relatively large amounts of data without lag.

If you do want to work with the data in Google Sheets, you can always import the data back into a Google Sheet, as I’ve done here.

You’ll see that cell A1 is now #NAME, as this new spreadsheet doesn’t know what the hell “=ImportJSON” is (functions aren’t carried across when you save). Feel free to change this field to ID.

The field headings should be fairly self-explanatory (and include a few very intriguing looking headers), and once you are in your static spreadsheet you can alter these to anything you wish.

Your first step should be to set up some Filters:

  • Sort by Points Per Game (column AJ)
  • Convert column AJ to a number column (Format > Number), and filter out rows lower than 4.
  • Convert “Elements Selected By” to a number column (Format > Number) and filter to, say, less than 15% (if you’re on the hunt for differentials).

You can also do calculations on the various columns to develop your own criteria, set up conditional formatting to highlight in green players who meet a certain set of conditions, or filter the list down to match your Watchlist on FPL.

Beyond Excel/Google Sheets, the next step would be to use a tool like Tableau to plot graphs using the data. Some advanced examples of this are here:

That’s it. Now you have the data to know if it’s really worth taking a punt on Calvert-Lewin, or whether it’s really worth spending the extra money on Aguero over Jesus.

Now go look at the remaining columns and filter / sort to your hearts content. Even if you don’t go as far as creating charts in Tableau, this should give you some data to work with and help you with researching your picks.

3 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s