If you ever need to pull
data from other Google Sheets to your current sheet, you can do this with the
IMPORTRANGE function. You can even append data
from multiple sheets with IMPORTRANGE. Now, the advantage of this function is that it can be embedded inside other functions. It's also a simple function to use. So let me show you how it works. (upbeat music) This is the sheet I
want to import data to. The data I want to grab is sitting in a file called HR Information. It has two tabs. The first one is HR 2010. So it has the people that
have an entry date until 2010. And then HR 2020 has the rest. I want to grab the information from these tabs and
bring it into this sheet. Now, your first step is to
grab the URL of the sheets you want to get the data from. So in my case is HR Information. You can get the URL directly from here. You just don't need that edit part. So just grab everything until edit, press Control + C, go to the sheets you want
to bring in the data to, type in an equals IMPORTRANGE, I can see it right here. So let's press Tab. All it needs is these spreadsheets URL and the range that you want to import. So the spreadsheets you URL
needs to go in quotation marks. Press Control + V to bring it in, close the quotation mark. Next is the range. Now, if you don't mention a sheet name it's going to bring in
data from the first sheet. So let's say I put in A1 until D10. So notice I'm also putting
the range in quotation marks. Now, when I close the
bracket and I press Enter, and if it's the first time
that you're connecting to another sheet, you get this message. You need to allow access to be
able to grab the information. So I'm going to click on Allow access. And now I get this in here automatically. If I want to be sure that I'm getting it from the right sheet and I don't care about
the order of sheets, I have to put the sheet name in there just like we've seen the help here. So if I put in HR 2010 and there was a space
between the HR and 2010, so then I need the
exclamation mark, right? So just like we see here. Now, when I press Enter, I'm sure that it's coming from HR 2010. So let's see if I change this to 2020. It's coming from the data from 2020. Now, if you want to be sure that you're grabbing
everything till the end, what you can do is remove
that end reference. So the rule number of
the end reference here. Just remove that press Enter. And then you make sure that
you include everything. So let's say we add
something to the other sheet. If I go to the bottom of this one and I just add LG here, presenter, go back to my IMPORTRANGE
and just wait a second. We can see it right here. Okay, so it pulls it
through automatically. Now, if you want to make
this dynamic, you can. We could, for example,
add a data validation here and select which tab we want to import. So if I go to Data, Data validation and I want a list of items, I just type in my items is
HR 2010, comma, HR 2020. I'm going to reject input if it's not from my
list, and click on Save I want to be able to select HR 2010 and I get the data automatically here. Well, one thing I can do is
just to reference this part from my dropdown name here. I'm also go make my formula but simpler by taking the
URL that I copied before. So I'm just going to copy this again and put it to the side somewhere. I'll put it right here. This way you can reference it. And that's another
advantage of Google Sheets is that you can connect
to different sheets depending on which cell you reference and which link you have in that cell. So now I can actually
replace this whole part with a cell reference here. And now when I press Enter, everything will continue
to work as before. What I want to do is to update
this, to reference this. So the part that I need
to make a cell reference is only the text here. So I'm going to remove that
and go to the site here typing an and because I'm
connecting a cell reference to text, click on this press Enter. I get 2010 data. If I switched my view
here, I get 2020 data. Okay, so that's how easy
it is to use IMPORTRANGE, but now you might be wondering how can I append these two together? How can I get them all in one list? Well, you can do that
also with IMPORTRANGE. So let me show you. If I go to the site here
and start with IMPORTRANGE, we practically need to
do what we did before. I'm going to select, this
is my URL, my range string. I'll start with the first sheet which was HR 2010! And I'll go with A1 until D50. Now, when I close the quotation
mark, close the bracket, and I press Enter, I get the data, right? Just like we saw before. But I also want to bring in
the other data from 2020. So check this out. I'm going to copy this, go to the end here add a semi-colon. The semi-colon is array syntax for adding the next dataset
below the current dataset. So if I paste this again,
everything else remains the same except this is my other
tab, which is 2020. Now you can't press Enter right now because if you do it's not going to work. Check this out. I get an error. What you need to do because it's an arena, you have to put it in the curly brackets. Starting right after the equal sign, I'm going to open the curly bracket. At the end, I'm going to
close the curly bracket. Now, when I press Enter, it's
going to load both datasets. But look at this. Because I went until 50, it's adding a bunch of empty rows and then it's adding the next one. Now I could go exact right. I could say, well, this
one only has rows until 21. The 2010 only has rows until 12, and only restricted to these rows to get them bright below the other one. But you probably want it stay flexible. This is when you can combine
the IMPORTRANGE function together with the Query function. So if we start here with Query, the Query function is quite a simple and very powerful function to use but you have to know the syntax first. And this is something
that we're going to cover in more detail in the next video. Wait for the next video if you want to know how Query works. For now, I'm just going
to show you the solution. The data that we want to
import is our IMPORTRANGE. The query itself is in
quotation marks is select cause we want to bring everything star. So this means that just
bring all the columns but we want to skip the
empty cells, so where. Now here we have to think
about what our flag is. Well, I can say if you
come across an empty cell in the first column, then exclude that. And to write it for the query function to understand what the first column is, you have to reference
it with Col for column and then the column index number. So that would be column one. You can't use the syntax, A, B, and C. That only works if you're
using the Query function inside your current spreadsheet. But we'll cover more of that next week. Just remember whenever you're
importing external data and you happen to use the Query function, you need to reference it in this way. So where column one is not null. I guess it should include
everything where it is not null. Now, I'm going to close
the quotation mark, close the bracket, and now when I press
Enter, I get the data. I get the first one below the next one. And notice the headers came as well. In this case, it's an easy fix. I can just ignore the headers. So for my range, instead of starting from A1, I'm going to start from A2 and start this one from A2 as well. Now, when I press Enter,
they're going to be right beside one another and
the headers are excluded. So all I have to do is just copy and paste the headers in as values. So Control+ Shift + V and I
have appended the dataset. And it's really easy to add more datasets. You just have to repeat the syntax here. So that's how you can use
the IMPORTRANGE function to grab data from different spreadsheets. That's it for today. I hope you enjoyed this video. If you did do give it a thumbs up. And if you like what you see, consider subscribing, and I'll
see you in the next video. (upbeat music)
