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.