Why IMPORTRANGE asks for access again in a shared Google Sheet

Read the full cell error before changing sharing settings. A repeated IMPORTRANGE failure can concern the Google account currently in use, permission to read the source, the connection to this destination file, or a Workspace administrator’s policy. Each calls for a different check.

Keep the source private while diagnosing the problem. Publishing it or switching it to anyone-with-the-link access is not a suitable permissions repair.

Understand what “Allow access” permits

Google distinguishes access to the source spreadsheet from authorization for another spreadsheet to import from it. A new source-to-destination connection requires approval. After approval, any editor of the destination can use IMPORTRANGE to fetch other parts of that source, beyond the range currently shown in the formula. Google says the connection lasts until the person who granted it is removed from the source. IMPORTRANGE permissions documentation.

Before approving a connection, check the destination’s audience. A narrow formula, a hidden source tab or a filtered report is not a range-level sharing boundary. If collaborators should receive only selected data, ask the source owner for a separately approved source file containing only that data. Do not create an extra export merely to work around an access denial.

1. Identify the exact files and account

Make a short diagnostic record, kept somewhere appropriate for the data:

• Source file: the spreadsheet that holds the original values.
• Destination file: the spreadsheet containing the failing formula.
• Source tab and cell range: for example, Data!A1:C4.
• The account email selected in each browser tab.
• The full error detail and when it first appeared.

Compare the file identifiers in the two URLs, not just their titles. A copied report can have the same name as the working original. Treat a different destination ID as a different connection to check; the original’s success does not establish that the copy is authorized. Likewise, confirm that a pasted source URL still names the intended source.

Open the source URL in the same account context used for the destination. If several Google accounts are signed in, use the profile menu to check which is active. Google’s multiple-account guidance explains that accounts have separate settings and that the default account can affect some situations. Switching to an account already authorized for the task is a diagnostic step; it does not give an unauthorized account access.

If the source will not open under the intended account, stop at the source-permission check below. If it opens but the destination is view-only, you will also need an authorized destination editor to make formula changes. Google documents these roles in its file-sharing guide.

2. Follow the branch indicated by the error

The first branch follows Google’s IMPORTRANGE instructions. The second and third are explicitly distinguished in current Workspace administrator guidance: an allowed URL does not replace source permission.

The allowlist feature applies to supported Workspace editions and administrator-configured organizational units or groups. It is not a setting that every personal Google account has. Do not request a blanket policy disablement or route the data through a personal account to evade a restriction.

3. Isolate a small import before changing a large formula

If your failing expression wraps IMPORTRANGE inside QUERY, FILTER or IFERROR, separate the import from the transformation for diagnosis. A single visible import makes it easier to see which stage is failing. It also avoids interpreting an outer function’s error as proof of a permission problem.

Use an empty diagnostic tab inside the affected destination, provided you are authorized to edit it and the destination audience is appropriate. A different test destination would introduce another connection, so success there would not prove this one works.

For a fully synthetic practice exercise, use two private Google Sheets files that you are allowed to create or edit:

1. In a file named Import practice source, create a tab named Data.
2. Enter the following values into Data!A1:C4, or import the synthetic source CSV. Verify the tab name afterward; a CSV does not carry sheet names or permissions.

3. In the destination file, create a tab named Import. Put the source’s actual URL in Import!B1. Do not substitute a guessed file ID or a published-web URL.
4. Keep Import!A4:C7 empty. In Import!A4, enter:

=IMPORTRANGE($B$1,"Data!A1:C4")

5. Wait for the cell’s state to settle, open its error detail if needed, and follow the matching branch above. If a connection prompt appears, approve it only when you have verified both files and the sharing scope. If policy or source permission blocks the import, stop and ask the appropriate owner or administrator.

Google’s importing workflow shows the URL, range and connection steps. The formula here uses a cell reference so the source URL is easy to inspect.

The expected result in A4:C7 is the exact four-row table above, including its heading row. Check both P-301 and P-303, all three Units values, and the total 23. In a separate Checks tab, =SUM(Import!C5:C7) should return 23. A total alone would not detect swapped IDs or teams; compare all twelve cells.

As a small freshness test in these synthetic files, change source Data!C4 from 4 to 9. When the import updates, the final record should be P-303, Blue, 9 and the total 28. No instant-refresh deadline is promised. Google documents import delays and workload considerations in the IMPORTRANGE reference; reference-optimization guidance also explains the cost of cross-file reads. Restore 4 after the test.

Once the import is correct, build transformations against its local result. For example, on another empty tab:

=QUERY(Import!A4:C7,"select A,C where B = 'Blue'",1)

With the restored source, the expected result is a header RecordID, Units, then P-301, 12 and P-303, 4. The QUERY guide explains the transformation; FILTER offers Boolean-condition alternatives. Keep permission diagnosis and transformation checks observable as separate steps.

Why a previously working report can fail again

Check what actually changed before reconnecting anything:

• A report or source was copied. Compare IDs against the working files. As a practical consequence of the per-file connection model, verify the new pair independently instead of assuming a same-named copy has the old connection.
• The person who granted the connection lost source access. Ask the owner to confirm whether that happened. Do not assume a title change, ordinary data edit or ownership change alone proves authorization was revoked.
• A restriction changed. Google notes that source download/print/copy restrictions can prevent new IMPORTRANGE exports while previously established formulas may continue. This can explain why an old report and a new copy behave differently. IMPORTRANGE restriction note.
• A policy or account context changed. Re-read the precise error. Admin allowlists, cross-domain sharing and the active account are separate checks; a different error requires a different repair.

If restrictions are intentional, keep them. Ask the owner for an approved reporting arrangement. Google’s sharing-controls guide describes the controls and their limits; access to a file does not imply authority to redistribute its contents.

What to send the owner or administrator

Use an approved private channel and include only what they need: source and destination links, the account needing access, the complete error detail, whether the source opens under that account, whether the report is a copy, and the time the issue began. Explain the reporting purpose and expected destination audience. Avoid posting private file URLs or screenshots containing real data to public forums.

Do not repeatedly remove and restore production sharing as a test. If the minimal import still fails after the authorized owner or administrator confirms the relevant access, keep the diagnostic result and ask them to investigate. This workflow identifies the failing layer; it cannot guarantee that an organization will permit the connection.

Platform, locale and verification

This guide targets the Google Sheets web app, a rolling service. The account and error-detail controls may appear differently in mobile apps. Workspace restrictions depend on edition and policy. Formula argument separators can vary by locale; retain the URL and quoted range text while adapting separators. See locale and function-language settings.

Verification: First-party Google documentation checked October 16, 2026, including administrator guidance last updated October 6, 2026. The synthetic rows and totals were independently checked. Not tested in Google Sheets. Source sharing, Allow access, copied-file authorization, policy failures and refresh timing have not been exercised in the application. The examples provide reproducible instructions and calculated expectations; no application screenshots or live account test results are presented.