Table of Contents
- INTRODUCTION
- PURPOSE OF IMPORTRANGE FUNCTION IN GOOGLE SHEETS
- PREREQUISITES TO LEARN IMPORTRANGE FUNCTION
- SYNTAX: IMPORTRANGE FUNCTION IN GOOGLE SHEETS
- EXAMPLES:
INTRODUCTION
GOOGLE SHEETS is the spreadsheet application offered by Google which provides many great functionalities to the users.
One of the functionalities is the presence of FUNCTIONS which return the result after calculation.
Whenever we try to create smart sheets, we always try to automate it up to the maximum extent possible.
To automate anything, we need to cover as many as possibilities to make the program error free.
One of the situations can arise where we want to get the data from the different sheets into one MASTER SHEET or the consolidated one.
But, how would we be able to do it. In EXCEL, we can do so by putting the absolute addresses, but here we have all our sheets online.
So, we have got one simple but very powerful function named IMPORTRANGE.
IMPORTRANGE formula gives us the power to bring a complete set of data into the current sheet and apply operations on this sheet.
IMPORTRANGE FUNCTION SIMPLY FETCHES THE DATA OVER THE DIFFERENT SPREADSHEETS IN GOOGLE SHEETS. IT CREATES A DYNAMIC CONNECTION AND WOULD UPDATE WHEN THE SOURCE UPDATES.
In this article, we’d learn about the IMPORTRANGE FUNCTION, its purpose, syntax formula and examples.
PURPOSE OF IMPORTRANGE FUNCTION IN GOOGLE SHEETS
IMPORTRANGE FUNCTION imports a range of cells from a specific sheet.
It’ll bring the data from another sheet to the current sheet, i.e. the sheet where we are applying the IMPORTRANGE FUNCTION.
There can be many situations where we’ll need the use of IMPORTRANGE FUNCTION such as
- We need to bring data from many Sheets to one main sheet.
- Consolidate data into one final table in other sheet than the current one.
- Use QUERY FUNCTION to execute Queries from other sheet.
- Bring the data from other sheet for analysis.
- Share the data just for the information and not for editing
and many more.
PREREQUISITES TO LEARN IMPORTRANGE FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- If you have knowledge about MODE in STATISTICS, it’ll be easier to get this function.
- Of course, access to GOOGLE SHEETS.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel? [ Although this article is for EXCEL but it’ll help you understand the basic working of any spreadsheet application including GOOGLE SHEETS]
SYNTAX: IMPORTRANGE FUNCTION IN GOOGLE SHEETS
The Syntax for the IMPORTRANGE FUNCTION is
=IMPORTRANGE(“COMPLETE ADDRESS OF THE SPREADSHEET OR THE UNIQUE KEY”,” SHEETNAME! RANGE”)
COMPLETE ADDRESS of the sheet will be something like https://docs.google.com/spreadsheets/d/uniquekey
SHEETNAME!RANGE is the Name of the sheet ! Range of the complete data.
The formula will be something like
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/uniquekey or simply uniquekey”,”Sheet1!A1:Z100″)
It’ll fetch the range A1 to Z100 from Sheet1 from the sheet with the address as given above.
EXAMPLES:
EXAMPLE DATA
Let us create some random data in SPREADSHEET 1 and fetch the data from sheet named IMPORTRANGE FUNCTION.
The DATA is shown below
EXAMPLE 1: GET THE DATA FROM SPREADSHEET 1 TO CURRENT SHEET [ IMPORTRANGE FUNCTION SHEET]
We’ll make use of IMPORTRANGE FUNCTION to fetch the data.
Follow the steps to get the complete data from other sheet
- Select the cell in the sheet where you want to bring the data in from the other sheet.
- For our example, we’ll click D5 cell to make it active on the IMPORTRANGE FUNCTION SHEET.
- Enter the function as =IMPORTRANGE(“1R8rXAmZewpYq7AcLXIiogbhy6iSLS8ISTT0-n07ZKaU”,”Sheet1!B4:F18″)
- Press ENTER.
- #REF error will appear when you try to apply the IMPORTRANGE FUNCTION first time.
- This error is just because of the permissions.
- Hover the mouse over cell and a popup will appear.
- Click ALLOW ACCESS.
- The data will be fetched as shown in the picture below.
The complete data has been imported to the new sheet.
