IMPORTRANGE for cross-spreadsheet data in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to import data from another spreadsheet using IMPORTRANGE.

Google Sheets

=IMPORTRANGE([1], "Sheet1!A1:B10")

Drag options to blanks, or click blank then click option'

ASheet1!A1:B10

Babc123xyz

C"A1:B10"

D"https://docs.google.com/spreadsheets/d/abc123xyz"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around the URL

Using the range as the first argument

The first argument of IMPORTRANGE is the URL or the spreadsheet key in quotes. Here, the full URL in quotes is correct.

2fill in blank

medium

Complete the formula to import range from a spreadsheet key instead of full URL.

Google Sheets

=IMPORTRANGE([1], "Data!C1:D20")

Drag options to blanks, or click blank then click option'

AData!C1:D20

B"Data!C1:D20"

C"1a2b3c4d5e6f7g8h9i0j"

D1a2b3c4d5e6f7g8h9i0j

Attempts:

3 left

💡 Hint

Common Mistakes

Using the key without quotes

Putting the range as the first argument

The spreadsheet key must be a text string in quotes as the first argument.

3fill in blank

hard

Fix the error in the formula to correctly import data from another spreadsheet.

Google Sheets

=IMPORTRANGE([1], "Sheet1!A1:B5")

Drag options to blanks, or click blank then click option'

A"https://docs.google.com/spreadsheets/d/xyz987abc"

Bhttps://docs.google.com/spreadsheets/d/xyz987abc

C"Sheet1!A1:B5"

DSheet1!A1:B5

Attempts:

3 left

💡 Hint

Common Mistakes

Missing quotes around URL

Missing quotes around range

The first argument must be a text string with quotes. Also, the second argument (range) must be in quotes. Here, the blank is for the first argument, so the correct answer is the URL in quotes.

4fill in blank

hard

Fill both blanks to import data from a spreadsheet key and specify the correct range.

Google Sheets

=IMPORTRANGE([1], [2])

Drag options to blanks, or click blank then click option'

A"1q2w3e4r5t6y7u8i9o0p"

BSheet2!A1:C10

C"Sheet2!A1:C10"

D1q2w3e4r5t6y7u8i9o0p

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the range

Using the key without quotes

The first argument is the spreadsheet key in quotes, and the second argument is the range also in quotes.

5fill in blank

hard

Fill all three blanks to create a formula that imports data and uses ARRAYFORMULA to double the values in column A.

Google Sheets

=ARRAYFORMULA(IMPORTRANGE([1], [2]) * [3])

Drag options to blanks, or click blank then click option'

A"2z3x4c5v6b7n8m9l0k"

B"Sheet1!A1:A10"

C2

D"Sheet1!B1:B10"

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the key or range

Using a string instead of number for multiplication

The IMPORTRANGE uses the spreadsheet key and range in quotes. The ARRAYFORMULA multiplies the imported column by 2 to double the values.

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