How to use importxml to pull just one of several lists

Question

This site has answers to a game called Spelling Bee which is published daily. I want to use importxml to pull the answers (which is a list of words) into Google Sheets, but I am a novice on this function.

I did some reading here, which helped somewhat. Using the formula =importxml("https://nytbee.com/","//li") does pull the list of the current day's answers. However, the site with answers also has a couple other lists of words that I do not want (the common and obscure ones at the bottom of the page). Those lists are also pulled by the formula, because I don't know how to tell the formula to stop after the current day's answers.

How can I get just the current day's answers? I know close to nothing about html, xml, or using the "developer tools" or "inspect element" pane (just to point out that I'd prefer answers to be as rudimentary as possible).

Answer 1

Try this formula:

=importxml("https://nytbee.com/","//*[@id='main-answer-list']")

or, better perhaps, setup a small table like the one below.

• Enter url into cell B1
• Enter the xpath into cell B2
• Enter a formula in Cell B3 - =importxml(B1,B2)

---

Some notes:

• XPATH

  • where to copy the xpath

    • This snapshot shows the place where you need to copy the xpath. It is the "div" at the top of the list.
      

  • double quotation marks

    • the xpath in IMPORTXML is entered between double quotes. But if the actual xpath includes double quotes (such as in this case: "//*[@id="main-answer-list"]), then you'll get an error message.
    • Option#1 - paste the xpath into a cell, and then reference that cell in IMPORTXML
    • Option#2 - change the double quotes for single quotes, then use the revised xpath in the formula.
      • original = "//*[@id="main-answer-list"]
      • revised = "//*[@id='main-answer-list']"

  • Layout Note how the answers are listed in a single cell, each value separated by a space. This isn't necessarily convenient.

    • a formula to list the answers by row in a single column
      • =transpose(split(importxml(B1,B2)," ",true))
        • split - separates the results based on the space between each word
        • transpose - takes the multi-column, single row of data, and returns it in a single column, multi-row

Sample transposed data