in this video I'm going to go over how to connect Google Sheets and bigquery and we're going to do
it both ways so what I mean by that is we're gonna get bigquery data to Google Sheets which
is something that's called connected sheets and then we will also do an example of getting Google
Sheets data to bigquery so let's get started with connected sheets which means basically we're gonna
take bigquery data and move it to Google Sheets and I have this empty Google Sheets file as you
can see it's called connect Google Sheets to bigquery Connected sheets it's just the name
doesn't really matter what it's called but it's an empty Google Sheets spreadsheet and
then I got my bigquery right here where I have my sales data data set and I got a couple of
tables customers and products I'm not going to go over how to set up bigquery in this video I
will link to a video below this that goes over how to set up bigquery so you understand what's
a project what's a project ID what's a data set what's a table all those things you need
to know to be able to successfully use this and also you need to know how to write some queries
which again it's going to be all in the video I'm going to link in the description which is
basically just bigquery tutorial just to get you up to speed so I got this set up so as you can see
I got this project it's called my project 25045 and this is the actual project ID this organic
Justice this number within that I have this sales data data set and I got two tables so first of all
let's just do a quick query from this customers table so I'll go ahead and add a new query window
and what I'll do here I'll just do a quick select statement so I'll just do select star meaning all
columns from and then I'll take that organic Justice ID and inside of this we have sales
data data set and within this we're going to get that customers table so I'll do some filters on
this but let's just go ahead and add a semicolon and run this so you can see what this looks like
so as you can see I got this data with 10 000 records we got some of this information
so let's go ahead and filter this by state so I'll go ahead and add a filter by state column
and we'll make it equal Illinois so let's go ahead and run this so you can see now I got
480 results which should be just those that match this criteria so that's pretty much it
this can be any query so this is just one example you just want to make sure
you write the query you're actually trying to get so now let's try to get this data in
this Google Sheets spreadsheet so what I'll do here I'll go to data
and I'm going to move to data connectors and here we have this option connect to bigquery
so this will list all the projects you have available under your account so you can see
now this is that ID organic Justice that's the one I want so I'm going to click on that
so now it's going to show me what data sets I have within that project which is sales data
is the data set and here we basically have different tables so you can just pick one of
these tables so this is fine you can actually do this and what it's going to do if you just
select one of these it will just grab the entire table and pull it over but typically if you're
working with bigquery you're probably not going to pull that entire thing to Google
Sheets so what we're going to do instead of picking one of this we're just gonna write a
custom query right here so I'll just click on that and this window is where your query is
going to go and basically I'll just go back and copy this SQL statement I did here and
go back to here and just paste it here just that simple so if I just do preview results
you'll see it shows me those results now here
and those same 480 lines as you can see so we can go ahead and click connect
and that's it we're done we're connected so I'm going to close this
so you can see I got all of this data later if you want to refresh this data with new data
you get in your database you can just click refresh and it's going to pull that data now
using this data you can now do different things so you can take this data and now build a pivot
table on top of that or you can add calculated columns basically calculate something with a
few different columns from this database and things like that so let's just go ahead and
rename this this is called connected sheets so I'll just call it my bigquery connection
it's just a name you can call it anything you like and that's going to be the tab
name down here and at this point I can take this and make a pivot table
and maybe in rows I can add gender and in values I can add customer ID and if I'd apply that will
basically take the data and do the calculation and give us the count per gender you can do this you
can do formulas now from this data so basically we just pull that data from bigquery to this Google
Sheets spreadsheet now what's also interesting here is that you can also add parameters in your
actual SQL statement and what I mean by that currently with this setup we're just gonna
pull Illinois results so what I could do I could go to a different tab I'm going to call this one
settings and then I'm just going to call State and just type IL for Illinois so now what I can do I
can use this cell value this Illinois in my actual SQL statement so to do that I'm going to go back
to this and we need to go back and modify this so I'm going to click on this connection settings
that's going to get back to this and here we have this option for parameters and we can click add
and it's going to ask us for a name so I'm just going to call this selected state
and then here we're going to do a reference to that cell so I'll click on this go to
that settings tab and click on this cell I'm going to press ok and I'm going to click add
and you can see when I did that it did add this add sign with that selected state which is the
name of that parameter in here so what I can do now I can grab that I'm going to cut that out of
here and then instead of having this Illinois as a state I'm going to delete that including the
single codes and paste that parameter name and with this setup again if I do preview results
you should see I get the same thing and if I connect we're basically in the same place
so now you can see we got the same results because it's Illinois but with this setup
now I can go back here and change this to Texas and then go back to this and if I refresh this
see now I get my results for Texas because basically I was able to use
that parameter in the actual SQL statement and that's pretty much
it that's how you can pull bigquery data to your spreadsheet using this connected sheets
so now let's do the exact opposite let's grab some data from Google Sheets and move it to
bigquery and for that I have this tab and it's basically a different spreadsheet and
you can see I have some data the first line in this data is basically column names and
the tab name down here is called customers so what I'm gonna do I'm gonna go to that
bigquery setup and you could add it to any data set so I'm just going to add it to a different
data set not this one so I'll just go ahead and create one more data set here in this project
so I'll just go ahead and name this test data create this data set and now within this data
set there will be no tables I just made it so I'm going to add this Google Sheets data
here as a table within this data set so what I'll do I'm going to click on this add data
and here as a source we're going to find this Google Drive
and what I'm going to do I'm going to go copy the link to that spreadsheet this one
go to here and paste it here and here as a format I'm going to choose Google sheet
and it's going to ask us for a range so here you can either do the tab name and the range
or you can just do the tab name if you want to grab the whole thing
so in this case the tab name is customers I'm going to copy that
and I'll go here and I'll paste it right there so that means just grab the whole tab customers
now if you want a particular range you can do exclamation and do something like A1 colon G20
or something like that I want to grab the whole data so I'll just go customers like this that's
fine and now I'm going to choose that data set I made this test data so I'll click here and click
right there and the name of the table I want to create within that data set so I'm going to call
this customers data like that should be no spaces here it did auto correct to have a space but it
shouldn't so just a single word like that and then I'm going to leave this as external table so I'm
going to scroll down and auto detect the schema again if you don't understand what this means go
to that video in the description and watch and understand how bigquery actually works and what
these things actually are one more thing I want to do here I want to go to this Advanced options
and I want to make sure that I explained that that first line is the headers so I'm
going to skip the header row the first line which will make it the actual column names
so at this point I'm going to do create table
and you'll see I got this customer's data table now within this test data data set so if I click
on that you'll see it lists all these column names which are basically these column names
from this spreadsheet and at this point I can just go ahead and write some SQL statements so
I'm going to get rid of this and let's do select star from again the name of this project ID
and then we have data set test data and then we have the table customers data
which is this so at this point if I just do a semicolon to finish this and hit run
let's just give this a second there it is so you can see how it went to that Google sheet
it grabbed all the data in that spreadsheet and it just pulls it over which is this data from here so
that's how we can now grab the data from Google sheet and bring it over to bigquery so let me go
ahead and quickly sort this alphabetically so I'm going to go order by that first name column here
so I have this person and that's his last name so let's go to our spreadsheet and find this person
there it is so let me change the last name to something else
so now if I go back and rerun this statement
you'll see that it grabs that data because we're basically connected to that spreadsheet with a
live connection and that's the reason why you'll probably notice that this runs a lot slower than
a native bigquery data set usually but there it is now that's our connection both ways so from
bigquery to Google Sheets or from Google Sheets to bigquery both ways one of them connected sheets
and the other one is just basically using that particular spreadsheet as a data source in our
bigquery data set if you have any questions related to this let me know in the comments
section and as always thanks for watching please subscribe and I'll see you in the next video