How to Import Data from Webpages into Google Sheets

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 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 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 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 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