IMPORTRANGE for other spreadsheets in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this IMPORTRANGE formula?

You have a spreadsheet with ID 1a2b3c4d5e6f7g8h9i0j. The sheet named Data has values A1=10, A2=20. What will be the result in your current sheet if you enter this formula in cell A1?

=IMPORTRANGE("1a2b3c4d5e6f7g8h9i0j", "Data!A1:A2")

A A1=10, A2=20 (values imported correctly)

B #REF! error because of missing permission

C #VALUE! error due to wrong range format

D Empty cells because source sheet is empty

💡 Hint

IMPORTRANGE imports data from another spreadsheet if you have access and use correct syntax.

IMPORTRANGE imports the exact values from the specified range in the other spreadsheet. Since the range is correct and you have access, the values 10 and 20 appear in A1 and A2.

❓ Function Choice

intermediate

Which formula correctly imports cells B1 to B5 from another spreadsheet?

You want to import cells B1 to B5 from the sheet named Sheet1 in another spreadsheet with ID abcd1234efgh5678. Which formula below is correct?

A =IMPORTRANGE("abcd1234efgh5678", "Sheet1!B1-B5")

B =IMPORTRANGE("abcd1234efgh5678", "Sheet1!B1:B5")

C =IMPORTRANGE("abcd1234efgh5678", "Sheet1:B1:B5")

D =IMPORTRANGE("abcd1234efgh5678", "Sheet1!B1;B5")

💡 Hint

The range must use a colon (:) between start and end cells.

The correct range format uses an exclamation mark after the sheet name and a colon between start and end cells, like "Sheet1!B1:B5".

🎯 Scenario

advanced

You want to import data but get a #REF! error. What is the cause?

You entered the formula =IMPORTRANGE("xyz987654321", "Sales!A1:C10") but see a #REF! error. What is the most likely reason?

A The range syntax is incorrect

B The spreadsheet ID is too long

C You have not granted permission to access the other spreadsheet yet

D IMPORTRANGE does not support ranges with more than 5 columns

💡 Hint

IMPORTRANGE requires permission to access data from another spreadsheet.

When you use IMPORTRANGE for the first time with a new spreadsheet, Google Sheets asks you to grant permission. Until you do, it shows #REF! error.

📊 Formula Result

advanced

What is the output of this IMPORTRANGE combined with QUERY?

You have a spreadsheet with ID 123abc456def and sheet Inventory with columns A (Item), B (Quantity). You enter this formula:

=QUERY(IMPORTRANGE("123abc456def", "Inventory!A1:B10"), "select Col1 where Col2 > 5", 1)

What will this formula return?

A Empty result because QUERY cannot use IMPORTRANGE

B All items regardless of quantity

C #REF! error due to missing permission

D List of items where quantity is greater than 5

💡 Hint

QUERY can filter imported data if permission is granted.

IMPORTRANGE imports the data, and QUERY filters it to show only items with quantity > 5. The header row is included because of the last argument 1.

❓ data_analysis

expert

How many rows will be imported by this formula?

You use this formula:

=IMPORTRANGE("zxy987654321", "Sheet2!A2:A")

The source sheet Sheet2 has 100 rows filled in column A starting from A1. How many rows will be imported?

A 99 rows (from A2 to A100)

B 100 rows (including A1)

C All rows including empty ones below row 100

D Only 1 row (A2)

💡 Hint

The range starts at A2 and goes down to the last filled row.

The range "A2:A" means from row 2 down all rows in column A. Since rows 1 to 100 are filled, starting at A2 means 99 rows imported.

Practice

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

easy

A. It imports data from one Google Sheet to another using a URL and range.

B. It exports data from Google Sheets to a CSV file.

C. It creates a chart based on selected data.

D. It sorts data within the same sheet.

Solution

1. Step 1: Understand the purpose of IMPORTRANGE

   The IMPORTRANGE function is designed to pull data from a different Google Sheet using its URL or key and a specified range.

2. Step 2: Compare options with the function's purpose

   Only It imports data from one Google Sheet to another using a URL and range. correctly describes this behavior. Options A, B, and D describe unrelated functions.

3. Final Answer:

   It imports data from one Google Sheet to another using a URL and range. -> Option A

4. Quick Check:

   IMPORTRANGE imports data between sheets [OK]

Hint: Remember: IMPORTRANGE pulls data from another sheet [OK]

Common Mistakes:

