[Narrator] Okay, so you
have a bunch of data in a Google Sheet and
you have a lot of people collaborating on it. They're really happy
about using Google Sheets but you need this data over
in your php application. In this video, we're gonna
show you how to use it. We'll start in the
Google Developer Console by creating a new project. I'm gonna name mine Google Sheets and PHP, but you can name yours
whatever works for you. Click Create and when the
project is done creating, click Enable API and Services. Scroll down to the Google Sheets API and click the Enable button
you'll find here on the screen. Once the API's enabled, click
the Create Credentials button, choose the Google Sheets API and that we'll be accessing
it from a web server and select Application Data. Select No for Compute Engine and click What Credentials Do I Need? Give your service account a name. I'll call mine SheetsPHP and give it a role of project owner. Clicking Continue down below here will give us a JSON file
that holds what we need for our credentials to access the API. Inside of my project directory, I will use composer to
require Google's API client to access the Google Sheets API. Once that's installed, I'll
copy over that JSON file that we got from Google
and store it locally as credentials.json. Next, I'll open everything
up in a code editor. I'm using Visual Studio Code but feel free to use
whatever works for you. Inside of a file called Sheet.PHP, we'll bring in Google's API client that we just got from
composer by pulling in vendor/autoload.php. Next we'll create a Google API client by calling new\Google_Client. Using our client object, we'll
set the application name. I'm gonna call mine Google Sheets and PHP, it really doesn't matter
what name you give this here, it's just for you. And then we'll set the scopes by calling client, set scopes. The scope here is going to be Google_Service_Sheets::Spreadsheets. Then we'll set the access type. The access type for this
application will be offline. Next, we'll give the
client the auth config that we saved into credentials.JSON by calling setAuthConfig and
passing in that file path. Now we can create a new
Google Service Sheets object passing in the client configuration that we just specified above. Now we're ready to move
on to the spreadsheet we're gonna work with. We need access to our spreadsheet ID. We're gonna have to pass
that for each of the calls that we make. But what spreadsheet are
we going to work with? Well, I found this giant
116th Congress Spreadsheet that's available as a Google Sheet and that's what we're gonna use. If you just Google for that,
what you're gonna need to do is create a copy of it
into your own Drive. So go to File, Make a
Copy and then just copy it to your Drive and once that's done, we're gonna grab the ID. The ID is right up here in the URL after spreadsheet/d. Grab the ID and come
back and paste it here into this SpreadsheetID variable. While we're on the topic
of the spreadsheet, we also need to share that spreadsheet with the email address that we'll find inside of credentials.JSON. So go to where it says Client_email, copy this email address, take
it over to your spreadsheet and click Share and then paste it in here where it says Share With
Others and click Send. This will give our application
access to the spreadsheet. Okay, let's log out some data. We're gonna log out the first
name, last name and party so that's columns F, E and D from rows two down through eight. So we need to specify a range and this will look like sheet name, which is congress, that
you can find at the bottom of the spreadsheet and
then an exclamation point and then the range, that's gonna be D2:F8. So we get rows two through
eight from columns D, E and F. So then we're going to use the Service to call spreadsheets_values and call get on that. The parameters that this is gonna take is the Spreadsheet ID and the
range that we just specified. Then we'll get the values out of that by calling response, getValues. Okay now that we have the values, we need to check to make
sure they actually exist. So if the values array is empty, then we know we didn't get anything back from the range that we specified so we'll simply print out No data found. Otherwise, we know that we do have values and each value will represent
a row from the spreadsheet. Each row will have three values so I'll create a mask
that shows 10 characters for the first string, 10
characters for the second and then the remaining
characters for the third string and we're left aligning the middle value. Next, we'll loop through each
one of the values as a row and for each row, we're
simply going to print out the three values in reverse order. So we'll call sprintf, we're gonna pass in the
mask that we specified above and then we'll pass in the third column, the second column and the first column. So that's F, E and D. So first name, last name and then party from the columns in the spreadsheet and we'll print those out to the console. Opening up the terminal,
I can run php sheets.php and lo and behold, there we go. We've got the data that we
expect to get back from Sheets for those columns. Okay, so that's how we can
read data from the spreadsheet. You can modify this range
to get different data but that's the basics. How about updating? So I want to update E2 and F2. So the first name and
last name for row two in the spreadsheet. I'm gonna update it to be
my last name and first name. So we'll specify E2 to F2 as the range and then we need to create a values array. The values array will
be an array of arrays, each array inside of the
values array represents a row of values in the spreadsheet. So the first value in here
will be the last name, the second will be the first name so Brent Schooley and then that's it for our values because we're only gonna update one row. So now we need to create a request. So we'll have a body variable here and we're gonna call it new Google_Service_Sheets_ValueRange because we want to specify the value range that we're going to update. And we'll give this the values
array that we created above. So that's the body for the request. Next we need to specify any parameters we would need for this request. In this case, we're just
going to specify how the values need to be
input into the spreadsheet. In this case, we're just going to specify that we want to use the RAW value that is provided to the spreadsheet. At this point, we have everything
we need to make the call so we'll set a result and we'll call, with the service, we'll
call spreadsheets_values and then we'll call update on that and we'll pass in the
Spreadsheet ID as before and the range as before, and then we'll pass in
the body for the request and the parameters to use for the request. So just to recap, we specify
a range and some values inside of a value range
and then pass that off to the Update method. Okay, give this a save
and open up the terminal and run php sheets.php and once it's done, if we
head over to the spreadsheet, we'll see the first name and
last name for our row two have been updated to my name. Okay excellent, now
what if we wanted to add a completely new row down at the bottom? Well the values will be free form but we could make them
correspond to the columns so what we're gonna do in
here is update the values. I'm just gonna put this is a new row as an array of strings and we'll come up to the
range and we'll just specify that we want to add it to the sheet by making the range just the sheet name. The body and params will
stay the same for this but we have one more thing
that we need to specify at this time and that's how
we want to insert the data. So we'll create another
variable here called insert and inside of this one, we'll specify the insertDataOption and we're gonna set that to INSERT_ROWS because we're going to be inserting rows at the end of the spreadsheet. Instead of calling update,
we want to call append to append rows to the
end of the spreadsheet and down here after the params, parameter, we want to
pass the insert parameter. Give everything a save, run php sheets.php and head back over to the spreadsheet and you'll see we've
added, this is a new row down at the bottom, so
that handles adding rows to the end of a spreadsheet. So we've covered how to
create, read and update data. There's a lot more you
can do with this API so please feel free to
explore it and let us know what you build when you do. That's gonna do it for this video. Thank you so much for watching. If you've enjoyed this video, please consider clicking
the Subscribe button over on the left. If you want to watch another video, I picked one out just
for you in the top right and if you're looking for the code, there's a button in the
bottom right for that. Until next time, I'm out of here. (upbeat music)
Don't.
Just Don't.
Never ever F ever ever build anything on top of anything from google like this.
I have spent so much time with this, and its all wasted the second Google changes or shuts down things with no warning or documentation.
You will spend a ridiculous amount of time coding circles around Googles wonky stuff, and in the end the bit from them will be a tiny little core inside a Frankenstein codebase that's only there to bend over backwards for the Google-part.
In the end it will have been so much simple to just build the whole thing from scratch without the Google-bit.
Don't do it kid, step away from the edge!
----------------------------------------------------------
Addition:
Sure, use it then.
But only it wont cause too much problems for you or your company, if it suddenly stops working the next month/week/day.
Consider it a plugged in toaster, hanging from a string over your bathtub.
You might be fine.
...I mean, its Google, right?
This comment was archived by an automated script. Please see /r/PowerDeleteSuite for more info
Google sheets as database feels so bodgy... I hate it. Databases are usually not even that expensive, right?
They have changed the complete dev interface for that multiple times already. Will be fun!
I did something similar with JavaScript to dump dates from a calendar. The problem I ran into is the maximum limit on API calls or something. It was a long time ago.
Overall my experience with Google API is terrible. For small quick projects sure, but if you're going to plan on running this for a while it's better to build your own project from scratch.
Check out gsxdb