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.

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 Excel files.

C. It creates charts based on data in the current sheet.

D. It sorts data within the same sheet automatically.

Solution

1. Step 1: Understand the purpose of IMPORTRANGE

   The IMPORTRANGE function is designed to bring data from one Google Sheet into another by specifying the source sheet's URL and the range of cells to import.

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, C, 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: IMPORTRANGE connects sheets using URL and range [OK]

Common Mistakes:

• Thinking IMPORTRANGE exports data instead of importing
• Confusing IMPORTRANGE with sorting or chart functions
• Assuming it works only within the same sheet

2. Which of the following is the correct syntax for the IMPORTRANGE function?

easy

A. =IMPORTRANGE(range_string)

B. =IMPORTRANGE(range_string, spreadsheet_url)

C. =IMPORTRANGE(spreadsheet_url)

D. =IMPORTRANGE(spreadsheet_url, range_string)

Solution

1. Step 1: Recall the IMPORTRANGE syntax

   The correct syntax requires the spreadsheet URL first, then the range as a string, like =IMPORTRANGE(spreadsheet_url, range_string).

2. Step 2: Check each option

   =IMPORTRANGE(spreadsheet_url, range_string) matches the correct syntax. =IMPORTRANGE(range_string, spreadsheet_url) reverses the parameters, and options A and C miss the required second argument.

3. Final Answer:

   =IMPORTRANGE(spreadsheet_url, range_string) -> Option D

4. Quick Check:

   Syntax is URL first, then range [OK]

Hint: URL comes first, range second in IMPORTRANGE [OK]

Common Mistakes:

• Swapping the order of URL and range
• Omitting the range argument
• Not enclosing range in quotes

3. Given the formula =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:B2"), what will be imported?

medium

A. The entire Sheet1 from the spreadsheet with ID abc123.

B. Only cell A1 from Sheet1 in the spreadsheet with ID abc123.

C. Cells A1 to B2 from Sheet1 in the spreadsheet with ID abc123.

D. Cells A1 to B2 from the current sheet.

Solution

1. Step 1: Analyze the range string

   The range string "Sheet1!A1:B2" specifies cells from A1 to B2 on Sheet1, which is a 2-row by 2-column block.

2. Step 2: Understand IMPORTRANGE behavior

   IMPORTRANGE imports exactly the specified range from the external spreadsheet URL, so it will bring those four cells.

3. Final Answer:

   Cells A1 to B2 from Sheet1 in the spreadsheet with ID abc123. -> Option C

4. Quick Check:

   Range string defines exact cells imported [OK]

Hint: Range like Sheet1!A1:B2 imports that exact block [OK]

Common Mistakes:

• Assuming it imports the whole sheet
• Thinking only one cell is imported
• Confusing current sheet with external sheet

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

medium

A. Change the range to "Sheet1!A1:B10".

B. Click the cell and allow access to the source spreadsheet.

C. Remove the quotes around the URL.

D. Use the IMPORTRANGE function without the URL.

Solution

1. Step 1: Understand the #REF! error cause

   The #REF! error often appears because Google Sheets requires permission to access the source spreadsheet before importing data.

2. Step 2: Fix by granting access

   Clicking the cell with the formula prompts a permission request. Allowing access resolves the error.

3. Final Answer:

   Click the cell and allow access to the source spreadsheet. -> Option B

4. Quick Check:

   Permission needed to import data [OK]

Hint: Allow access prompt fixes #REF! in IMPORTRANGE [OK]

Common Mistakes:

• Changing range without checking permissions
• Removing quotes causing syntax errors
• Omitting URL which breaks the formula

5. You want to import data from two different sheets in another spreadsheet and combine them vertically in your current sheet. Which formula setup is correct?

hard

A. =ARRAYFORMULA({IMPORTRANGE("url", "Sheet1!A1:B5"); IMPORTRANGE("url", "Sheet2!A1:B5")})

B. =IMPORTRANGE("url", "Sheet1!A1:B5") + IMPORTRANGE("url", "Sheet2!A1:B5")

C. =IMPORTRANGE("url", "Sheet1!A1:B5") & IMPORTRANGE("url", "Sheet2!A1:B5")

D. =IMPORTRANGE("url", "Sheet1!A1:B5, Sheet2!A1:B5")

Solution

1. Step 1: Understand combining ranges vertically

   To stack two ranges vertically, use curly braces with a semicolon between ranges inside ARRAYFORMULA.

2. Step 2: Analyze each option

   =ARRAYFORMULA({IMPORTRANGE("url", "Sheet1!A1:B5"); IMPORTRANGE("url", "Sheet2!A1:B5")}) correctly uses {range1; range2} inside ARRAYFORMULA to combine vertically. =IMPORTRANGE("url", "Sheet1!A1:B5") + IMPORTRANGE("url", "Sheet2!A1:B5") tries to add ranges (invalid). =IMPORTRANGE("url", "Sheet1!A1:B5") & IMPORTRANGE("url", "Sheet2!A1:B5") uses & which concatenates text, not ranges. =IMPORTRANGE("url", "Sheet1!A1:B5, Sheet2!A1:B5") tries to specify two ranges in one IMPORTRANGE call, which is invalid.

3. Final Answer:

   =ARRAYFORMULA({IMPORTRANGE("url", "Sheet1!A1:B5"); IMPORTRANGE("url", "Sheet2!A1:B5")}) -> Option A

4. Quick Check:

   Use {range1; range2} to stack ranges vertically [OK]

Hint: Use curly braces with semicolon to stack IMPORTRANGE results [OK]

Common Mistakes:

• Trying to add ranges with + operator
• Using & to combine ranges instead of text
• Passing multiple ranges in one IMPORTRANGE call