xml - eBay API 将 XML 解析为 Google 脚本的问题

Question

我正在尝试调用 eBay API GetMyeBaySelling 我可以获取 Act、Timestamp、Version 和 Build 返回并将其写入 Google 表格，但无论我尝试什么，我都无法返回“项目”列表。响应如下，我需要帮助获取任何其他字段。假设我想遍历并为所有返回的列表返回“BuyItNowPrice”。我已经尝试了下面的代码，但我无处可去。
我更习惯于 Excel VBA，但尝试过渡到 Google Sheets Script。
API 响应:

[20-09-15 19:52:46:012 BST] Logging output too large. Truncating output. <?xml version="1.0" encoding="UTF-8"?>
<GetMyeBaySellingResponse xmlns="urn:ebay:apis:eBLBaseComponents"><Timestamp>2020-09-15T18:52:45.627Z</Timestamp><Ack>Success</Ack><Version>1163</Version><Build>E1163_CORE_APISELLING_19187371_R1</Build><ActiveList><ItemArray><Item><BuyItNowPrice currencyID="GBP">3.99</BuyItNowPrice><ItemID>##########</ItemID><ListingDetails><StartTime>2011-09-16T10:51:54.000Z</StartTime><ViewItemURL>https://www.ebay.co.uk/itm/???????????????????????????-/############</ViewItemURL><ViewItemURLForNaturalSearch>http://cgi.ebay.co.uk/????????item=############&amp;category=51169&amp;cmd=ViewItem</ViewItemURLForNaturalSearch></ListingDetails><ListingDuration>GTC</ListingDuration><ListingType>StoresFixedPrice</ListingType><Quantity>70</Quantity><SellingStatus><CurrentPrice currencyID="GBP">3.99</CurrentPrice></SellingStatus><ShippingDetails><ShippingServiceOptions><ShippingServiceCost currencyID="GBP">0.0</ShippingServiceCost></ShippingServiceOptions><ShippingType>Flat</ShippingType></ShippingDetails><TimeLeft>PT15H59M9S</TimeLeft><Title>????????????????????????????</Title><WatchCount>3</WatchCount><QuestionCount>3</QuestionCount><QuantityAvailable>0</QuantityAvailable><SKU>???????</SKU><PictureDetails><GalleryURL>http://thumbs.ebaystatic.com/pict/???????????_5.jpg</GalleryURL></PictureDetails><NewLeadCount>3</NewLeadCount><ClassifiedAdPayPerLeadFee currencyID="GBP">0.0</ClassifiedAdPayPerLeadFee><SellerProfiles><SellerShippingProfile><ShippingProfileID>??????????</ShippingProfileID><ShippingProfileName>Flat:Royal Mail 2nd(Free),Royal Mail Tra,Same#79</ShippingProfileName></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>????????????</ReturnProfileID><ReturnProfileName>Returns Accepted,Buyer,30 days#2</ReturnProfileName></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>124284320022</PaymentProfileID><PaymentProfileName>PayPal#0</PaymentProfileName></SellerPaymentProfile></SellerProfiles><HideFromSearch>true</HideFromSearch><ReasonHideFromSearch>OutOfStock</ReasonHideFromSearch><OutOfStockControl>true</OutOfStockControl></Item><Item>

我的代码:

    var response = UrlFetchApp.fetch(site, options);

  
   var document = XmlService.parse(response);
  Logger.log(response);
  var root = document.getRootElement();
  var NS = XmlService.getNamespace("urn:ebay:apis:eBLBaseComponents");
   
  var ack = root.getChildText('Ack', NS);
  var time = root.getChildText('Timestamp', NS);
  var version = root.getChildText('Version', NS);
  var build = root.getChildText('Build', NS);
  var shortmessage = root.getChildText('ShortMessage', NS);
   //Logger.log(shortmessage);  
  var longmessage = root.getChildText('LongMessage', NS);
   //Logger.log(longmessage);
  
  var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChild('BuyItNowPrice', NS);
        Logger.log(itms + '1st Log');
  for (var i = 0; i < itms.length; i++) {
    var item = itms[i].getChild('Item', NS).getText();
      Logger.log(item + '2nd Log');
  }

    //sheet.getRange('A2').setValue(xml);
   var LstSheet = sheet.getLastRow()+1
   sheet.getRange(LstSheet, 1).setValue([ack]);
   sheet.getRange(LstSheet, 2).setValue([time]);
   sheet.getRange(LstSheet, 3).setValue([version]);
   sheet.getRange(LstSheet, 4).setValue([build]);
   sheet.getRange(LstSheet, 5).setValue([shortmessage]);
   sheet.getRange(LstSheet, 6).setValue([longmessage]);

