What it does – It imports data from one spreadsheet to another
Syntax=IMPORTRANGE("spreadsheet_URL", "string_range")spreadsheet_URL – This represents the link for the google sheets containing the data that needs to be imported. The URL must be enclosed within inverted commas (“”).string_range – This represents the range of cells that contain the data which needs to be placed in the new sheet. This should also be enclosed within double inverted commas (“”). It is optional to include the sheet name before the range.
Sample Usage=IMPORTRANGE("www.examplelink.com", "sheet1:A6:A9")
//retrieves data in cells A6:A9 from sheet 1 of spreadsheet with URL “examplelink.com”
Avoiding error
Make sure to write the sheet name in the second argument to avoid ‘Internal Import Error’.
You can make a copy of the original and the new spreadsheet and follow along with the tutorial.
The IMPORTRANGE function in Google Sheets allows users to synchronize two Google Sheets. Data can be easily transferred from one sheet to the other, and any modification made on the old sheet is reflected in the new sheet that uses the IMPORTRANGE function.
The function is used when you merge separate sheets which contain data that is updated frequently. For example, a manager might need a consolidated dynamic sheet to keep themselves up to date with the performance of their subordinates or a junior might need a sheet to receive his daily tasks from his senior at work.
Let’s take an example to understand how to use the IMPORTRANGE function in Google Sheets. Suppose, a manager updates the daily tasks for his juniors in a Google Sheet. Let’s create a sheet for his junior that updates itself as the manager updates the original sheet.
We will refer to the manager’s sheet as the original sheet and the subordinate’s sheet as the new sheet henceforth.
Steps for using the IMPORTRANGE function in Google Sheet
Let’s assume initially the original and the new spreadsheet look as follows:
To import data from the original sheet to the new one while ensuring that the two sheets are in sync we follow the steps below:
Step 1: Identify the URL of the sheet and the cell range which contains the data to be imported from the original sheet.
We find that the range of cells which needs to be inputted is A6:A9 in our example.
Step 2: Select the complete URL and press CTRL+C to copy for Windows and CMD+C for Mac.
Step 3: Navigate to the cell in the new sheet where you want to place the data and in that cell enter the following formula:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1qNAOK1xky-Xv33CFt0imI7kwyayVhgNHCWeqBg2qUfc/edit#gid=0”, ”A6:A9”)Step 4: Press Enter.
Depending on the size of data and your net connection, the cell containing the formula will show “Loading…” for a while until it automatically displays the data as shown below:
Note: When using the IMPORTRANGE function in Google Sheets for the first time, you will see the #REF error in the cell which contains the formula. When that happens, Click the cell→ and select Allow access. This access will remain in place until the user who granted access is removed as the source.
We have successfully synced the two sheets and imported the data. Any changes made to the cell range containing the data in the original sheet will have an impact on the new sheet as well.
For example, if the client meeting is cancelled and removed from the original sheet, the new sheet will then be a blank cell.
It is important to note that the IMPORTRANGE function in Google Sheets only copies and pastes the data from the source sheet and not the formatting of the cells. Hence, when we use the IMPORTRANGE function, the colour of the cell in the original sheet and the new sheet is different.
Alternate ways
Instead of adding the complete URL, we could have just added the workbook key and also included the name of the sheet in the string_range argument.
We can find the workbook key within the URL as shown below:
The name of the sheet can be found in the bottom of the workbook. In case, we don’t specify the name of the sheet, the IMPORTRANGE function in Google Sheets will import data from the first sheet of the workbook.
Thus, our new formula is:
=IMPORTRANGE("1qNAOK1xky-Xv33CFt0imI7kwyayVhgNHCWeqBg2qUfc", "Sheet1!A6:A9")When we input the aforementioned formula we find the same result as we did before:
Conclusion
Thus, the IMPORTRANGE formula in Google Sheets is a very useful function to keep track of data that is updated on a frequent basis. It saves time on account of having to make changes to multiple sheets and increases convenience in the day-to-day flow of communication among employees at different levels.
Once the data is received, numerous operations can be performed using the data in the new sheet. You can know about some of these operations through our blog.
See Also
Refer to the articles below to get a clearer picture of the incredible tasks you can complete in Google Sheets using relevant functions.
Import Indian Government Open Data to Google Sheets
How to use the QUERY function with IMPORTRANGE in Google Sheets