在Google Sheets中使用ImportXML导入体育数据

Question

我正在尝试从以下网站导入投注数据：https://www.pinnacle.com/en/odds/match/football/usa/nfl

使用Chrome ive中的Inspect函数，尝试确定我所需要的数据的确切链接。我最多只能做到以下几点：

=IMPORTXML(A5,"//td[@class='game-name name']/span")

这给了我许多变量名，但没有数据。

理想情况下，我希望导入包含所有相关列的表odds-data。

有人能帮上忙吗？

Answer 1

要获得用于构建数据表的值，请仔细检查(Chrome)开发人员工具中的网络选项卡，您将发现一个调用889?callback=angular.callbacks._0，其中包含由angular函数包装的JSON对象形式的值。

angular.callbacks._0({"ResponseTime":"2018-09-09T20:19:26Z","OddsType":"american","Leagues":[{"LeagueId":889,"SportId":15,"IsLive":false,"HasLiveLines":true,"Events":[{"EventId":841876318,"LeagueId":889,"SaveTime":"2018-09-10T03:15:47Z","DateAndTime":"2018-09-10T16:10:00Z","Cutoff":"2018-09-10T23:10:00Z","HoursAndMinutes":"16.10","PeriodNumber":0,"IsLive":false,"IsOffline":false,"LiveStatus":2,"Totals":{"OverPrice":104.0,"UnderPrice":-115.0,"Min":45.0},"IsHandicapEmpty":false,"IsMoneyLineEmpty":false,"IsTeamTotalsEmpty":false,"Participants":[{"Id":479,"Name":"New York Jets","Type":"Team1","Handicap":{"Price":-113.0,"Min":7.0},"MoneyLine":256.0,"TeamTotals":{"OverPrice":-110.0,"UnderPrice":-106.0,"Min":17.5},"IsDraw":false},{"Id":480,"Name":"Detroit Lions","Type":"T...

有几个选项可以将其添加到Google电子表格中。最直接但不一定是最简单的选项是使用脚本编辑器检索响应字符串，提取JSON字符串，然后从JSON对象检索相关数据。下面是一个可以帮助您入门的代码片段：

function getJSON(aUrl,sheetname) {
  var aUrl = "https://www.pinnacle.com/webapi/1.17/api/v1/GuestLines/Deadball/15/889?callback=angular.callbacks._0";
  var response = UrlFetchApp.fetch(aUrl);
  var jsonString = response.getContentText().replace("angular.callbacks._0(", "").replace(");", "");
  Logger.log(jsonString);
  var dataAll = JSON.parse(jsonString); //
  var data = dataAll.Leagues["0"].Events;
  /*for (i in data){
    var overPrice = data[i].Totals.OverPrice;
    var underPrice = data[i].Totals.UnderPrice;
    var minPrice = data[i].Totals.Min;
    //
  }*/

  //funcs() to insert data into spreadsheet
}

首先，您需要确定要从对象获取的内容；我怀疑您要查找的数据可以在dataAll.Leagues["0"].Events下找到。

下一步将插入来自对象int电子表格单元格的数据。这是一个如何做到这一点的sample。