hi there jamie here today at teachers tech hope you're having a great day today
today i want to show you how you can import data from the web into google sheets so what i mean for
this something like a table here so here i am on wikipedia these are netflix films of 2021 they're
in this table you can use a function that imports them directly into google sheets
so you have the entire table not only can you import the table you can also limit the columns
as you import import them by adjusting the formula and adding other functions and then you can also
filter them so you can pull out exactly what you want out of the web so you have the data
that you want to use so i'm going to show you how to do this on teachers tech today
so the data that i'm pulling is from wikipedia like i showed you in the intro and i can put
a link to this where i'm pulling it from down below in the description so you can follow along
exactly i'm going to select this list of netflix original films 2021. so you can see that there's a
list right here of contents and then we have this table down here so i'm going to import this and it
can be done really quickly into google sheets but what i need to take from this is the url so i'm
just going to go ctrl c or you could right click and copy that also i'm going to go over to google
sheets and the function i need to use for this is going to be import html so i'm just going to go
to a1 here and start with my equals and i'm going to type import here it is right here import html
now you can see the syntax that's going to be used is the url query index and local
if you want to see more about the definition of these uh just click this and to open them
up and you can see it will give you a little bit more information i'll explain it as i go through
the first part is going to be the url now it's important to remember you need to use
the quotation marks on this so i'm going to put the quotation marks first here and then i'm just
going to paste in the formula and then i'm going to put another quotation mark and then a comma
the query if we look is either going to be a list or a table so for this example first i'm going to
do a table but then i'll switch it over to a list to show you what happens also so i am going to put
table just like this in quotation marks again and then another comma and this one is index this is
going to be on what uh watch table on the page it is so this is just going to be one like so
and i'm just going to close my bracket to end the function and hit enter and it loads in so
this is exactly what that from that table is pulled over but what i want to show you here
if i just click in a1 you can see the formula here and if i'm just going to control c copy this and
go to this here just to show an example if i paste this in instead of table if i wrote list and i'm
just going to change it like this hit return now look it gave me back this because you can have
tables and lists on the same page if i go back and look at this here was the list that they pulled
from here this was the table this was the list so as i change that in the query you get something
else so i just wanted to point that out for a list and this was the table now i want to show you how
you can customize this whether it be to limit the columns or to filter specific information
from that table i just wanted to point out before i moved on with this with this formula here
notice that there was one part i left out and you don't need it so if i just turn the formula help
on here you can see they had this in their example and that was the locale right here you don't need
to uh put anything in for that so i just left it uh it is going to be kind of default on
what was part of the page already so i just left it i didn't bother using that
all right so let's move on by limiting columns you can see that right now we have one two three four
five columns let's say i finally wanted the first two columns uh to speed up things what i'm going
to do is just copy this formula i'm going to go over to a new tab that i have already set
up called filter data data and i'm going to be an a1 again and i'm just going to paste that formula
on but i do need to change this the function or the syntax on it i need to add another function
and that's going to be the query one so i'm going to go up to the very beginning and just type in
query like so but then i need to open and have a open bracket like this and then i'm going to go
to the end of this and this is where i'm going to say well what do i want to query here so i'm going
to put a comma you do need to use the quotation marks on this and i'm going to write select
like so and what do i want to select well i want to select column one and column two so
i could put column one like so column two like this and then i end my quotation marks and then
my bracket so that i'm closing that query also so i hit enter and now i've only brought in the two
columns so using it that way you can go through and pick the columns from the web page to quickly
bring in that to google sheets so the last thing i want to show you is how you could
bring in a specific part of this table where you can kind of add a filter to it
so i'm going to do a little copy and pasting again to save some time i'm going to just select
everything up here and ctrl c now i'm going to go to my next tab and i am going to just go in a1
again and i'm going to paste it in so i'm going to keep query the same here but what i need to do is
change at this point here uh so what i'm going to change is i'm going to select this is going to be
where i'm going to put an asterisk asterisk first so i'm just going to go ahead and delete this here
so i do need an asterisk like so and at this point i'm going to type the word where
and i need to say that column 2 is going to equal something so i'm going to add right after here i'm
going to say column 2 equals and at this point what does it equal so this where i need to put
an apostrophe and i'm going to write drama and close the apostrophe like this so make sure
you have your quotation marks at the beginning here and then at the end but then the word that
you're searching for make sure that you have the apostrophes around it i'm gonna go ahead hit enter
notice it pulled back this time anything in column two that is drama so it went through that whole
list and now it filtered back this so you can really pull out specific information from these
tables so i hope you like this little tutorial on how to import data from the web into google
sheets i find this even handy if i need to kind of practice data sometimes is just to pull it in
from the web and then i can even show examples to you guys when i'm creating my tutorials so thanks
for watching this week on teachers tech i'll see you next time with more tech tips and tutorials