Of all the G Suite web apps, Google Sheets may be the most impressive. Released in 2006, it quickly developed into a serious competitor to Microsoft Excel as an up-and-coming spreadsheet editor.
Google Sheets today includes many editing and collaboration features that millions of students, employees, and hobbyists use every day.
One of the most valuable features of Google Sheets is its robust feature set. Features are a key component that makes spreadsheets so powerful, and each spreadsheet editing platform usually has several unique features of its own.
The IMPORTRANGE feature in Google Sheets provides a seamless connection between two or more tables, opening up many interesting possibilities. It allows you to import data into Google Sheets.
What is IMPORTRANGE ?
IMPORTRANGE is one of the many features supported by Google Sheets. Functions are used to create formulas that can manipulate the data in your spreadsheets, perform calculations, and more.
Some of the more than 100 supported functions include DATE to convert a string to a date, COS to return the cosine of an angle specified in brightness, and ROUND to round decimals to a specific decimal place.
– /
This long list of features includes IMPORTANGE. IMPORTRANGE enables integration between tables by allowing you to import a range of cells from another spreadsheet (or sheet on the same sheet).
This allows Google Sheets users to split their data into multiple different sheets while still being able to view it with a simple formula. Uniquely, it also provides a level of collaboration where you can import data from a third-party sheet (if allowed) into your own.
How to use IMPORTRANGE
The first step to using this powerful feature is to create a spreadsheet from which you want to import data into Google Sheets. Either find it or, as I will do in this example, create a dummy sheet with multiple rows of data.
Here we have a simple sheet of two columns and three rows. Our goal is to take this data and import it into another spreadsheet that we are using. Create a new sheet or go to an existing sheet and let’s customize it.
You will start with the same process as for any function – click an empty cell to access the function bar. Enter = IMPORTANT in it. This is a function keyword that we can use to import table data.
The IMPORTRANGE function uses two parameters in its basic syntax: IMPORTRANGE (spreadsheet_url, range_string). Let’s go over both.
spreadsheet_url is exactly what it looks like – the URL of the spreadsheet from which you are trying to import the data range. You just copy and paste the table URL and here. Even easier, if you like, you can simply use the table id string which is also in the url.
This identifier is a long string of text that sits between “spreadsheets / d /” and “/ edit” in the table URL. In this example, this is “1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”.
The range_string parameter is just as simple. Instead of printing all of the spreadsheet data from another sheet, you can return a specific range. To import the data shown in the entire sheet of our example, the range must be A1: B4.
This can be simplified to A: B if we are fine with importing all future data from those columns. If we wanted to import data without headers, it would be A2: B4.
Let’s put together our complete formula: = IMPORTRANGE (“1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”, “A: B”)
You will notice that trying to use this formula assuming you replaced spreadsheet_url correctly will first show a link error. Then you will need to click a cell to connect the two sheets.
If everything was done correctly, you should see the data imported into the current sheet.
It’s simple, right? It’s worth noting that the formatting will not be preserved during this import, as you can see above, but all the data will be preserved in plain text.
Why use IMPORTRANGE ?
Now that you see how easy it is to use IMPORTRANGE, why would you use it at all? Let’s take a quick look at a few use cases.
The best organization
You may find yourself caught up in a very complex sheet containing moving variables that you want to completely separate from other parts of your data. IMPORTRANGE is perfect for this because it allows you to do just that.
Since IMPORTRANGE easily allows you to import data from another worksheet into the same spreadsheet, you can create a Variables worksheet where you can store anything with moving parts. The combination of IMPORTRANGE, QUERY and CONCATENATE can then be used to combine everything.
Working with a friend
Two heads are better than one, and IMPORTRANGE will even let you connect to sheets that don’t belong to your account if available to you. If you’re working on a collaborative project, the work of two or more people can be dynamically merged into one sheet using IMPORTRANGE.
Hiding confidential data
If you have a personal table with rows or columns that you want to show everyone, IMPORTRANGE is great for that.
One example would be creating a form using Google Forms. In it, you can ask for some of the respondents’ personal information – you obviously don’t want to disclose it, right? However, the form can also ask less personal questions that you want to display in a public shared link. This can be achieved by setting range_string appropriately and connecting the form’s answer sheet.
IMPORTRANGE is a powerful Google Sheets tool that works well for many different situations when you need to import data into Google Sheets. This is one of the most important differences between Google Sheets and Microsoft Excel
Do you have any questions about how you can use this feature, or would you like to share the same with us? Leave a comment below and we’ll check it out!
–