Search code examples
phpxmlgoogle-sheetsfeed

Import XML for NFL scores 2018 - New XML?

Last year I was provided with IMPORTXML so I can get live scoring updates to Google Sheets. I went to use it today to prep for 2018 and it's still pulling last year's data. I'm not familiar how I can get the information needed for this year, if it's changed or if it's just too soon. I was hoping someone with a little more knowledge could maybe guide me or point e in the right direction?

=importxml("http://www.nfl.com/liveupdate/scorestrip/ss.xml","//g/@eid" )

This was one of the fields I used, and it's still pulling 2017. Thanks!

Solution

  • Looks like that XML service is discontinued or not so easy to find.

    However this site is a JSON equivalent I believe.

    http://www.nfl.com/liveupdate/scores/scores.json

    and this site gives live data also and you can choose by date.

    http://www.nfl.com/liveupdate/game-center/2018090600/2018090600_gtd.json

    After staring at the output from the first link, it looks like you can get the matches in play and scheduled from that, and then use the references to select a particular match for the full play-by-play details in the second link.

    Below is a function you can use http://www.nfl.com/liveupdate/scores/scores.json to get the results into Google-sheets and present with selected fields.

    To install and use it read the instructions here: https://developers.google.com/apps-script/guides/sheets/functions#using_a_custom_function

    In your sheet pick a cell and enter = ReadNFLResults()

    To run the script periodically (say every 2 minutes), follow these instructions https://www.quora.com/How-can-I-periodically-run-a-Google-Script-on-a-Spreadsheet

    /** Imports NFL results to your spreadsheet Ex: ReadNFLResults()
* @customfunction
*/

function ReadNFLResults(){

  try{

    var res = UrlFetchApp.fetch("http://www.nfl.com/liveupdate/scores/scores.json");
    var content = res.getContentText();
    var json = JSON.parse(content);   

    // Extracts the keys (these change every week)
    var keys = [];
    for(var k in json) keys.push(k);

    // Declare array for results
    var NFLResults =[];

    // Make a Title Row
    NFLResults.push(["Date","Stadium","Time","Qtr","Home Team","Hs1","Hs2","Hs3","Hs4","HsT","Vis Team","As1","As2","As3","As4","AsT"]);

    // Extracts the Games one per line
    for(n=0; n<keys.length; n++){
      var Date = ""+keys[n];
      var Stadium = json[keys[n]]["stadium"];
      var Clock = json[keys[n]]["clock"];
      var TV = json[keys[n]]["media"]["tv"];
      var Qtr = json[keys[n]]["qtr"];
      var Home = json[keys[n]]["home"]["abbr"];
      var Hs1 = json[keys[n]]["home"]["score"]["1"];
      var Hs2 = json[keys[n]]["home"]["score"]["2"];
      var Hs3 = json[keys[n]]["home"]["score"]["3"];
      var Hs4 = json[keys[n]]["home"]["score"]["4"];
      var HsT = json[keys[n]]["home"]["score"]["T"];
      var Away = json[keys[n]]["away"]["abbr"];
      var As1 = json[keys[n]]["away"]["score"]["1"];
      var As2 = json[keys[n]]["away"]["score"]["2"];
      var As3 = json[keys[n]]["away"]["score"]["3"];
      var As4 = json[keys[n]]["away"]["score"]["4"];
      var AsT = json[keys[n]]["away"]["score"]["T"];
      NFLResults.push([Date,Stadium,Clock,Qtr,Home,Hs1,Hs2,Hs3,Hs4,HsT,Away,As1,As2,As3,As4,AsT]);
    }

   // Return array to Google-Sheets
    return (NFLResults);

  }
  catch(err){
      return "Error getting data";  
  }

}

    Here is sample results as of Sept 10, 2018:

    NFL results in progress