javascript - 如何格式化我的 api 调用以在 google 电子表格中发布？

Question

我已经在下面设置了对 seatgeek 的 API 调用，但我在构建响应时遇到了问题。我想将“stats”属性中的四个标题以及其他几个标题写入我的电子表格。我不知道如何正确提取数据，我相信这是因为我不知道如何设置语法来建立标题。以下是 seatgeek 文档 ( or you can find here - about 1/3 down the page under Events ):

{
 "stats": {
  "listing_count": 161,
  "average_price": 97,
  "lowest_price": 62,
  "highest_price": 296
},

为了完成调用，我设置了如下代码:

function myFunction() {
 var url = 'A1';
 var response = UrlFetchApp.fetch(url);
 var json = response.getContentText();
 var data = JSON.parse(json);
 Logger.log(data);
}

我关注了一个 youtube 视频，所以它可以工作，但它的结构不正确。如果我检查记录器，这将返回我猜是 xml 格式(如下)？这是一串数据，如果我通读它，果然，我正在寻找的“统计数据”就埋在中间。

19-11-21 16:08:32:853 CST] {meta={per_page=1, took=3, total=1, page=1, 
geolocation=null}, in_hand={}, events=[{venue={display_location=Denver, 
CO, country=US, access_method={method=NONE, created_at=2015-11- 
04T17:30:28Z, employee_only=false}, address=1000 Chopper Circle,  
city=Denver, timezone=America/Denver, 
url=https://seatgeek.com/venues/pepsi-center/tickets, capacity=21000, 
extended_address=Denver, CO 80204, score=0.7527942, 
num_upcoming_events=106, name_v2=Pepsi Center, metro_code=751, 
popularity=0, name=Pepsi Center, links=[], location={lon=-105.008, 
lat=39.7487}, id=187, state=CO, postal_code=80204, slug=pepsi-center, 
has_upcoming_events=true}, access_method={method=NONE, created_at=2015- 
11-04T17:30:28Z, employee_only=false}, conditional=false, 
date_tbd=false, description=, created_at=2019-08-12T20:15:53, 
title=Portland Trail Blazers at Denver Nuggets, type=nba, score=0.687, 
short_title=Trail Blazers at Nuggets, visible_until_utc=2019-12- 
13T07:30:00, stats={lowest_sg_base_price_good_deals=13, 
visible_listing_count=718, lowest_price_good_deals=20, 
average_price=120, lowest_price=20, highest_price=1780, 
lowest_sg_base_price=13, listing_count=885, dq_bucket_counts=[86, 283, 
162, 66, 72, 106, 72, 5], median_price=80}, popularity=0.758, links=[], 
id=4960016, performers=[{image=https://seatgeek.com/images/performers- 
landscape/denver-nuggets-ea3cb6/2103/huge.jpg, images= . 
{huge=https://seatgeek.com/images/performers-landscape/denver-nuggets- 
ea3cb6/2103/huge.jpg}, 
image_license=https://creativecommons.org/licenses/by/2.0/, type=nba, 
colors={all=[#0E2240, #FEC524, #0E2240], iconic=#0E2240, primary= . 
[#0E2240, #FEC524]}, divisions=[{display_type=Conference,  
taxonomy_id=1030100, short_name=null, display_name=Western Conference, 
slug=null, division_level=1}, {display_type=Division,  
taxonomy_id=1030100, short_name=Northwest, display_name=Western - 
Northwest, slug=western-northwest, division_level=2}], 
url=https://seatgeek.com/denver-nuggets-tickets, score=0.7, 
num_upcoming_events=69, stats={event_count=69}, taxonomies= . 
[{parent_id=null, name=sports, document_source={source_type=ELASTIC, 
generation_type=FULL}, id=1000000}, {parent_id=1000000, 
name=basketball, document_source={source_type=ELASTIC, 
generation_type=FULL}, id=1030000}, {parent_id=1030000, name=nba, 
document_source={source_type=ELASTIC, generation_type=FULL}, 
id=1030100}], popularity=0, 
image_attribution=https://www.flickr.com/photos/34247743@N07/ 
5342565327/in/photolist-9973xi-996Uxp-996Qwr-9971mx-pW93vv-9ddXGD- 
9dcgRk-9dh49b-eXUzb7-eXUyqJ-eXUyJU-gAGfkV-9ddVN4-efsxLC-efmNi4-nLwpvg- 
efmNqB-bGHTnP-btP6DQ-btP2NY-btP36U-bGHQX2-bGHSEK-btP3Wm-bGHRQc-bGHSa2- 
9dfrVw-7qjEsp-6t2y82-7qoAD9-7qozqW-7qoyRj-7qoyg3-6QTVX7-jrx2ht-a8hFoL- 
9dgYYm-rY1RES-s1iU4H-rJi58H-r4X6SX-s1FLi4-m1dYBZ-nMXgaJ-rZKUfA-rFyrmk- 
rZTsUc-rZKTmb-9myz8B-rJ3c8q, name=Denver Nuggets, location= . 
{lon=-105.008, lat=39.7487}, short_name=Nuggets, id=2103, 
home_team=true, home_venue_id=187, slug=denver-nuggets, primary=true, 
has_upcoming_events=true}, 
{image=https://seatgeek.com/images/performers-landscape/portland-trail- 
blazers-d0de88/2106/huge.jpg, images= . 
{huge=https://seatgeek.com/images/performers-landscape/portland-trail- 
blazers-d0de88/2106/huge.jpg}, 
image_license=https://creativecommons.org/licenses/by-sa/3.0/deed.en, 
type=nba, colors={all=[#191919, #D01631, #191919], iconic=#D01631, 
primary=[#191919, #D01631]}, divisions=[{display_type=Conference, 
taxonomy_id=1030100, short_name=null, display_name=Western Conference, 
slug=null, division_level=1}, {display_type=Division, 
taxonomy_id=1030100, short_name=Northwest, display_name=Western - 
Northwest, slug=western-northwest, division_level=2}], 
url=https://seatgeek.com/portland-trail-blazers-tickets, 
away_team=true, score=0.71, num_upcoming_events=67, stats= . 
{event_count=67}, taxonomies=[{parent_id=null, name=sports, 
document_source={source_type=ELASTIC, generation_type=FULL}, 
id=1000000}, {parent_id=1000000, name=basketball, document_source= . 
{source_type=ELASTIC, generation_type=FULL}, id=1030000}, 
{parent_id=1030000, name=nba, document_source={source_type=ELASTIC, 
generation_type=FULL}, id=1030100}], popularity=0, 
datetime_utc=2019-12-13T03:30:00, enddatetime_utc=null, 
url=https://seatgeek.com/trail-blazers-at-nuggets-tickets/12-12-2019- 
denver-colorado-pepsi-center/nba/4960016, event_promotion=null, 
announce_date=2019-08-12T00:00:00, datetime_tbd=false, taxonomies= . 
[{parent_id=null, name=sports, id=1000000}, {parent_id=1000000, 
name=basketball, id=1030000}, {parent_id=1030000, name=nba, 
id=1030100}], datetime_local=2019-12-12T20:30:00, time_tbd=false, 
announcements={}, status=normal}]}

youtube 视频调整了他们的最后一行以指定他们想要从中提取数据的属性。如果我效仿并在最后一行添加“stats”(如下所示)，我会收到“未定义”错误。

Logger.log(data.stats)

即使如此，我也不想只是“记录”通话，我希望它在我的电子表格中。如何使用正确的标题调用数据并将其设置为粘贴到我的工作表中？

Answer 1

请熟悉JSON .

问题

您所说的“XML 对象”实际上是 JSON 对象的“字符串”版本。

你只是accessing properties当它们(属性)不存在时在您的对象上。

为了证明我的观点，这里有一个实验:

代码:

function myFunction() {
  var j = {
    "meta": [],
    "in_hand": [],
    "events": [
      {"a":"1"},
      {"a":"2"}
    ]
  };
  
  Logger.log(j); //Here is the "XML" version of the object, as you said.
  Logger.log(j.doesnotexist); //This will say undefined, meaning this property does not exist.
}

输出:{meta=[], in_hand=[], events=[{a=1}, {a=2}]}

解决方案

考虑到这一点，下面是解决您的问题的方法。 API 返回的对象采用以下格式:

{
  meta,
  in_hand,
  events = [{
    venue = {},
    access_method = {},
    conditional=false,
    date_tbd=false,
    // Other properties here, then, finally:
    stats = {},
    // Then more properties here.
  }]
}

假设您的 data 变量包含 JSON.parse 的结果，您的 stats 变量应该是:

var stats = data.events[0].stats;

此外，如果您要返回多个事件，您可以有一个像这样的统计数据数组:

var stats = [];
for (var i = 0; i<data.events.length; i++) {
  stats.push(data.events[i].stats);
}

希望我能够澄清并指导您找到解决方案。保重。