• Confusing IMPORTRANGE with export functions
• Thinking it sorts or charts data
• Assuming it works within the same sheet only

2. Which of the following is the correct syntax to import cells A1 to B5 from another spreadsheet using IMPORTRANGE?

easy

A. =IMPORTRANGE(spreadsheet_url, "A1:B5")

B. =IMPORTRANGE(spreadsheet_url, A1:B5)

C. =IMPORTRANGE("spreadsheet_url", A1:B5)

D. =IMPORTRANGE("spreadsheet_url", "A1:B5")

Solution

1. Step 1: Check the syntax requirements for IMPORTRANGE

   The first argument must be a string with the spreadsheet URL or key in quotes. The second argument must be a string with the range in quotes.

2. Step 2: Identify the correct option

   =IMPORTRANGE("spreadsheet_url", "A1:B5") correctly uses quotes around both arguments. Options A and B miss quotes around the URL or range, and C misses quotes around the range.

3. Final Answer:

   =IMPORTRANGE("spreadsheet_url", "A1:B5") -> Option D

4. Quick Check:

   Both URL and range must be quoted strings [OK]

Hint: Always put URL and range inside quotes [OK]

Common Mistakes:

• Forgetting quotes around URL or range
• Using cell references without quotes
• Using commas inside range string

3. Given the formula =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!C2:C4"), what will be the output if Sheet1 cells C2, C3, and C4 contain 10, 20, and 30 respectively?

medium

A. 10

B. [10, 20, 30]

C. Error: Access denied

D. 30

Solution

1. Step 1: Understand the range and data

   The range "Sheet1!C2:C4" includes three cells with values 10, 20, and 30.

2. Step 2: Determine IMPORTRANGE output

   IMPORTRANGE imports the entire range as an array of values, so it returns [10, 20, 30].

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   IMPORTRANGE returns all cells in the range [OK]

Hint: IMPORTRANGE returns all cells in the specified range [OK]

Common Mistakes:

• Expecting only the first cell value
• Confusing output with error messages
• Not recognizing array output

4. You entered =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:A3") but see a #REF! error. What is the most likely fix?

medium

A. Click the cell and allow access to the other spreadsheet.

B. Change the range to "A1:A2".

C. Remove quotes around the URL.

D. Use a different function like VLOOKUP.

Solution

1. Step 1: Understand #REF! error with IMPORTRANGE

   #REF! often appears when access permission to the source spreadsheet is not granted yet.

2. Step 2: Identify the fix

   Clicking the cell usually prompts a permission request to allow access. Granting access fixes the error.

3. Final Answer:

   Click the cell and allow access to the other spreadsheet. -> Option A

4. Quick Check:

   Grant access to fix #REF! error [OK]

Hint: Grant permission when #REF! appears with IMPORTRANGE [OK]

Common Mistakes:

• Changing range unnecessarily
• Removing quotes causing syntax errors
• Switching functions without reason

5. You want to import the range B2:D4 from a spreadsheet with URL https://docs.google.com/spreadsheets/d/xyz789 but only if the values in column B are greater than 50. Which formula correctly combines IMPORTRANGE and FILTER to do this?

hard

A. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4") > 50

B. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", FILTER("Sheet1!B2:D4", "Sheet1!B2:B4" > 50))

C. =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50)

D. =FILTER("Sheet1!B2:D4", IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50)

Solution

1. Step 1: Understand combining IMPORTRANGE and FILTER

   IMPORTRANGE imports data, FILTER filters rows based on a condition. We must import the full range and separately import the column to filter on.

2. Step 2: Analyze each option

   =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) imports the full range and filters rows where column B values are > 50 using a second IMPORTRANGE call for column B. This is correct.
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", FILTER("Sheet1!B2:D4", "Sheet1!B2:B4" > 50)) incorrectly tries to use FILTER inside IMPORTRANGE, which is invalid.
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4") > 50 compares the entire imported range to 50, which is invalid.
   =FILTER("Sheet1!B2:D4", IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) tries to filter a string range without importing it first, which is invalid.

3. Final Answer:

   =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) -> Option C

4. Quick Check:

   Use FILTER on IMPORTRANGE data with condition on imported column [OK]

Hint: Use FILTER on IMPORTRANGE twice: full range and condition column [OK]

Common Mistakes:

• Trying to FILTER inside IMPORTRANGE
• Comparing entire range directly to a number
• Filtering a range string without importing