How To Use APIs & Google Sheets To Assess FPL Prospects

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.

Hillary is right about how she lost

Hillary Clinton apologizes at least 35 times in What Happened. She apologizes for her failings as a candidate, she apologizes for not apologising enough, and she apologizes for being the wrong messenger her campaign message. Clinton’s book offers a reasonable postmortem, perhaps the most accurate one written so far, of why Trump won.

It’s not in doubt, for instance, that Russia waged an unprecedented information war against Clinton’s campaign. Polls clearly show that the intervention of James Comey, the director of the FBI, a week before polling day, did help to swing the result. The issue of Hillary’s emails was given a disproportionate level of attention. Trump did receive free wall-to-wall coverage.

Studies have shown clearly that Trump’s explicit appeals to gender and race played a key role in winning over white working class voters. Throughout the campaign, Trump used language that was explicitly hostile toward women, calling Clinton a “nasty woman,” making comments about Carly Fiorina’s looks, and promoted tweets that referred to Fox News moderator Megyn Kelly as a “bimbo.” Trump also called her “overrated” and “lightweight,” and “average in every way.”

Clinton’s candidacy ran up against assumptions that Americans have on the role of women in public life. As Malcolm Gladwell put it on the eve of the election, “people had a preexisting mental notion of what a female candidate would look like, and she doesn’t look like it. She is being penalized for having a series of traits that people find unacceptable in a woman.” Clinton didn’t make a secret of her desire for power, and refused to hide that. Men simply don’t get criticised for being ambitious.

If you’re talking about What Happened during the election campaign, you can’t simply ignore these facts. They are literally what happened. It’s not as though Comey’s intervention didn’t make a difference, or Russia wasn’t waging information war via Facebook, that Trump wasn’t explicitly sexist, nor that Clinton didn’t face obstacles a man would not have faced. If it wasn’t for any one of these factors, Hillary Clinton would have won the Electoral College. Even despite these factors, thrown in with the failings she herself admits to.

