Problem with eBay API Parsing XML to Google Script

user_14283247 0
Viewed 165

I am trying to call the eBay API GetMyeBaySelling I can get Act, Timestamp, Version and Build to return and write it to a Google Sheet but no matter what I try I can not get the 'Item' list to return. The Response is below and I need help getting any of the other fields. Lets say I want to loop through and return 'BuyItNowPrice' for all the returned listings. I have tried the the code below but I am getting nowhere fast.

I am more used to Excel VBA but trying to transition to Google Sheets Script.

API Response:

[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>

My Code:

    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]);

Thanks in Advance

UPDATE: 16-09-2020 @ 17:08hrs UK: OK, I now have working code and it writes data to the sheet but it takes about 82 seconds to write 200 records in 4 columns. Would it be quicker to write the data to an array then add it to the sheet? If it would be faster can you help with an example. In fact, it would be good to see how to write it to an array anyways, then to the sheet, so I can learn some more.

My working code:

  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]);
ebay-api google-apps-script google-sheets xml-parsing xml
1 Answers

I believe your goal as follows.

  • You want to retrieve the values of BuyItNowPrice from ItemArray using Google Apps Script.
  • response of var response = UrlFetchApp.fetch(site, options); is in "API Response:".

Modification points:

  • I think that in the XML data in your question, root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChild('BuyItNowPrice', NS) returns null. In this case, it is required to be var itms = root.getChild('ActiveList', NS).getChild('ItemArray', NS).getChildren();.
  • In the for loop, it is required to be var item = itms[i].getChild('BuyItNowPrice', NS).getText();.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
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');
}
To: 
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);
}

When your XML data in "API Response:" is used, the sample script becomes as follows.

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);
  }
}

Reference:

Added:

In your initial question, you wanted to retrieve the value of BuyItNowPrice. In your new question, you want to retrieve the values of ItemID, Title, ListingDetails.ViewItemURLForNaturalSearch and PictureDetails.GalleryURL. From your initial question, I didn't notice your new question.

In your script, appendRow is used in a loop. In this case, the process cost will be high. So I would like to propose to put the values in an array and put the array to the sheet.

Modified script:

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);
user_71086530
Related