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']