IMPORTRANGE for cross-spreadsheet data 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. In that spreadsheet, the range Sheet1!A1:B2 contains:

A1: 10
B1: 20
A2: 30
B2: 40

If you enter this formula in your current sheet:

=IMPORTRANGE("1a2b3c4d5e6f7g8h9i0j", "Sheet1!A1:B2")

What will be the output in your sheet?

A 10

B [[10, 20], [30, 40]]

C #REF! error because permission not granted

D [["Sheet1!A1:B2"]]

💡 Hint

IMPORTRANGE imports the exact range as a block of cells.

IMPORTRANGE imports the specified range as a block of cells with their values. Since the range is A1:B2 with values 10,20 in first row and 30,40 in second, the output is a 2x2 array with those numbers.

❓ Function Choice

intermediate

Which formula correctly imports data from another spreadsheet?

You want to import cells A1 to A5 from a spreadsheet with ID abc123xyz789 and sheet named Data. Which formula below will do this correctly?

A =IMPORTDATA("abc123xyz789", "Data!A1:A5")

B =IMPORTXML("abc123xyz789", "Data!A1:A5")

C =IMPORTRANGE("abc123xyz789", "Data!A1:A5")

D =IMPORTRANGE(Data!A1:A5, "abc123xyz789")

💡 Hint

IMPORTRANGE needs the spreadsheet ID first, then the range as text.

IMPORTRANGE requires the spreadsheet ID as the first argument (in quotes), and the range string as the second argument (also in quotes). Only option C follows this syntax correctly.

🎯 Scenario

advanced

You imported data but see a #REF! error. What is the likely cause?

You used =IMPORTRANGE("1x2y3z4w5v6u7t8s9r0q", "Sheet1!A1:C10") but the cell shows #REF! error. What is the most common reason for this error?

A You have not granted permission to access the other spreadsheet yet.

B The spreadsheet ID is invalid and does not exist.

C The range syntax is incorrect; it should be 'Sheet1!A1:C10' without quotes.

D IMPORTRANGE cannot import ranges larger than 5 cells.

💡 Hint

IMPORTRANGE requires you to allow access the first time you use it.

When you first use IMPORTRANGE with a new spreadsheet, Google Sheets asks you to grant permission. Until you do, it shows #REF! error. The other options are incorrect because the ID format is valid, the range string must be quoted, and IMPORTRANGE can import large ranges.

📊 Formula Result

advanced

What is the output of this formula with IMPORTRANGE and QUERY?

You have a spreadsheet with ID sheet123id456. In that spreadsheet, Sales!A1:B4 contains:

A1: Product
B1: Amount
A2: Apple
B2: 100
A3: Banana
B3: 150
A4: Apple
B4: 200

You enter this formula:

=QUERY(IMPORTRANGE("sheet123id456", "Sales!A1:B4"), "select Col1, sum(Col2) where Col1 = 'Apple' group by Col1", 1)

What will be the output?

A #REF! error because QUERY cannot be used with IMPORTRANGE

B [["Apple", 300]]

C [["Product", "Amount"], ["Apple", 300]]

D [["Product", "sum"], ["Apple", 300]]

💡 Hint

QUERY with header row 1 returns headers and aggregated data.

IMPORTRANGE imports the data including headers. QUERY with header=1 treats first row as headers. The query groups by Product 'Apple' and sums Amount 100+200=300. The output includes headers 'Product' and 'sum' and one data row.

❓ data_analysis

expert

How many rows will be imported by this IMPORTRANGE formula?

You have a spreadsheet with ID xyz987abc654. The sheet Data has 1000 rows filled in columns A to D.

You enter this formula:

=IMPORTRANGE("xyz987abc654", "Data!A2:D501")

How many rows and columns will be imported?

A 500 rows and 4 columns

B 501 rows and 4 columns

C 499 rows and 4 columns

D 500 rows and 5 columns

💡 Hint

Count rows from A2 to D501 inclusive.

The range A2:D501 includes rows 2 through 501, which is 501 - 2 + 1 = 500 rows. Columns A to D is 4 columns. So the imported data is 500 rows by 4 columns.