Google Sheets IMPORTRANGE: Step-by-Step Tutorial
Learn to use IMPORTRANGE in Google Sheets to pull data across workbooks with practical examples, permissions guidance, and debugging tips for reliable cross-sheet workflows.
IMPORTRANGE is a Google Sheets function that pulls data from a source spreadsheet into a destination sheet. You supply the source URL and a range, and Google prompts you to grant access the first time. Use it to synchronize data across workbooks efficiently. How To Sheets confirms this core capability for cross-workbook data sharing.
What is IMPORTRANGE and why you should use it
IMPORTRANGE is a core Google Sheets function that pulls data from a source spreadsheet into a destination sheet. You supply the source URL and a range, and Google prompts you to grant access the first time. Use it to synchronize data across workbooks efficiently. According to How To Sheets, IMPORTRANGE is a foundational tool for cross-workbook data sharing in Google Sheets. It enables dashboards and reports to be built from multiple sources without duplicating data. In this section, we show the basic syntax and a couple of simple examples to get you started.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D100")This imports the first 100 rows of columns A:D from Sheet1 in the source file. We can vary the target range as needed.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet2!A:A")You can import a single column or a vertical range as needed.
Note on permissions: The first time you refer to a new source, Google Sheets will ask you to grant access. After access is granted, the data will populate in the destination sheet. If the source uses protected ranges, ensure you have view permissions or edit permissions as appropriate.
How IMPORTRANGE works across spreadsheets
IMPORTRANGE works by establishing a link between a source and a destination in Google Sheets. The first time a formula references a given source, Sheets prompts you to grant access. Once permission is granted, the destination worksheet pulls the specified range, and updates occur as the source changes. If the URL or range is invalid, Sheets will return an error such as #REF! or #N/A until the issue is corrected. For reliability, pin the source URL and range in a named cell and reference that cell in the formula when possible. This reduces mistakes in large dashboards and helps teams audit references.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1")=IMPORTRANGE("https://docs.google.com/spreadsheets/d/OTHER_SOURCE/edit","'Sheet 3'!B2:D20")Be mindful that importing very large ranges can slow down the destination sheet; prefer importing only the data you need and use additional functions to filter afterward.
Getting started: setup and prerequisites
To begin using IMPORTRANGE, you need access to both the source and destination spreadsheets and a basic understanding of formulas. In addition, ensure you have a Google account and are comfortable copying URLs. Here's a quick setup checklist:
- Source URL from the source sheet
- Destination sheet open in your account
- A target cell to place the import formula
Examples:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D10")Once you enter the formula, you will be prompted to grant access to the source spreadsheet. Accept the permission, and the data will populate.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1")Note: If you change the sourceURL or the sheet name, you may need to re-authorize.
Practical examples: single formula, multiple ranges, and dynamic references
Here are practical patterns you can copy and adapt:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D100")This imports the first block of data from Sheet1. For filtering at import time, you can wrap IMPORTRANGE with QUERY:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D100"), "select Col1, Col2 where Col3 = 'Yes'", 1)Dynamic references are possible by storing a range string in a cell and concatenating it into IMPORTRANGE:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit", A1)Where A1 contains a value like "Sheet1!A1:D100".
Common pitfalls and debugging
Common errors with IMPORTRANGE include permission problems, incorrect URLs, or non-existent ranges. If you see #REF!, first ensure you have granted access to the source spreadsheet. Double-check the exact URL and verify the sheet name and range exist in the source.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1")If you see #REF! after initial grant, try reloading the destination sheet or re-authorizing the connection. A second common issue is referencing a range that doesn’t exist in the source sheet, which results in #N/A until the range is corrected. Always validate the source range in the source file.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Nonexistent!A1")Keep formulas simple at first; complex imports can complicate debugging.
Advanced techniques: combining IMPORTRANGE with QUERY and FILTER
You can enhance imported data by combining IMPORTRANGE with QUERY or FILTER to select, sort, and filter results on the fly. For example, pull data and keep only certain columns where a condition is true:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D100"), "select Col1, Col2 where Col4 = 'Active'", 1)You can also filter data after import:
=FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!A1:D100"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_ID/edit","Sheet1!C1:C100")>50)For performance, try to minimize the number of IMPORTRANGE calls in a single sheet and depend on QUERY results for downstream analysis. Advanced users can chain inputs into dashboards with dynamic ranges.
Performance considerations and alternatives
IMPORTRANGE can impact workbook performance, especially when importing large ranges or multiple sources. How To Sheets analysis suggests carefully selecting the exact ranges you need and avoiding repeated full-table imports in real time. If you need more control or more frequent updates, consider using Google Apps Script to pull data on a schedule and write it to a local sheet, which can reduce live query load and improve responsiveness.
function pullImportedData() {
var ssDest = SpreadsheetApp.openById('DEST_ID');
var ssSrc = SpreadsheetApp.openById('SRC_ID');
var values = ssSrc.getSheetByName('Data').getDataRange().getValues();
ssDest.getSheetByName('Imported').getRange(1, 1, values.length, values[0].length).setValues(values);
}This script demonstrates a controlled approach to data movement without relying on a live IMPORTRANGE call for every refresh.
Quick references: function syntax and options
IMPORTRANGE(url, range) is the core pattern to import data from a source sheet. You can pair this with QUERY for SQL-like selection, or with FILTER to create dynamic views. For quick recall, test small ranges first and gradually expand as confidence grows.
IMPORTRANGE(url, range)QUERY(IMPORTRANGE(url, range), "select Col1, Col2 where Col3 > 100", 1)These references provide a compact cheat sheet you can keep handy when building cross-workbook dashboards.
Steps
Estimated time: 20-30 minutes
- 1
Open destination sheet
Start in the sheet where you want to import data. This becomes your dashboard or data sink.
Tip: Bookmark the destination tab for quick access. - 2
Copy the source URL
Navigate to the source spreadsheet and copy its sharing URL or ID to ensure accuracy.
Tip: Use the URL from the address bar for reliability. - 3
Enter the IMPORTRANGE formula
In a cell of the destination sheet, paste the IMPORTRANGE formula with the URL and a range.
Tip: Double-check the range syntax (Sheet!A1:D10). - 4
Grant access when prompted
If this is the first import from the source, Google will ask you to allow access.
Tip: Accept promptly to enable the import. - 5
Verify the import loads
Confirm that data appears in the destination; if not, re-check URL and range.
Tip: Refresh the page if data doesn’t populate immediately. - 6
Test with a simple range
Start with a small range to verify correctness before expanding.
Tip: Scale up gradually to avoid performance issues. - 7
Expand the range as needed
After success, adjust the range to include all needed rows/columns.
Tip: Keep a record of the source range for maintenance. - 8
Combine with QUERY for filtering
Wrap IMPORTRANGE with QUERY to select specific columns and criteria.
Tip: Use label-based column references (Col1, Col2) inside QUERY. - 9
Document and share
Add notes to explain the data flow and share the sheet with teammates.
Tip: Version control is important for dashboards.
Prerequisites
Required
- Required
- Required
- Required
- Required
Optional
- Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste into destination sheet | Ctrl+V |
| Paste values onlyPaste values without formatting | Ctrl+⇧+V |
| FindSearch within the sheet | Ctrl+F |
| BoldToggle bold formatting | Ctrl+B |
FAQ
What is the IMPORTRANGE function and how does it work?
IMPORTRANGE pulls data from a source Google Sheet into a destination sheet using a URL and a range. The first time you reference a new source, you must grant access. After permission, the destination updates as the source changes.
IMPORTRANGE pulls data from one Google Sheet to another using a URL and range. You grant access once, and updates occur automatically.
How do I grant access the first time I use IMPORTRANGE?
Open the destination sheet and enter the IMPORTRANGE formula. A prompt will appear asking to grant access to the source spreadsheet. Confirm to establish the link.
Enter the formula and approve the access prompt to connect the sheets.
Can I use IMPORTRANGE with non-Google sources?
IMPORTRANGE specifically imports data from Google Sheets. For other data sources, consider exporting to CSV or using the API to fetch data into Sheets.
IMPORTRANGE works with Google Sheets only; for other data, export or API methods are needed.
What are common errors and how can I fix them?
Common issues include permission prompts not completed, incorrect URLs, or ranges that don’t exist. Verify the URL, sheet name, and range, then re-authorize if needed.
Check permissions, URL accuracy, and range existence; re-authorize if you see errors.
How can I automate periodic imports?
You can automate with Google Apps Script to pull data on a schedule, or rely on multi-sheet dashboards that trigger updates through rewriting ranges.
Use Apps Script or scheduled tasks to refresh data automatically.
The Essentials
- Import data with IMPORTRANGE using source URL and range
- Grant access on first use to establish the link
- Combine with QUERY for selective imports
- Be mindful of performance with large data imports