The charge being levelled against Clinton then, is that she doesn’t have the right to make this argument. That by pointing out that there were other factors involved in her not winning, she is somehow diminishing her own role in the defeat (even though she fulsomely acknowledges her own failings. That it’s a less than perfect apology, even though Trump’s apology for his “grab them by the p**sy remarks was suffixed by “Hillary Clinton, and her kind, have run our country into the ground.”

Hillary has every right to tell her side of the story. We don’t demand other politicians disappear from public life when their front line careers end, nor do we try to silence their contributions. For a right-wing media so keen on protecting free speech, there does seem to be a lot of anger that Hillary Clinton has dared to write a book.

So why does the media have such a blind spot on Clinton? Why are they arguing that Clinton is blaming everyone but herself, when she clearly blames herself quite a lot? Why are they demanding that she disappear from public life, when they don’t make that demand of John McCain, Al Gore or Barack Obama?

The reason the media has such a blind spot on Hillary – why it has to paint Hillary as “she blames everyone but herself” – is because to admit that Hillary got some things right in her book is to admit that they got a lot of things wrong. It would have to take responsibility for giving Trump’s campaign the wall-to-wall coverage it did, for the disproportionate coverage of Hillary’s email server, and for not fact-checking Trump’s claims more aggressively.

Instead the media has leaned on a false narrative that Hillary fails to take responsibility for her own campaign – compounding their own errors in the campaign with yet more now. Meanwhile, the right continues to wage information warfare, aided and abetted by the Times and the Washington Post. Clinton is right about why she lost. When will the media take their share of the blame?

The political system was not designed for a President like Trump

Today, as millions of commuters were heading to work, an Improvised Explosive Device partially detonated on the London Underground.

Had the device exploded as intended, everyone on the train could have been killed. The bomb-maker is still on the loose.

With emergency services still tending to survivors, Donald Trump tweeted “Another attack in London by a loser terrorist. These are sick and demented people who were in the sights of Scotland Yard. Must be proactive!

There’s no evidence that Scotland Yard was aware of an attack being imminent (if so, the threat level would surely have been raised), and even if they were, it’s highly unlikely Donald Trump would have known about it.

The media ecosystem, from the talking heads on cable news, to the blogs and the social media echo chamber, is set up to amplify and interpret Trump’s every word. The speed with which news moves is unprecedented.

Neither has there even been a President so lacking in message discipline as Trump. CEOs, let alone Presidents, simply don’t overshare their private thoughts into the public domain.

There’s good reason for this. America loses when it alienates its international allies or meddling in their affairs, and the more Trump shoots from the hip, the greater the risk of this.

Today is yet another instance where Trump would have been better off keeping his mouth shut.

Yes, your anger at Diane Abbott is a problem

Another day, another story about Diane Abbott. This time Diane Abbott appeared on Good Morning Britain and had the temerity to quote a word that confronts her every day. People often say they want politicians to be more straight-talking and stop hiding behind soundbites, and yet when one gives us the unvarnished truth, we don’t like it.

The phrase “online abuse” acts as a convenient catch-all for things that we’d rather not think about. It gives us a way out from directly addressing the racism and sexism that infects out public life. If you squint, “online abuse” almost sounds like “criticism,” giving an outlet to claim your free speech is being impinged on.

Abbott is expected to face down a continual and ceaseless torrent of vile comments about her gender and race, never once losing her temper. She faces a level of provocation that may of us would consider intolerable. Yet, if she dares to speak openly and frankly about the abuse she faces, she’s accused of frightening the horses.

Using the n-word, as Diane Abbott did this morning, is the only way of communicating precisely what it’s like to be in her position. If it offends you to hear it once, imagine what it must be like to be Diane Abbott and see it in your Twitter timeline hundreds of times a day, aimed at you.

Those who attack Abbott are very quick to claim that their free speech has been impinged upon when they’re called out. Yet they only seem to value free speech when it’s their own.

Some may have problems with Abbott’s politics which transcend her race or her gender. May I suggest that calling her the n-word is unlikely to make her change her mind.

At the election, Theresa May had the opportunity to put her case directly to the British people. Every vote for me, she said, would make her stronger in the negotiations to come with Europe. That was then, the last we heard about Europe for two months as May toured round the country delivering soundbites and attacking Abbott. Boris Johnson, no-one’s choice for a Foreign Secretary, frothed at the mouth on the Today programme raising the spectre of Abbott as Home Secretary.

The election campaign sowed the seeds for what was to follow. Theresa May’s all too clear failings as a leader were hidden behind venal and vitriolic personal attacks. The Tories tried to make a virtue of Theresa May never putting herself up for debate, as though holding court amongst massed ranks of Tory activists was somehow an improvement. Every tough question came back to a monotonous stock answer.

Diane Abbott, on the other hand, was subjected to an altogether more forensic examination. In an election that was billed as a battle of two competing visions for the country, it suddenly became very important that Abbott knew precisely the costs of increasing police numbers and the contents of the Harris report.

If you think I’m demanding special treatment for Diane Abbott, compare and contrast how David Davis has handled the Brexit negotiations. Davis has set out a general approach for how he’d like negotiations to be handled, and left civil servants in Brussels to handle the finer details. Would Davis fare any better if grilled by the Today programme on the precise costs of individual line items in the EU settlement? Almost certainly not.

Over the last few year, uncomfortable double standards have emerged in British politics. The tech titans are fearful of silencing free speech, but Diane Abbott quoting the contents of her Twitter feed is considered worthy of censure. Women and BAME politicians face abuse and vitriol when they give a poor interview, white middle-class politicians like Jacob Rees-Mogg are lauded as “men of conviction.”

On many levels, we don’t have the political class we deserve in this country, and we certainly don’t have one worthy of the challenges that this country now faces. When I hear Amber Rudd talk about getting the right hashtags in place to deal with cybersecurity, David Davis’ Panglossian outlook on Britain’s trade prospects, or John McDonnell pulling out Mao’s Red Book in his budget response, I cringe a little. People are now seriously proposing Jacob-Rees Mogg as a future Prime Minister, for heavens’ sake.

I don’t expect our political class to be perfect, but I’m thoroughly tired of the double standards, racism and misogyny that infects our public life.

Recorded hate crime against disability, religion and gender/sexual orientation is skyrocketing

GOV.UK published statistics on hate crimes and racist incidents recorded by the police.

It makes grim reading if you are from a religious minority, disabled, or from a gender and sexual minority.

Recorded hate crime against all four is on the rise. The rise is fastest amongst lesbians, gays and bisexuals.

This goes against a lot of the rhetoric that we hear about Britain becoming a more safe and tolerant place.

Screen Shot 2017-09-13 at 19.10.37

View my worksheet in Tableau Public

This Week’s Best Politics Articles

Here are some of the best things I’ve read this week:

Politics

Fake Russian Facebook Accounts Bought $100,000 in Political Ads – The New York Times

The Fake Americans Russia Created to Influence the Election – The New York Times

Why Verrit, a pro-Clinton media platform, is doomed to fail | Michael Paarlberg | Opinion | The Guardian

Where have all the workers gone? An inquiry into the decline of the U.S. labor force participation rate

Corbyn

Why Jeremy Corbyn should be considered the favourite at the next election

Silicon Valley’s Politics: Liberal, With One Big Exception – The New York Times

Britain’s clampdown on FGM is leaving young girls traumatised | Nadifa Mohamed | Opinion | The Guardian

Data

Credit firm Equifax says 143m Americans’ social security numbers exposed in hack | US news | The Guardian

North Korea

The Risk of Nuclear War with North Korea | The New Yorker

 

The PR Agency is dead

I have a fondness for PR. PR gave me my first break into the media. It allowed me to meet many amazing people along the way. But PR is dying.

The demise of Bell Pottinger encapsulates broadly what has happened with PR for a while:  that the era of “getting the word out” either via Press Releases, boozy lunches, backroom deals or glamorous events, is coming to an end.

Bell Pottinger is a high-profile casualty of these changes, but the agency is the symptom and not a cause of an industry that has allowed itself to grow lazy and complacent and which has consistently refused to upskill to meet the demands of the digital age.

Screen Shot 2017-09-08 at 14.26.51

It looked for a while like social media might offer a brief respite, or a new opportunity to beleaguered PR agencies. But after the first novelty, people’s appetite for branded schlep and gimmicky ‘activations’ wore thin.

Brands themselves also discovered that they could pay to reach people themselves using Facebook Ads or Google AdWords. And that these campaigns often delivered more measurable return on investment than anything the agencies had served up.

And for all the bluster about becoming more “data driven” and “branded storytelling”, nothing ever seemed to come of it. While brands and digital agencies were racing off into the sunset with techniques like retargeting and embracing tools like Google Data Studio and Tableau, PR agencies were uploading a video to YouTube and calling it a multimedia strategy.

Ah but influencer marketing! Ah but no. Much as the PR industry would like another set of gatekeepers to glom to, it turns out the secret to engaging influencers is … to pay them a lot of money. Then they might tweet about you. Or something. You’re not really sure what you get actually, and they can’t  tell you. It doesn’t just offer false hope, it reincarnates the same problems PR has had all along.

In response to the Bell Pottinger case, the PRCA ‘issued a statement’ (how else?) saying that “The PRCA has never before passed down such a damning indictment of an agency’s behaviour.” The industry will then go back to pretending nothing has happened.

The tide of Press Releases that no-one will reads will continue, hacks will continue to “sell in” non-stories that boost a client’s ego but have no impact on the business, and people will continue to tweet their stupid photo’s from award shows attended only by their friends.

Stick a fork in PR, it’s done.