IMPORTHTML for web tables in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What does this IMPORTHTML formula return?

Given the formula =IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 1) in cell A1, what will be the output in the first row of the imported table?

A A blank cell because the table index is incorrect

B The first country's name and population data only

C An error because the URL is invalid

D The header row with column titles like 'Country or area' and 'Population (1 July 2023)'

💡 Hint

IMPORTHTML imports the entire table including headers, not just data rows.

The IMPORTHTML function imports the entire HTML table specified by the index. The first row is usually the header row with column titles.

❓ Function Choice

intermediate

Which formula correctly imports the second table from a webpage?

You want to import the second table from https://example.com/data. Which formula will do this correctly?

A =IMPORTDATA("https://example.com/data", "table", 2)

B =IMPORTHTML("https://example.com/data", "list", 2)

C =IMPORTHTML("https://example.com/data", "table", 2)

D =IMPORTHTML("https://example.com/data", "table", 1)

💡 Hint

The third argument is the table index starting at 1.

IMPORTHTML with type 'table' and index 2 imports the second table on the page. 'list' is for lists, IMPORTDATA is for CSV/TSV files.

🎯 Scenario

advanced

You want to import a table but get a #N/A error. What is the likely cause?

You used =IMPORTHTML("https://example.com/page", "table", 1) but the cell shows #N/A. What is the most likely reason?

A The webpage does not have any tables or the table index is wrong

B The formula syntax is incorrect

C Google Sheets does not support IMPORTHTML

D Your internet connection is offline

💡 Hint

Check if the webpage actually contains tables and the index matches.

IMPORTHTML returns #N/A if the specified table index does not exist or the page has no tables. Syntax errors cause different errors.

📊 Formula Result

advanced

What happens if you use IMPORTHTML with a list type on a page with no lists?

Formula: =IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "list", 1)
What will be the result?

A #N/A error because there are no lists on the page

B The first table on the page is imported instead

C An empty range with no data

D The entire webpage content is imported as text

💡 Hint

IMPORTHTML requires the specified element type to exist on the page.

If the page has no lists, IMPORTHTML returns #N/A because it cannot find the requested element.

❓ data_analysis

expert

How many rows will be imported by this formula?

You use =IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 1). The first table has 235 rows including the header. How many rows will appear in your sheet?

A 234 rows excluding the header row

B 235 rows including the header row

C 236 rows because an extra empty row is added

D Only the first 100 rows due to Google Sheets limits

💡 Hint

IMPORTHTML imports the entire table as is, including headers.

IMPORTHTML imports the full table including the header row, so all 235 rows appear.

Practice

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

easy

A. It imports tables or lists from a web page into your spreadsheet.

B. It imports images from a web page into your spreadsheet.

C. It exports your spreadsheet data to a web page.

D. It creates charts based on web page data.

Solution

1. Step 1: Understand IMPORTHTML purpose

   The IMPORTHTML function is designed to pull data from web pages, specifically tables or lists.

2. Step 2: Identify correct data type imported

   It imports structured data like tables or lists, not images or charts.

3. Final Answer:

   It imports tables or lists from a web page into your spreadsheet. -> Option A

4. Quick Check:

   IMPORTHTML imports tables/lists [OK]

Hint: IMPORTHTML grabs tables or lists from URLs [OK]

Common Mistakes:

• Thinking it imports images or charts
• Confusing IMPORTHTML with export functions
• Assuming it imports unstructured text

2. Which of the following is the correct syntax to import the first table from a webpage using IMPORTHTML?

easy

A. =IMPORTHTML("http://example.com", "list", 1)

B. =IMPORTHTML("http://example.com", "table")

C. =IMPORTHTML("http://example.com", "table", 1)

D. =IMPORTHTML("http://example.com", 1, "table")

Solution

1. Step 1: Recall IMPORTHTML syntax

   The syntax is IMPORTHTML(url, query, index) where query is "table" or "list" and index is the number of the table or list.

2. Step 2: Check each option

   =IMPORTHTML("http://example.com", "table", 1) matches the correct syntax with url, "table", and index 1. =IMPORTHTML("http://example.com", "list", 1) uses "list" instead of "table". =IMPORTHTML("http://example.com", "table") misses the index. =IMPORTHTML("http://example.com", 1, "table") has parameters in wrong order.

3. Final Answer:

   =IMPORTHTML("http://example.com", "table", 1) -> Option C

4. Quick Check:

   Correct syntax = IMPORTHTML(url, "table", index) [OK]

Hint: Syntax: IMPORTHTML(URL, "table" or "list", index) [OK]

Common Mistakes:

• Omitting the index number
• Swapping parameter order
• Using wrong query type like "list" when table needed

3. Given the formula =IMPORTHTML("https://example.com/data", "table", 2), what will it do?

medium

A. Import the first list from the webpage at https://example.com/data

B. Import the second table from the webpage at https://example.com/data

C. Import the second list from the webpage at https://example.com/data

D. Import the first table from the webpage at https://example.com/data

Solution

1. Step 1: Analyze the IMPORTHTML parameters

   The formula uses "table" as the query and 2 as the index, so it targets the second table on the page.

2. Step 2: Match parameters to options

   Import the second table from the webpage at https://example.com/data correctly states it imports the second table. Options A and C mention lists, which is incorrect. Import the first table from the webpage at https://example.com/data mentions first table, which is wrong index.

3. Final Answer:

   Import the second table from the webpage at https://example.com/data -> Option B

4. Quick Check:

   "table" + 2 = second table imported [OK]

Hint: Index number picks which table or list to import [OK]

Common Mistakes:

• Confusing 'table' with 'list'
• Mixing up index numbers
• Assuming it imports all tables at once

4. You wrote =IMPORTHTML("https://example.com", "table", "first") but get an error. What is the problem?

medium

A. The formula needs an extra parameter for headers.

B. The URL must end with .html to work.

C. The query parameter should be "list" instead of "table".

D. The index parameter must be a number, not text.

Solution

1. Step 1: Check the index parameter type

   The third parameter must be a number indicating which table or list to import. "first" is text, causing an error.

2. Step 2: Verify other parameters

   The URL can be any valid URL, no need to end with .html. The query "table" is valid. No extra parameter for headers is required.

3. Final Answer:

   The index parameter must be a number, not text. -> Option D

4. Quick Check:

   Index must be numeric [OK]

Hint: Index must be a number, not words [OK]

Common Mistakes:

• Using text instead of number for index
• Thinking URL must end with .html
• Adding unnecessary parameters

5. You want to import the third table from a webpage but the page has only two tables. What will =IMPORTHTML("https://example.com", "table", 3) return?

hard

A. It will return an error indicating the table does not exist.

B. It will import the second table instead.

C. It will import an empty table with no data.

D. It will import the first table by default.

Solution

1. Step 1: Understand IMPORTHTML behavior with invalid index

   If the index number is higher than the number of tables on the page, IMPORTHTML cannot find the requested table.

2. Step 2: Result of requesting non-existent table

   Google Sheets returns an error because the requested table does not exist; it does not default to another table or return empty data.

3. Final Answer:

   It will return an error indicating the table does not exist. -> Option A

4. Quick Check:

   Invalid index causes error [OK]

Hint: Index must not exceed available tables [OK]

Common Mistakes:

• Assuming it imports closest available table
• Expecting empty data instead of error
• Thinking it defaults to first table