Shopping at LEGO or Amazon?
Please use our links: LEGO.comAmazon
As an Amazon Associate we earn from qualifying purchases.

Brickset API into Google Sheets - how to do it?

AlpinemapsAlpinemaps Member Posts: 4
I'm attempting to utilize the Brickset API into Google Sheets, but I'm having a bit of a problem.  Wanted to see if anyone had any advice.

What I'd like to do is pull the name of a set from the API and display it in my spreadsheet.  So, for instance, if I have '10030' in my Set # field (that I have input), then 'Imperial Star Destroyer' shows up in the API called field.

I'm using the IMPORTXML function within Google Sheets.  The function requires two parameters - the URL you are calling, and an XPATH query to run on the data.

So far, the only success I'm getting is when I use the correct URL, and then use the '*' parameter in the XPATH parameter. Unfortunately, that brings in all data in the call.  So, if I just run it on the checkKey operator, I only get the 'OK' value if I use '*'.  When I run it on the getSets operator, I only get values if I use '*' (the values are all of the data about the set I am calling).

The URL formatting isn't the issue (as far as I can tell), because I can successful use checkKey and getSets to at least pass the correct values and to call the info. The problem lays with parsing the data that comes back.  I'd really like to use getSets to just bring back the 'name' field.

When I try the same operation with more basic XML documents, it all works.  But for some reason, I'm not having any luck with Brickset.  Any suggestions?

Comments

  • AlpinemapsAlpinemaps Member Posts: 4
    To provide some additional clarification...

    '/*' and '*/*' also provide the same result (all info)
  • AlpinemapsAlpinemaps Member Posts: 4
    Figured out a solution, and wanted to share.

    The basic issue is that the API calls to a namespace (xmlns) in the header.  So you can't make a direct call to the name attribute (e.g., //ArrayOfSets/sets/name will not work).

    You have to do a work around to call the path.  In this case, it would be called as such:

    //*[local-name() ='ArrayOfSets']/*[local-name() = 'sets']/*[local-name() ='name']

    The local-name gets you into the namespace.

    This should help anyone that might be looking for this sometime in the future.
    ricecakejuno_2023kiki180703
  • juno_2023juno_2023 WisconsinMember Posts: 13
    Thanks for this!
  • HuwHuw Brickset Towers, Hampshire, UKAdministrator Posts: 6,725
    Yes, thanks, @Alpinemaps. Glad you got it sorted because I am usually no help at all in figuring out how to actually use the API :)
  • AlpinemapsAlpinemaps Member Posts: 4
    No problem!  I always hate it when someone posts a question, but you never see a solution, lol.  And I figured, worst case, if I forget my solution someday, I could always come back here.  Hope this helps!
    juno_2023
Sign In or Register to comment.

Shopping at LEGO.com or Amazon?

Please use our links: LEGO.com Amazon

Recent discussions Categories Privacy Policy Brickset.com

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Brickset.com is a participant in the Amazon Services LLC Associates Program, the Amazon.com.ca, Inc. Associates Program and the Amazon EU Associates Programme, which are affiliate advertising programs designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

As an Amazon Associate we earn from qualifying purchases.