提前致谢
更新:16-09-2020 @ 17:08hrs UK:好的，我现在有工作代码，它将数据写入工作表，但在 4 列中写入 200 条记录大约需要 82 秒。将数据写入数组然后将其添加到工作表是否会更快？如果它会更快，你可以帮忙举个例子。事实上，看看无论如何如何将它写入数组，然后写入工作表会很好，这样我就可以学到更多。
我的工作代码:

  var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren();
  for (var i = 0; i < itms.length; i++) {
    
    var item = itms[i].getChild('ItemID', NS).getText();
    var title = itms[i].getChild('Title', NS).getText()
    var url = itms[i].getChild('ListingDetails', NS).getChild('ViewItemURLForNaturalSearch', NS).getText()
    var imgurl = itms[i].getChild('PictureDetails', NS).getChild('GalleryURL', NS).getText()
    
    
    sheet.appendRow([item, title, url, imgurl]);

Answer 1

我相信你的目标如下。

您想检索 BuyItNowPrice 的值来自 ItemArray使用 Google Apps 脚本。

response的 var response = UrlFetchApp.fetch(site, options);在“API 响应:”中。

retrofit 要点:

我认为在您问题中的 XML 数据中，root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChild('BuyItNowPrice', NS)返回 null .在这种情况下，要求是var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren(); .

在for循环中，要求为var item = itms[i].getChild('BuyItNowPrice', NS).getText(); .

当以上几点反射(reflect)到你的脚本中时，它变成如下。
修改后的脚本:
从:

var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChild('BuyItNowPrice', NS);
      Logger.log(itms + '1st Log');
for (var i = 0; i < itms.length; i++) {
  var item = itms[i].getChild('Item', NS).getText();
    Logger.log(item + '2nd Log');
}

到:

var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren();
for (var i = 0; i < itms.length; i++) {
  var item = itms[i].getChild('BuyItNowPrice', NS).getText();
  Logger.log(item);
}

使用“API Response:”中的 XML 数据时，示例脚本如下所示。

function myFunction() {
  var response = `<?xml version="1.0" encoding="UTF-8"?>
<GetMyeBaySellingResponse
    xmlns="urn:ebay:apis:eBLBaseComponents">
    <Timestamp>2020-09-15T18:52:45.627Z</Timestamp>
    <Ack>Success</Ack>
    <Version>1163</Version>
    <Build>E1163_CORE_APISELLING_19187371_R1</Build>
    <ActiveList>
        <ItemArray>
            <Item>
                <BuyItNowPrice currencyID="GBP">3.99</BuyItNowPrice>
                <ItemID>##########</ItemID>
                <ListingDetails>
                    <StartTime>2011-09-16T10:51:54.000Z</StartTime>
                    <ViewItemURL>https://www.ebay.co.uk/itm/???????????????????????????-/############</ViewItemURL>
                    <ViewItemURLForNaturalSearch>http://cgi.ebay.co.uk/????????item=############&amp;category=51169&amp;cmd=ViewItem</ViewItemURLForNaturalSearch>
                </ListingDetails>
                <ListingDuration>GTC</ListingDuration>
                <ListingType>StoresFixedPrice</ListingType>
                <Quantity>70</Quantity>
                <SellingStatus>
                    <CurrentPrice currencyID="GBP">3.99</CurrentPrice>
                </SellingStatus>
                <ShippingDetails>
                    <ShippingServiceOptions>
                        <ShippingServiceCost currencyID="GBP">0.0</ShippingServiceCost>
                    </ShippingServiceOptions>
                    <ShippingType>Flat</ShippingType>
                </ShippingDetails>
                <TimeLeft>PT15H59M9S</TimeLeft>
                <Title>????????????????????????????</Title>
                <WatchCount>3</WatchCount>
                <QuestionCount>3</QuestionCount>
                <QuantityAvailable>0</QuantityAvailable>
                <SKU>???????</SKU>
                <PictureDetails>
                    <GalleryURL>http://thumbs.ebaystatic.com/pict/???????????_5.jpg</GalleryURL>
                </PictureDetails>
                <NewLeadCount>3</NewLeadCount>
                <ClassifiedAdPayPerLeadFee currencyID="GBP">0.0</ClassifiedAdPayPerLeadFee>
                <SellerProfiles>
                    <SellerShippingProfile>
                        <ShippingProfileID>??????????</ShippingProfileID>
                        <ShippingProfileName>Flat:Royal Mail 2nd(Free),Royal Mail Tra,Same#79</ShippingProfileName>
                    </SellerShippingProfile>
                    <SellerReturnProfile>
                        <ReturnProfileID>????????????</ReturnProfileID>
                        <ReturnProfileName>Returns Accepted,Buyer,30 days#2</ReturnProfileName>
                    </SellerReturnProfile>
                    <SellerPaymentProfile>
                        <PaymentProfileID>124284320022</PaymentProfileID>
                        <PaymentProfileName>PayPal#0</PaymentProfileName>
                    </SellerPaymentProfile>
                </SellerProfiles>
                <HideFromSearch>true</HideFromSearch>
                <ReasonHideFromSearch>OutOfStock</ReasonHideFromSearch>
                <OutOfStockControl>true</OutOfStockControl>
            </Item>

        </ItemArray>
    </ActiveList>
</GetMyeBaySellingResponse>`;

  var document = XmlService.parse(response);
  var root = document.getRootElement();
  var NS = XmlService.getNamespace("urn:ebay:apis:eBLBaseComponents");
  var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren();
  for (var i = 0; i < itms.length; i++) {
    var item = itms[i].getChild('BuyItNowPrice', NS).getText();
    Logger.log(item);
  }
}

引用:

XML Service

添加:
在您最初的问题中，您想检索 BuyItNowPrice 的值。 .在您的新问题中，您要检索 ItemID 的值, Title , ListingDetails.ViewItemURLForNaturalSearch和 PictureDetails.GalleryURL .从你最初的问题，我没有注意到你的新问题。
在您的脚本中， appendRow在循环中使用。在这种情况下，工艺成本会很高。所以我想建议把值放在一个数组中，然后把这个数组放到工作表中。
修改后的脚本:

var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren();
var values = itms.map(e => {
  var item = e.getChild('ItemID', NS);
  var title = e.getChild('Title', NS);
  var url = e.getChild('ListingDetails', NS).getChild('ViewItemURLForNaturalSearch', NS);
  var imgurl = e.getChild('PictureDetails', NS).getChild('GalleryURL', NS);
  return [item ? item.getText() : "", title ? title.getText() : "", url ? url.getText() : "", imgurl ? imgurl.getText() : ""];
});
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);