What you will build
Diagnose the two common IMPORTRANGE permission states: a first-time #REF! connection that needs Allow access, and a source file you cannot read yet. You will connect the verified Catalog range and confirm the imported rows without making the source publicly editable.
Learning objective
By the end, you can tell a connection-authorization prompt from a source-sharing problem, use the verified source ID and range, grant access only from the intended destination, and verify the imported result.
Prepare the sample data
Paste this example as tab-separated values beginning at A1 on a new worksheet:
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
This lesson uses a separate real source workbook. Open the source first and confirm the Catalog tab contains SKU | Price, P01 | 12, and P02 | 35 in A1:B3.
Open the real IMPORTRANGE source workbook — Catalog!A1:B3 ↗
How to get the source URL: with the source workbook open, copy the full URL from the browser address bar (or use Share > Copy link). In a Google Sheets URL shaped https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit, the spreadsheet ID is the text between /d/ and /edit. For this lesson the ID is 1hjtKz7Ro4oMdcaUDr_Qm5q7zwSHd7u0ASiLKNkfyBqI. Use that exact ID with the source range Catalog!A1:B3.
Open the sample workbook in Google Drive (access required) ↗
Work through the example
Step 1: In the destination workbook, select H2 and enter =IMPORTRANGE("1hjtKz7Ro4oMdcaUDr_Qm5q7zwSHd7u0ASiLKNkfyBqI","Catalog!A1:B3"). Wait for Google Sheets to evaluate the formula.
Step 2: If H2 shows #REF! with “You need to connect these sheets,” select H2 and click Allow access. This authorizes this destination workbook to read the source; it does not make the source public.
Step 3: Confirm H2:I4 spills SKU | Price, P01 | 12, and P02 | 35. If Sheets instead says you do not have permission to access the source, open the linked source workbook and request Viewer access from its owner first; after access is granted, return to H2 and retry the connection.
Formula to try
Use H2 in the verified destination workbook. The formula points to the lesson’s real source workbook and imports Catalog!A1:B3.
=IMPORTRANGE("1hjtKz7Ro4oMdcaUDr_Qm5q7zwSHd7u0ASiLKNkfyBqI","Catalog!A1:B3")Checkpoint
| SKU | Price |
|---|---|
| P01 | 12 |
| P02 | 35 |
Verified live after Allow access: the source Catalog!A1:B3 spills into H2:I4 as SKU/Price, P01/12 and P02/35.
Common mistake and correction
After the correct permission path is complete, select H2 and verify the formula bar still contains the IMPORTRANGE formula and H2:I4 contains the three-row Catalog result.
Independent practice
For another private source you are authorized to use, write down the source owner, destination workbook, exact tab/range, and intended sharing scope. Predict whether you expect a source-sharing request or the one-time Allow access prompt before entering the formula.
Key takeaways
- ✓IMPORTRANGE requires a real source spreadsheet and an existing tab/range.
- ✓A first-time #REF! that says the sheets must be connected is resolved with Allow access in the destination.
- ✓If your account cannot read the source file, obtain source sharing permission before retrying IMPORTRANGE.
- ✓Allow access connects the intended source and destination; it does not require public Editor sharing.
- ✓For this lesson, Catalog!A1:B3 imports into H2:I4 as SKU/Price, P01/12, and P02/35.
Check your understanding
Answer four questions about the practice sheet.
Continue learning
Previous lesson: Fix circular dependency errors; Next lesson: Fix incorrect date formats.
Comments
No comments yet. Be the first to join the discussion.