Introduction
Google Sheets is a powerful cloud‑based spreadsheet platform that allows teams to share and manipulate data in real time. One of the most useful capabilities is the ability to pull information from one spreadsheet into another without manual copying. By mastering the IMPORTRANGE and QUERY functions, you can create live links between sheets, filter rows, select specific columns, and even apply complex criteria—all while keeping the source data intact. This article walks you through the step‑by‑step process of importing data from one Google Sheet to another, demonstrates practical examples, and shows how to combine both functions for dynamic, criteria‑driven reports. Whether you are consolidating sales dashboards, syncing inventory lists, or building a master KPI sheet, these techniques will streamline your workflow and reduce errors.
Setting Up the Source Sheet
Before you can import anything, the source spreadsheet must be prepared for external access. Open the sheet that contains the original data and verify that the range you intend to share is organized in a tabular format with clear headers. If you plan to filter later, consider adding a helper column that flags rows meeting your conditions (e.g., Status = “Active”). Next, adjust the sharing settings: click Share → Get link → set the link to “Anyone with the link can view.” This permission level is required for IMPORTRANGE to retrieve data from a different file.
Using IMPORTRANGE to Pull Raw Data
The IMPORTRANGE function has a simple syntax: =IMPORTRANGE(spreadsheet_url, range_string). The spreadsheet_url can be the full URL or just the file ID, and range_string follows the A1 notation (e.g., “Sheet1!A1:D100”). Insert the formula in the destination sheet’s cell A1, accept the permission prompt, and the entire range will appear instantly. This creates a live connection—any changes made in the source sheet automatically refresh in the target. If you only need a subset of columns, you can later wrap the result in a QUERY statement to select the desired fields.
Refining Data with QUERY
The QUERY function lets you treat the imported range as a mini‑database, using a language similar to SQL. Its syntax is =QUERY(data, query_string, [headers]). For example, to retrieve only rows where the “Region” column equals “EMEA” and show columns A, C, and D, you would write:
- =QUERY(IMPORTRANGE(“URL”,”Sheet1!A:D”), “select Col1, Col3, Col4 where Col2 = ‘EMEA'”, 1)
Note that column identifiers are “Col1”, “Col2”, etc., because QUERY does not recognize the original header names when the data comes from IMPORTRANGE. By adjusting the where clause, you can filter by dates, numeric thresholds, or text patterns, giving you granular control over what appears in the destination sheet.
Combining IMPORTRANGE and QUERY for Dynamic Reports
When you need a continuously updating report that reflects only the most relevant records, nest IMPORTRANGE inside QUERY. This approach reduces the amount of data transferred, speeds up calculations, and keeps the destination sheet tidy. For instance, to pull the last 30 days of sales from a master log and order them by revenue descending, use:
- =QUERY(IMPORTRANGE(“URL”,”Sales!A:G”), “select Col1, Col3, Col5 where Col4 >= date ‘”&TEXT(TODAY()-30,”yyyy-mm-dd”)&”‘ order by Col5 desc”, 1)
The TEXT function formats the date dynamically, ensuring the query always reflects the most recent 30‑day window. You can further embed additional functions—such as ARRAYFORMULA for calculations or IFERROR to handle missing permissions—creating robust, automated dashboards that require no manual updates.
Best Practices and Common Pitfalls
To keep your imports reliable, follow these guidelines:
- Limit the range size. Import only the columns and rows you truly need; large ranges can slow down both sheets.
- Use named ranges. Defining a named range in the source sheet simplifies the range_string and makes formulas easier to read.
- Watch for permission errors. If the source sheet’s sharing settings change, IMPORTRANGE will return a #REF! error. Re‑authorize the connection by re‑entering the formula.
- Mind the header row. Set the optional headers argument in QUERY correctly (0 for no header, 1 if the first row contains labels) to avoid off‑by‑one issues.
- Test incrementally. Build the IMPORTRANGE first, verify the data appears, then add the QUERY layer. This helps isolate errors quickly.
Conclusion
Importing data between Google Sheets using IMPORTRANGE and QUERY transforms static spreadsheets into dynamic, interconnected workspaces. By first preparing a clean source sheet, then establishing a live link with IMPORTRANGE, and finally refining the view with QUERY, you gain precise control over which rows and columns appear in your target file. The combined formula not only filters data based on any criteria you define but also updates automatically as the source evolves, eliminating manual copy‑pasting and reducing errors. Adhering to best practices—such as limiting range size, using named ranges, and handling permissions—ensures performance stays optimal. Armed with these techniques, you can build powerful dashboards, consolidate reports, and streamline collaboration across your organization.
