Google Sheets IMPORTRANGE Function
IMPORTRANGE returns a live array from a range identified by a spreadsheet URL and a quoted range string. When the source changes, Google Sheets refreshes the imported result without copying and pasting values again.
Reviewed by the SheetsX team
The range string includes the source tab name and cell range inside quotes.
One formula returns four headers and five fictional product rows on Sheet7.
SheetsX confirms the imported array and formatting while leaving Sheet6 unchanged.
How do you use IMPORTRANGE in Google Sheets?
Use =IMPORTRANGE(spreadsheet_url, range_string). For example, =IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Sheet6!A1:D6") imports A1:D6 from Sheet6. The first connection between two separate spreadsheets may show #REF!; open that cell and choose Allow access. Keep the destination spill range empty so the returned array can expand.
Watch SheetsX build and verify an IMPORTRANGE result
The Agent clears all cells that would block the array, writes the formula in Sheet7!A1, verifies every imported header and row, applies readable formatting, and re-reads the source to confirm it was not changed.
The recording uses fictional product data. The source and destination are separate tabs in one demonstration spreadsheet, so Google Sheets did not require an access prompt; cross-spreadsheet imports normally require one-time permission.
“Replace the incorrect hardcoded import output in A1:D6 on Sheet7 with =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WABc9EY0TRLg5wlrLAzvHfFdx742_9hJd7rZILdN1BU/edit","Sheet6!A1:D6"). Do not change Sheet6!A1:D6. Clear every old value in Sheet7!A1:D6 that blocks the imported array. If Google Sheets asks to allow access, allow it. Verify the headers and five rows exactly match Sheet6. Remove the red fill from Sheet7!A1:D6, format Sheet7!A1:D1 with dark green fill and bold white text, use pale green fill for Sheet7!A2:D6, reply in English, and confirm no other cells changed.”
How to use IMPORTRANGE in Google Sheets
Identify the source spreadsheet and exact tab range, enter the formula in an empty destination, authorize the connection when required, and verify the expanded result.
- 1
Copy the source spreadsheet URL
Open the spreadsheet that contains the source data and copy its URL. IMPORTRANGE accepts the complete URL, the spreadsheet key, or a cell reference that contains either value.
- 2
Build the quoted range string
Combine the source tab and cells as "Sheet6!A1:D6". Put single quotes around tab names that contain spaces, for example "'Quarter 1'!A:D".
- 3
Enter the formula in an empty destination
Place =IMPORTRANGE(url, range_string) in the top-left destination cell. Clear nearby values first because a multi-cell result cannot overwrite occupied cells.
- 4
Allow access and verify the array
For the first connection between separate spreadsheets, select the #REF! cell and choose Allow access. Compare headers, row count, and values with the source after the import resolves.
Google Sheets IMPORTRANGE formula examples
Use a fixed URL, a spreadsheet key, a URL stored in a cell, or a named range. Every example returns an array, so the destination must have enough empty cells.
| Use case | Formula or setting | What it does |
|---|---|---|
| Import a fixed range | =IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Sheet1!A1:D100") | Imports rows 1–100 and columns A–D from Sheet1 in the referenced spreadsheet. |
| Reference a URL stored in a cell | =IMPORTRANGE(B1,"Orders!A:F") | Uses the source URL or spreadsheet key in B1 and imports all populated order columns A–F. |
| Import from a tab with spaces | =IMPORTRANGE(B1,"'Quarter 1'!A2:G") | Single quotes inside the range string protect a source tab name that contains a space. |
| Import a named range | =IMPORTRANGE(B1,"SalesData") | Returns a named range when the source spreadsheet defines SalesData. |
| Import one source column | =IMPORTRANGE(B1,"Customers!B2:B") | Imports column B from row 2 downward, excluding the header in row 1. |
Ask SheetsX to write and verify IMPORTRANGE
A precise Agent prompt names the source and destination tabs, supplies the exact formula, handles blocked spill cells, defines formatting, and protects the source range.
Install SheetsX- Open a new English Agent chat so the task starts with clean context.
- Name the exact source and destination ranges and paste the intended formula.
- Tell the Agent to clear only destination values that block the imported array.
- Require a row-by-row comparison and confirmation that the source stayed unchanged.
- Review the formula bar, imported values, formatting, and final scope-of-changes report.
Google Sheets IMPORTRANGE questions
What is the syntax for IMPORTRANGE in Google Sheets?+
The syntax is IMPORTRANGE(spreadsheet_url, range_string). The first argument identifies the spreadsheet; the second is quoted text such as "Sheet1!A1:D20" or a named range.
Why does IMPORTRANGE show #REF! and ask for access?+
A new destination spreadsheet must be authorized to read a source spreadsheet. Select the #REF! cell and choose Allow access while signed in to an account that can open the source.
Why does IMPORTRANGE say the array result was not expanded?+
One or more destination cells already contain data. Clear only the cells that the imported array needs, then let the formula recalculate. Do not clear or overwrite the source range.
Can IMPORTRANGE import another tab in the same spreadsheet?+
Yes, a URL that points to the current spreadsheet can resolve, as shown in the recording. For ordinary same-spreadsheet references, however, =Sheet6!A1:D6 or an array reference is simpler and avoids an external-data function.
Does IMPORTRANGE update automatically?+
Yes. Google Sheets refreshes the destination after the source changes, but updates can be delayed. Large ranges, long chains of imports, and many receiving sheets increase recalculation time.
How can I make IMPORTRANGE faster?+
Import only the rows and columns you need, summarize data before importing it, avoid chains in which one imported sheet feeds another, and reduce the number of destination spreadsheets that read the same source.
Does IMPORTRANGE copy formatting?+
No. IMPORTRANGE returns values, not the source cell formatting. Apply number formats, colors, borders, and text styles in the destination range after the array resolves.