IMPORTXML for structured data in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at an e-commerce company.

📋 Request: Your manager wants you to collect the latest product names and prices from a competitor's website to compare pricing.

📊 Data: You have the URL of the competitor's product page which lists products in a structured HTML format with product names inside <h2> tags and prices inside <span class='price'> tags.

🎯 Deliverable: Create a Google Sheet that automatically imports the product names and prices using IMPORTXML formulas.

Sample Data

Product Name	Price
Wireless Mouse	$25.99
Bluetooth Keyboard	$45.50
USB-C Hub	$32.00
Webcam HD	$55.75
Gaming Headset	$70.20

1

Step 1: Open a new Google Sheet and enter the competitor's product page URL in cell C1.

Enter the URL as plain text, for example: https://example.com/products

Expected Result

Cell C1 contains the URL text.

2

Step 2: In cell A2, use IMPORTXML to extract all product names from the competitor's page.

=IMPORTXML(C1, "//h2")

Expected Result

Cells A2 to A6 show the product names: Wireless Mouse, Bluetooth Keyboard, USB-C Hub, Webcam HD, Gaming Headset.

3

Step 3: In cell B2, use IMPORTXML to extract all product prices from the competitor's page.

=IMPORTXML(C1, "//span[@class='price']")

Expected Result

Cells B2 to B6 show the prices: $25.99, $45.50, $32.00, $55.75, $70.20.

4

Step 4: Format the price column as currency for better readability.

Select cells B2:B6, then Format > Number > Currency

Expected Result

Prices in column B display with currency formatting, e.g., $25.99.

5

Step 5: Add headers in row 1: 'Product Name' in A1 and 'Price' in B1.

Type 'Product Name' in A1 and 'Price' in B1

Expected Result

Row 1 shows headers for the data columns.

Final Result

Product Name       | Price
-------------------|--------
Wireless Mouse     | $25.99
Bluetooth Keyboard | $45.50
USB-C Hub          | $32.00
Webcam HD          | $55.75
Gaming Headset     | $70.20

✓The competitor offers five products with prices ranging from $25.99 to $70.20.

✓You can now compare these prices with your own products easily.

✓IMPORTXML automatically updates the data when the competitor's page changes.

Bonus Challenge

Use IMPORTXML to also extract product availability status if available on the page, and add it as a new column.

Show Hint

Look for an HTML tag or class that contains availability info, for example, //div[@class='availability']

Practice

1. What does the IMPORTXML function do in Google Sheets?

easy

A. It fetches data from a web page using a URL and XPath query.

B. It imports data from another Google Sheet only.

C. It creates charts based on web data automatically.

D. It exports your sheet data to a web page.

Solution

1. Step 1: Understand IMPORTXML purpose

   IMPORTXML is designed to pull data from web pages by using a URL and an XPath query to specify what data to extract.

2. Step 2: Compare options

   Only "It fetches data from a web page using a URL and XPath query." correctly describes this function. The other options describe unrelated features.

3. Final Answer:

   It fetches data from a web page using a URL and XPath query. -> Option A

4. Quick Check:

   IMPORTXML = fetch web data [OK]

Hint: IMPORTXML grabs web data using URL + XPath [OK]

Common Mistakes:

• Thinking IMPORTXML only works with other sheets
• Confusing IMPORTXML with chart creation
• Assuming it exports data instead of importing

2. Which of these is the correct syntax for using IMPORTXML to get all <h2> elements from a webpage URL in cell A1?

easy

A. =IMPORTXML(A1, "//h2/@text")

B. =IMPORTXML(A1, "//h2[]")

C. =IMPORTXML(A1, "h2")

D. =IMPORTXML(A1, "//h2")

Solution

1. Step 1: Recall IMPORTXML syntax

   The function takes two arguments: a URL and an XPath query. To select all <h2> elements, the XPath is "//h2".

2. Step 2: Evaluate options

   =IMPORTXML(A1, "//h2") uses correct XPath syntax. =IMPORTXML(A1, "//h2[]") has invalid brackets. =IMPORTXML(A1, "h2") misses the XPath axis. =IMPORTXML(A1, "//h2/@text") tries to get an attribute "text" which doesn't exist.

