IMPORTRANGE is a function in Google Sheets that allows users to import a range of cells from one spreadsheet to another.
This function is helpful for businesses that work with multiple spreadsheets and need to consolidate or reference data across sheets.
Syntax
The function’s syntax is straightforward.
=IMPORTRANGE(spreadsheet_url, range_string)
- The URL of the spreadsheet from which to import data.
- The range of cells to be imported is written in standard A1 notation.
Business Use Cases
After covering several business use cases, we’ll dive deeper into a CRM data migration we did recently.
Consolidating Data from Multiple Sheets
Businesses often have data spread across multiple spreadsheets. IMPORTRANGE can consolidate this data into a single master sheet for analysis or reporting.
Live Reports and Dashboards
Live reports or dashboards that need to pull in data from various sources can be updated automatically using IMPORTRANGE, ensuring that the most current data is always displayed.
Sharing Specific Data
When you need to share a specific range of data from a private spreadsheet without giving access to the entire document, IMPORTRANGE can import only the selected rows or columns into a new, shared document.
Automating Data Transfer Between Sheets
For repetitive tasks involving data transfer between sheets, IMPORTRANGE can automate the process, saving time and reducing the risk of manual errors.
Centralizing Information for Easier Access
Instead of switching back and forth between multiple spreadsheet files, IMPORTRANGE allows users to centralize information in one spreadsheet, making it easier to access and manage.
Data Analysis and Financial Modeling
For complex tasks like data analysis and financial modeling, IMPORTRANGE can import relevant data sets into a single sheet where advanced calculations and models can be applied.
Cross-Departmental Data Sharing
Different departments can use IMPORTRANGE to share data while maintaining control over their spreadsheets. This facilitates cross-departmental collaboration without compromising data integrity.
The Data Migration Use Case
Many organizations import data from one CRM system to another or from one financial system to another.
We were tasked with exporting data from several standard and custom Salesforce objects and importing the data into ConnectWise PSA.
We had to do several sets of imports
| Salesforce | ConnectWise PSA |
|---|---|
| Accounts | Companies |
| Contacts | Contacts |
| CPE (Custom Object) | Configurations |
| Credentials (Custom Object) | Configurations |
Migration projects like this often involve multiple test imports — using vendor-provided import templates — before doing the final imports into a new system.
However, the source data changes between the time the test and final imports happen.
To avoid reworking the import templates every time, the IMPORTRANGE formula can update the templates with the latest data values in the correct columns. Additional functions can be incorporated to perform on-the-fly data transformations.
In this example, the Description field in the Salesforce extract was in Column P. We used IMPORTRANGE to map the values to Column AV in the ConnectWise import template.
How to Use IMPORTRANGE
Step 1: Identify Source Spreadsheet and Range
Locate the spreadsheet you want to import data from and determine the range of cells.
Step 2: Implement the Function
In your destination spreadsheet, type =IMPORTRANGE(“spreadsheet_url”, “range_string”). Replace the placeholders with your specific URL and range.
Step 3: Grant Permission
Upon first use, Google Sheets will prompt you to allow access to the source spreadsheet. Click “Allow Access”.
Step 4: Refine Your Data Management
Use IMPORTRANGE with functions like QUERY, FILTER, or VLOOKUP to manipulate the imported data further.
Best Practices and Tips
- Secure Data: Only share spreadsheets with trusted users since IMPORTRANGE can expose your data.
- Performance: Avoid importing large ranges, which may slow your spreadsheet.
- Error Handling: Familiarize yourself with common errors like #REF! and learn how to troubleshoot them.
IMPORTRANGE is a powerful tool for business users in Google Sheets, enabling efficient data management across multiple spreadsheets.
By understanding and using this function effectively, you can save time, reduce errors, and make more informed business decisions.