To use IMPORTRANGE in Google Sheets, enter the source spreadsheet’s URL and a quoted sheet-and-range reference. For example, =IMPORTRANGE(B1,"’Club Register’!A1:D100") reads the URL in B1 and brings columns A through D from the Club Register tab into the destination.
The first connection between the files normally requires permission. If Sheets shows #REF! with a prompt to connect the sheets, choose Allow access after confirming the source and destination. If the message says you lack permission to the source, request access through the source owner before attempting the connection.
- Use IMPORTRANGE for a different spreadsheet file.
- Put the source URL in one clearly labelled cell.
- Leave space for the result to expand.
- Import only the fields the destination needs, and review access to the source and destination.
- Check freshness separately from whether the formula displays without error.
Connect a destination to its source
Open the destination spreadsheet. Put the source file’s full URL in B1. Choose an empty output location such as A4. If the source tab is named Club Register and its headers are in row one, enter the following formula in A4.
=IMPORTRANGE(B1,"'Club Register'!A1:D100")The first argument identifies the spreadsheet file. The second identifies the tab and range inside it. The single quotation marks around Club Register protect the tab name containing a space. The entire range reference is enclosed in double quotation marks because it is text passed to the function.
Check the first header, one known data row and the last expected record. The formula can return a perfectly valid block from the wrong tab, so confirm its contents rather than relying on the absence of an error. If the source has a similar tab for last term, open the source and verify its name and period.
Check the source tab name after a rename
The tab reference is inside a text string. If a colleague renames the source tab, inspect the destination formula and update the string where needed. Check the exact punctuation and spacing. A browser title shows the spreadsheet’s name, while the tab label identifies the worksheet inside it. Use the worksheet label in the second argument.
Use a bounded range deliberately
A1:D100 is easy to audit. A row added at 101 is outside it. Choose the last row based on the actual workflow and test the first new row after an update. Open-ended ranges can accommodate growth, and they can also include unrelated notes farther down or increase the amount being requested. A dedicated source tab with a clearly defined data area makes the choice easier.
Understand what Allow access connects
When a destination first connects to a source, Google Sheets can display #REF! and offer Allow access. Verify that you are editing the intended destination and authorised to access the source. If your account does not have source access, use its approved sharing process. A copied URL identifies a file. It does not grant permission to read it.
Review both audiences before creating the connection
Google’s help explains that after access is granted, editors of the destination can use IMPORTRANGE to pull from other parts of the connected source. Selecting A1:D100 limits that formula’s output. It is not a security boundary around the rest of the source file. For example, a staff spreadsheet might contain a public club schedule on one tab and private contact details on another. If the intended destination audience should see only the schedule, use a separately prepared and approved source file containing just that material.
Also consider viewers of the destination. People who can see the destination can see the data displayed there under its sharing rules. Review the intended audience before exposing the import. When the task requires an external recipient to receive only a verified result, use the organisation’s approved delivery method.
A copied destination may need its own check
If a colleague makes a copy of the reporting spreadsheet, open the copy and inspect the import and its account permissions. Test whether it has the connection it needs and whether that connection is appropriate. Avoid assuming a copy will use the same approved audience or working source. A copied dashboard can also still point at an earlier term’s file.
Fix the main IMPORTRANGE errors
The connection prompt appears
Read the full error and use the access control only after verifying the files. When access is granted, wait for the data to load and compare it with a known source row. If it remains unavailable, check the source account and the exact prompt instead of repeatedly rewriting the range.
The range cannot be found
Open the source and confirm the tab name and reference. Check that the URL identifies the right spreadsheet. Try a small known range from that tab in an authorised test cell. If the small example works, compare its punctuation and row limits with the full formula. Confirm whether the source uses a named range or tab reference.
The output cannot expand
Inspect the cells below and to the right of the formula. A header, note, merged cell or another result may occupy part of the intended space. Move the import to a free area or remove only the conflict you are authorised to change. Edit the source to change its records. The destination cells are produced by the formula.
It is loading or is slower than expected
Check whether you are importing more rows or columns than the report needs. Google recommends condensing and summarising the data in the source when practical and limiting chains of imported spreadsheets. A small approved summary can move less data than a complete ledger. Compare a known source record and the actual destination result before assuming a delay means the source is wrong.
Keep live reports and submitted copies distinct
A live import is useful for a report that should follow the source. A submission or monthly handoff can require a fixed record. Make the authorised snapshot only after verifying the imported values and their freshness. Keep the live report and snapshot clearly named. If the source changes later, the snapshot should still represent the version that was actually shared.
Avoid a long chain of dependent files
Suppose a raw ledger feeds a club spreadsheet, which feeds a report, which feeds a second dashboard. One change can depend on several separate imports before it appears in the final view. Check each link when diagnosing a stale number. When appropriate, have the report connect to an approved summary source directly. Keep the design simple enough that the responsible person can identify where a figure came from.
Check the source before relying on the latest update
A formula displaying values does not prove that the last edit has arrived. Compare a known recent record with the destination. Allow for the documented refresh behaviour and any calculation delays. If a deadline requires a particular state, verify it explicitly before exporting. A timestamp field added by the source team can help when it genuinely represents the source version.
Combine an import with a reporting formula carefully
For a shared workbook, you can import an approved source block to a dedicated tab, then use local QUERY, FILTER or a pivot on that block. This provides a place to inspect the incoming values before interpreting the report. Test the import on its own before adding another formula. If a query fails, you can then distinguish a connection error from a query error.
Design a source-to-report test
Put five fictional club records into a source copy. Import the approved fields into a destination tab. Check a category, a number and the final row. Change one amount and verify the imported value after it refreshes. Add a row just outside a fixed range and see why it does not appear. Extend the range where appropriate and confirm the record now participates in the report.
Label the report’s inputs
State the source tab or approved reporting table, and identify any filters used in the destination. A report labelled “Approved only” should be checked against both a pending and an approved record. Do not infer from an accurate imported row that the downstream filter is also accurate. Verify each stage.
Decide whether a direct sheet reference is enough
If the data is in another tab of the same spreadsheet, a local reference is often enough. For example, =’Club Register’!A2 refers to A2 in that tab. IMPORTRANGE is for a different spreadsheet file. Choose based on where the source actually lives. This avoids adding a cross-file access connection when you only need a reference inside one workbook.
Use the least complicated source for the task
An approved export can be appropriate for a one-off submission. A local reference can suit several tabs in one workbook. A cross-file import can suit an ongoing report with a managed source. Compare the need for freshness, the intended audience and who will maintain the connection. Check a known record after choosing the workflow.
Walk through a shared report with the right access
Imagine a school activities coordinator keeps a working register. Its columns include Session, Venue, Capacity and Published status, as well as staff-only notes in the same file. A group of volunteers needs to see the published schedule and help maintain a separate report. Before creating the connection, decide which information volunteers may receive and which file can safely be connected to a destination they edit.
Put the permitted material in an approved source file
The coordinator can prepare a source file containing only the approved schedule fields. Check its entire contents, including other tabs and notes below the schedule. Then connect the volunteer report to that file. A narrower formula range controls what that particular result shows, while the approved source file controls what other ranges destination editors could request through the connection.
Check the destination as a reader and an editor
A viewer can see data already displayed in the destination. An editor can work with the destination formula and, after authorisation, can use the connection as documented by Google. Decide which roles the audience needs. Review the sharing settings and the actual displayed results. Use an approved snapshot when a recipient needs a fixed copy or when the required access model does not suit a live connection.
Verify the information beyond the visible table
A source file prepared for sharing should have a clear scope. Check whether an unused tab contains a copy of raw data or whether another report beneath the visible area contains sensitive details. Review the destination too. A later formula that imports from an unintended source can change what readers see. Assign ownership of the source and destination review under the organisation’s process.
Use a named range or table reference where it suits the source
Google documents that IMPORTRANGE can refer to named ranges, and its help also describes supported table references. A named range can give the approved data block a readable name. In the source spreadsheet, create or verify the named range under the spreadsheet owner’s workflow. If the approved range is named PublishedSchedule, the destination can use the following pattern.
=IMPORTRANGE(B1,"PublishedSchedule")Check the name in the source and confirm exactly which rows and columns it covers. A readable name is helpful when another editor needs to understand the connection. It does not guarantee that a newly inserted row belongs to the named range. Check a new record near the end and verify whether the source maintainer needs to extend the definition.
Choose stable headings for a downstream formula
Suppose the source moves the Status field from column D to E. An import might still succeed, while a downstream query now checks the wrong position. Agree which columns the reporting source exposes and keep their order stable where practical. After a structure change, inspect the imported headers and update the dependent logic deliberately. A value in the expected output column should still mean what its heading says.
Check what happens after a source name changes
A team can rename the tab or change the named range. The destination formula contains a text reference. Check that reference after the change and verify a known record. When there are several destinations, review them under the approved process before changing the source’s public reporting shape. Keep the source owner informed about which reports depend on it.
Reduce the data moved between spreadsheets
Google recommends condensing the data at the source where practical. Suppose the recipient needs monthly totals by club rather than every transaction. Prepare a source summary with Month, Club and Approved amount. Then import that smaller block. Check a subtotal in the source and the corresponding destination value. This keeps the external request focused on the actual report.
Distinguish local filtering from a smaller external import
If a destination imports A1:Z10000 and then runs a local query returning five rows, the external request still starts from the large range. A local query can make the displayed output smaller. It does not automatically make the incoming range smaller. If transfer size or access scope is the issue, design the approved source to expose only what the destination needs.
Avoid repeating the same import in many places
If several calculations use the same approved data block, consider importing it once into a dedicated destination tab and using local formulas against that tab. This gives the reviewer a single place to compare data and makes the report’s dependency easier to understand. Check the destination scope and keep unrelated notes out of its result area.
Check the documented limit when the range is large
Google states that IMPORTRANGE is capped at 10 MB of received data per request. Rows with long text can make a range large even when its row count looks modest. If you hit a limit or load delays, test a small sample and inspect what the report actually needs. Summarise in the source or select a smaller approved range where appropriate. Do not split one huge dataset into many interdependent requests by habit.
Diagnose stale data across a chain
Imagine Report imports from Summary, and Summary imports from Raw. A recent edit is visible in Raw but absent from Report. Open Summary and find the same record. If it has not arrived there, inspect Summary’s connection and range first. If Summary is current and Report is not, inspect the second connection and its output. Follow the record through each stage.
Compare one record rather than two entire totals
Choose a distinctive ID or recent approved row and inspect its value at every stage. Totals can remain unchanged when two edits offset one another. A known record reveals where the difference begins. Once the record is present, check the downstream filters and aggregation. A missing report value is not always caused by a stale import.
Avoid cycles between reports
A source can accidentally depend on a destination that already imports from it. Google warns against cyclic chains. Keep the flow of source data clear. Make a short list of which file reads from which other file. If you find a cycle, decide which workbook should own the underlying data and rebuild the reporting direction under the approved workflow. Verify a simple direct import before restoring the dependent report.
Give a deadline check an explicit record
For a monthly handoff, select a recent approved transaction you expect to be included. Open the source, import tab and final summary. Check the record, its status and the reporting period. Verify the current result before preserving an approved snapshot. An apparent refresh time or a filled table is less useful than checking the data that matters.
Use a staged import to separate access from report logic
In the destination, put the source URL in a labelled control cell. Create an Import tab with the external formula in one place. Then create a Report tab with a local reference or query that reads the imported table. Verify each layer before using the next one. The source owner controls the approved material, the import provides a copy to inspect and the report chooses how to present it.
Test with a small reference first
Choose a range with a header and one known record. Check the permission prompt and read the record in the destination. Then expand to the approved range. If the full formula fails, compare the tab name and destination space with the small version. This test narrows the problem without changing permissions again unnecessarily.
Keep a local comparison of expected headers
Write down the approved columns in their intended order. When a new source version arrives, check them before running the report. A copied formula can still display a value even when a column’s role changed. If a query expects the third column to be Status, verify that it still is. Test a record that distinguishes approved from pending status.
Handle a source-owner change deliberately
If the person who connected the files leaves the team or loses source access, check the connection under the organisation’s process. Google’s guidance explains that access persists until the user who granted it is removed from the source. Confirm the current account and review the approved route for continuity. Avoid connecting to a personal replacement file simply because it is easier to reach.
Work through three realistic failures
The URL opens for you but the destination shows an access error
First verify the account that opened the URL and the account editing the destination. A school and personal account can be active in one browser. Confirm that the intended account has access to the source and is authorised to create the destination connection. Follow the prompt when the required role and approved audience are in place. Test a known row after connecting.
The last new row is missing even though the import works
Compare the row number in the source with the end of the range. If the reference stops at row 100, row 101 is outside the request. Extend the range when that matches the report and verify the new row. Also check downstream filters or fixed ranges. It is possible to import the new row and then leave it out of the report.
The imported cells look different from the source
IMPORTRANGE returns the data. Check the destination display and apply the appropriate local formatting. Verify whether a date, currency or identifier is displayed differently. If a code’s leading zeros matter, check the underlying type and approved representation. A format applied in one file is not a substitute for verifying the actual value and meaning in the other.
Check a live handoff before it becomes a routine report
Ask the person maintaining the report to identify the source URL, the approved scope and one expected record. Test a recent edit and verify the report’s filters. Review permissions when the destination gains a new audience. Keep the accepted snapshot and the live file distinct when the process needs both.
Verify one change in the source and one change in the report
In a harmless test, update an amount in the source and confirm it arrives in the import. Then change a local report selector and confirm that the relevant rows change while the imported table stays the same. Those are separate operations. Knowing which layer changed makes later troubleshooting easier.
Record the boundary of the approved report
State which period and fields the recipient should see. If a source or destination is replaced, verify the new link, data and permissions before directing readers to it. Check that the delivered report represents the approved current state. A successful formula is only one part of a reliable handoff.
Decide what to do when the source is unavailable
Start by identifying whether you need the live result or an already approved snapshot. A school report under review might need the latest authorised data, while an accepted submission should preserve the result that was originally sent. If the source cannot be opened, inspect the access message and contact the responsible owner through the approved channel. Keep any verified historical copy clearly identified as historical.
A source was moved or replaced
Check whether the new source is the same spreadsheet or a different file. A replacement file can have a different URL and need a new connection. Compare its approved fields and range before changing the destination. Verify that the new source contains the same kind of records and that its audience is appropriate. Connect only after checking those facts, then compare an expected row and the final report total.
The source owner removed a former collaborator
Review the existing authorisation with the responsible owner. The destination can depend on the access granted through a particular user. If that connection no longer works, have an authorised user follow the organisation’s process for reconnecting the approved files. Review the data shown before asking other users to rely on the restored report. A connection made to the wrong replacement can appear healthy and still read unrelated records.
The report must still show a useful state
A report should make it clear if its data is unavailable. Avoid replacing every import error with an empty result that looks like zero records. Give the maintainer a way to see the original error and give readers a clear explanation when the report is unavailable. If a fallback snapshot is permitted, label its date and scope. Verify that it cannot be mistaken for a current live result.
Verify two reports that intentionally use different sources
Imagine a volunteer schedule and a staff dashboard. The volunteer report reads a separate approved schedule file. The staff dashboard reads a detailed source under its own access policy. Both reports can list the same event while showing different fields. Check the intended audience, source URL and range for each. Use a record whose public and internal details are visibly different so you can verify the separation without exposing the private data.
Check one change in each source
Update an approved schedule field in a harmless copy and confirm the volunteer report reads it. Update a staff-only field in an authorised test and verify it is absent from the volunteer source file. Review the full contents and destination permissions under the approved workflow. A deliberately separated source makes this test meaningful. Hiding a column in the detailed file does not create the same access boundary.
Check what a downstream formula adds or removes
The volunteer report might filter published sessions by venue, while the import itself contains the full approved schedule. Test one session at each venue. If a record disappears, inspect the imported table before changing the external formula. If it is present in the import, the issue is in the local report logic. That separates a data connection problem from a display choice.
Keep a connection checklist for the next maintainer
Record the source file, approved range, destination audience and report purpose in an appropriate place. Include one expected record that can be verified without sensitive data. Check the exact tab or named range and explain where the local report reads it. If a named range expands or a source tab changes, update the documentation and verify the result.
Run the checklist after a term rollover
A term rollover can change the source spreadsheet, record range, editor group and report dates at the same time. Review each item separately. Check the source and destination account, the latest record and any report filters. Preserve a historical snapshot where required. Confirm the new connection through the approved workflow before the report is shared with the next group.
Use two control records
Verify a recent record that must appear and a record outside the report’s scope that must stay out. Then check the reporting period and approval status. A single correct record does not establish that the import is current or that the filter works. The two control records give you a simple way to test both inclusion and exclusion.
Use a fixed copy when the reader needs a record of what was submitted
Verify the live result against the approved source and confirm the report’s filter and period. Create the permitted snapshot under the organisation’s process and open it separately. Check the displayed values, labels and audience. A saved snapshot can document what was submitted while the live destination continues to update. Record which file has each role. If the recipient later asks for a correction, use the verified source, then check the new copy before replacing the earlier one.
Frequently asked questions
Why does IMPORTRANGE show #REF!?
Read the full message. The destination may need permission, the reference may be wrong or the result may be blocked.
Can I use a cell containing the URL?
Yes. Use the cell as the first argument and provide the source tab and range as the second.
Can destination editors access other source ranges?
Google documents that editors of an authorised destination can use the connection to pull from other parts of the source. Use a separately prepared source if the audience needs a limited subset.
Why is a new record missing?
Check whether the range includes the new row, then verify whether the latest source update has arrived.
Can I use it for two tabs in the same file?
A direct tab reference is usually simpler when both tabs are in the same spreadsheet.
Does a small visible query make IMPORTRANGE fetch less data?
A local query can reduce the displayed result, while the external import still requests its specified range. Keep that source range appropriate.
Does IMPORTRANGE preserve every source format?
Verify the displayed dates, currency and identifiers in the destination. Use suitable local formatting and check the actual values.
Helpful reading
- Google Docs Editors Help on IMPORTRANGE, permissions and performance
- How to Share a Google Drive File Without Access Problems
- How to Use QUERY in Google Sheets
- How to Use ARRAYFORMULA in Google Sheets
- How to Use Conditional Formatting Based on Another Cell in Google Sheets
Verify the source, audience and latest result
Check which file is connected, who can see or use the destination and whether the imported records match the approved source.