3. Final Answer:

   =IMPORTXML(A1, "//h2") -> Option D

4. Quick Check:

   Correct XPath syntax = =IMPORTXML(A1, "//h2") [OK]

Hint: Use double slashes and tag name for XPath [OK]

Common Mistakes:

• Adding brackets [] incorrectly in XPath
• Omitting // in XPath
• Trying to get text as attribute with @text

3. Given the formula =IMPORTXML("https://example.com", "//ul/li"), what will the output be?

medium

A. All paragraphs (<p>) from the page.

B. Only the first list item from the page.

C. All list items (<li>) inside unordered lists (<ul>) from the page.

D. An error because XPath is invalid.

Solution

1. Step 1: Understand the XPath query

   The XPath "//ul/li" selects all
2. elements that are children of any <ul> element on the page.

3. Step 2: Predict IMPORTXML output

   IMPORTXML will return all matching list items, not just the first, and it won't return paragraphs or error since XPath is valid.

4. Final Answer:

   All list items (
5. ) inside unordered lists (<ul>) from the page.
-> Option C

6. Quick Check:

   XPath selects all matching nodes = All list items (
7. ) inside unordered lists (<ul>) from the page. [OK]

Hint: XPath //ul/li selects all list items under ul [OK]

Common Mistakes:

• Assuming only first match is returned
• Confusing <li> with <p> tags
• Thinking XPath syntax is wrong here

4. You wrote =IMPORTXML("https://example.com", "//div[@class='price']") but get a #N/A error. What is the likely problem?

medium

A. The URL is invalid or unreachable.

B. The XPath syntax for class attribute is incorrect.

C. IMPORTXML does not support attribute filters.

D. You must use single quotes inside the XPath instead of double quotes.

Solution

1. Step 1: Check XPath syntax

   The XPath "//div[@class='price']" is correct for selecting divs with class 'price'.

2. Step 2: Consider other causes of #N/A

   #N/A often means the URL is unreachable or blocked. IMPORTXML supports attribute filters and double quotes inside XPath strings are allowed if escaped properly.

3. Final Answer:

   The URL is invalid or unreachable. -> Option A

4. Quick Check:

   #N/A often means URL problem [OK]

Hint: Check URL accessibility if #N/A error occurs [OK]

Common Mistakes:

• Assuming XPath syntax is wrong when it's correct
• Not verifying the URL is accessible
• Thinking IMPORTXML can't filter by attributes

5. You want to import the latest news headlines from https://news.example.com where headlines are in <h3 class='headline'> tags. Which formula correctly imports only the text of these headlines?

hard

A. =IMPORTXML("https://news.example.com", "//h3[@class='headline']")

B. =IMPORTXML("https://news.example.com", "//h3[@class='headline']/text()")

C. =IMPORTXML("https://news.example.com", "//h3[@class='headline']/@text")

D. =IMPORTXML("https://news.example.com", "//h3[@class='headline']/innerText")

Solution

1. Step 1: Understand XPath to get text content

   To get only the text inside elements, use the XPath function /text() after selecting the element.

2. Step 2: Evaluate options

   =IMPORTXML("https://news.example.com", "//h3[@class='headline']/text()") correctly uses /text(). =IMPORTXML("https://news.example.com", "//h3[@class='headline']") returns the whole element including tags. =IMPORTXML("https://news.example.com", "//h3[@class='headline']/@text") tries to get an attribute 'text' which doesn't exist. =IMPORTXML("https://news.example.com", "//h3[@class='headline']/innerText") uses invalid XPath syntax.

3. Final Answer:

   =IMPORTXML("https://news.example.com", "//h3[@class='headline']/text()") -> Option B

4. Quick Check:

   Use /text() to get element text [OK]

Hint: Add /text() to XPath to get only text content [OK]

Common Mistakes:

• Omitting /text() and getting full HTML tags
• Using @text which is not an attribute
• Trying invalid XPath like innerText