Can you use Google Sheets as a database? Learn how to build a Next.js app using a spreadsheet as the data layer https://fireship.io/lessons/google-sheets-database-nextjs/

#googlesheets #js #webdev

🔗 Resources

Source Code https://github.com/fireship-io/google-sheets-database
Google APIs PRO Lesson https://fireship.io/lessons/google-apis-node-tutorial/
Next.js Full Course https://fireship.io/courses/react-next-firebase/ 
Google Sheets https://www.google.com/sheets/about/

📚 Chapters

00:00 Google Sheets Database?
00:35 Pros & Cons
01:15 Create a Spreadsheet
01:49 Create Next.js App
02:40 Ways to Authenticate Google APIs
03:34 GCP Service Account
04:36 Next Env Variables
05:07 Google API Auth
06:07 Query the Speadsheet

 🤓 Install the quiz app

iOS https://itunes.apple.com/us/app/fireship/id1462592372?mt=8
Android https://play.google.com/store/apps/details?id=io.fireship.quizapp

🔥 Watch more with Fireship PRO

Upgrade to Fireship PRO at https://fireship.io/pro
Use code lORhwXd2 for 25% off your first payment. 

🎨 My Editor Settings

- Atom One Dark 
- vscode-icons
- Fira Code Font
2024-02-04T14:03:27.168Z
nova-2
2-general-nova
2024-01-09.29447
30089e05-99d1-4376-b32e-c263170674af
92d0ee35-8def-4d2c-a08b-29266d368f75
3840471b77702b726ac3df58819ab7f50555be949eeaf7175f43e4d4174aab0b
67875a7f-c9c4-48a0-aa55-5bdb8a91c34a
deprecated
What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it?
That's impossible.
Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack.
And that's exactly what I'll show you how to do today by integrating Google Sheets with Next.
Js.
In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node.
Js application, which also opens the door to connecting hundreds of other Google APIs to your server.
If you're new here, like and Subscribe and follow along with the full write up on Fireship IO.
But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database.
First of all, it was never designed to be an app database.
It's not going to be ACID compliant and doesn't support joins or complex queries.
Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds.
But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production.
If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits.
But, most importantly, it's super easy to use.
So let's jump right into things.
The first step is to create a Google Sheet and add some data to it.
You'll notice I have 2 columns here, one for the title and one with content in the form of HTML.
Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner.
That'll come into play later when we start making requests through the API.
Now in order to make the sheet available to the app, we need to share it publicly.
When you click the share button, it generates a URL.
And inside that URL, you'll notice an d, which is the ID of the sheet itself.
You'll wanna make a note of that ID for later.
The next step is to generate our Next JS application.
And keep in mind, you can follow the same basic process for any Node.
Js server.
We can run npx create next app from the command line and then open it up in Versus Code.
Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node.
Js.
Run npm install Google APIs, guys.
And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code.
Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet.
That means we need 2 different components in Next, which will live in the pages slash post directory.
The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet.
For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project.
Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be.
There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2.
In this demo, we'll be using default credentials, which is just like using an API key.
The limitation though is that you only have access to resources on your own Google account.
That works fine for us, but many apps Using Google APIs want access to resources owned by other users.
Like, for example, if our app wanted to access files in somebody else's Google Drive.
For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication.
That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it.
Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API.
Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account.
Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services.
Type sheets into the search bar, then click the enable button.
That'll take a second to enable, then click manage.
From there, you'll see a tab for credentials.
Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys.
From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud.
I'm downloading that file To the root of my Next JS project as secrets dot JSON.
But it's very important that you don't expose this file publicly.
If someone gets a hold of it, they'll I have access to your Google Cloud account.
So make sure to add it to your git ignore file before committing this to a public repo.
Now that our credentials are in place, we need to make them available to our Riverside code.
And, the way we can do that is with an environment variable.
The Next.
Js framework will automatically find any environment variables that You define in the dotenv dot local file.
Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON.
It's important that that variable has that exact name.
In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier.
And now we're finally ready to write some JavaScript code.
We'll first go into the post ID dot JS file and import Google from the Google APIs package.
Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props.
If you're not familiar with Next.
Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component.
So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML.
To access Google Sheets on the server, we need to authenticate the API client.
That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope.
That's going to look for That Google applications environment variable that we defined earlier to authenticate us.
We can then use the returned object to access the Google Sheets API, by passing it as the auth param.
And now, we're ready to make our first query to the spreadsheet or database, I mean.
We want to fetch an individual row number, which we'll get from the URL as the ID parameter.
Then to make the request, we need to specify a range of columns in cells.
The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access.
For example, If we want row 23, we can say column a 23 through column c 23.
But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL.
Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method.
That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable.
That'll give us a response that contains the data we want for the UI.
The data is formatted in an array and I'm using destructuring here to give the values a name of title and content.
Now, at this point, we can run the next app by running npm run dev.
And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet.
And that's what I would call some next level sheet.

Speaker 0: What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it? That's impossible. Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack. And that's exactly what I'll show you how to do today by integrating Google Sheets with Next. Js.

In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node. Js application, which also opens the door to connecting hundreds of other Google APIs to your server. If you're new here, like and Subscribe and follow along with the full write up on Fireship IO. But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database. First of all, it was never designed to be an app database.

It's not going to be ACID compliant and doesn't support joins or complex queries. Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds. But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production. If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits. But, most importantly, it's super easy to use.

So let's jump right into things. The first step is to create a Google Sheet and add some data to it. You'll notice I have 2 columns here, one for the title and one with content in the form of HTML. Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner. That'll come into play later when we start making requests through the API.

Now in order to make the sheet available to the app, we need to share it publicly. When you click the share button, it generates a URL. And inside that URL, you'll notice an d, which is the ID of the sheet itself. You'll wanna make a note of that ID for later. The next step is to generate our Next JS application.

And keep in mind, you can follow the same basic process for any Node. Js server. We can run npx create next app from the command line and then open it up in Versus Code. Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node. Js.

Run npm install Google APIs, guys. And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code. Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet. That means we need 2 different components in Next, which will live in the pages slash post directory. The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet.

For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project. Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be. There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2. In this demo, we'll be using default credentials, which is just like using an API key. The limitation though is that you only have access to resources on your own Google account.

That works fine for us, but many apps Using Google APIs want access to resources owned by other users. Like, for example, if our app wanted to access files in somebody else's Google Drive. For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication. That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it. Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API.

Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account. Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services. Type sheets into the search bar, then click the enable button. That'll take a second to enable, then click manage. From there, you'll see a tab for credentials.

Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys. From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud. I'm downloading that file To the root of my Next JS project as secrets dot JSON. But it's very important that you don't expose this file publicly. If someone gets a hold of it, they'll I have access to your Google Cloud account.

So make sure to add it to your git ignore file before committing this to a public repo. Now that our credentials are in place, we need to make them available to our Riverside code. And, the way we can do that is with an environment variable. The Next. Js framework will automatically find any environment variables that You define in the dotenv dot local file.

Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON. It's important that that variable has that exact name. In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier. And now we're finally ready to write some JavaScript code. We'll first go into the post ID dot JS file and import Google from the Google APIs package.

Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props. If you're not familiar with Next. Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component. So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML. To access Google Sheets on the server, we need to authenticate the API client.

That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope. That's going to look for That Google applications environment variable that we defined earlier to authenticate us. We can then use the returned object to access the Google Sheets API, by passing it as the auth param. And now, we're ready to make our first query to the spreadsheet or database, I mean. We want to fetch an individual row number, which we'll get from the URL as the ID parameter.

Then to make the request, we need to specify a range of columns in cells. The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access. For example, If we want row 23, we can say column a 23 through column c 23. But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL. Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method.

That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable. That'll give us a response that contains the data we want for the UI. The data is formatted in an array and I'm using destructuring here to give the values a name of title and content. Now, at this point, we can run the next app by running npm run dev. And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet.

And that's what I would call some next level sheet.
What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it? That's impossible. Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack. And that's exactly what I'll show you how to do today by integrating Google Sheets with Next. Js. In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node. Js application, which also opens the door to connecting hundreds of other Google APIs to your server. If you're new here, like and Subscribe and follow along with the full write up on Fireship IO. But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database. First of all, it was never designed to be an app database. It's not going to be ACID compliant and doesn't support joins or complex queries. Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds. But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production. If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits.
google
databases
data management
internet
microsoft
But, most importantly, it's super easy to use. So let's jump right into things. The first step is to create a Google Sheet and add some data to it. You'll notice I have 2 columns here, one for the title and one with content in the form of HTML. Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner. That'll come into play later when we start making requests through the API. Now in order to make the sheet available to the app, we need to share it publicly. When you click the share button, it generates a URL. And inside that URL, you'll notice an d, which is the ID of the sheet itself. You'll wanna make a note of that ID for later. The next step is to generate our Next JS application. And keep in mind, you can follow the same basic process for any Node. Js server. We can run npx create next app from the command line and then open it up in Versus Code. Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node.
Js. Run npm install Google APIs, guys. And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code. Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet. That means we need 2 different components in Next, which will live in the pages slash post directory. The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet. For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project. Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be. There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2.
blogging
google
intellectual property
In this demo, we'll be using default credentials, which is just like using an API key.
The limitation though is that you only have access to resources on your own Google account. That works fine for us, but many apps Using Google APIs want access to resources owned by other users. Like, for example, if our app wanted to access files in somebody else's Google Drive.
google
For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication.
That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it. Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API.
youtube
video gaming
Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account.
Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services. Type sheets into the search bar, then click the enable button. That'll take a second to enable, then click manage. From there, you'll see a tab for credentials.
google
Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys. From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud. I'm downloading that file To the root of my Next JS project as secrets dot JSON. But it's very important that you don't expose this file publicly. If someone gets a hold of it, they'll I have access to your Google Cloud account. So make sure to add it to your git ignore file before committing this to a public repo. Now that our credentials are in place, we need to make them available to our Riverside code. And, the way we can do that is with an environment variable. The Next. Js framework will automatically find any environment variables that You define in the dotenv dot local file. Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON. It's important that that variable has that exact name. In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier. And now we're finally ready to write some JavaScript code. We'll first go into the post ID dot JS file and import Google from the Google APIs package. Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props. If you're not familiar with Next. Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component.
So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML. To access Google Sheets on the server, we need to authenticate the API client. That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope. That's going to look for That Google applications environment variable that we defined earlier to authenticate us. We can then use the returned object to access the Google Sheets API, by passing it as the auth param. And now, we're ready to make our first query to the spreadsheet or database, I mean. We want to fetch an individual row number, which we'll get from the URL as the ID parameter. Then to make the request, we need to specify a range of columns in cells. The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access. For example, If we want row 23, we can say column a 23 through column c 23. But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL. Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method.
google
databases
programming
That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable.
That'll give us a response that contains the data we want for the UI. The data is formatted in an array and I'm using destructuring here to give the values a name of title and content. Now, at this point, we can run the next app by running npm run dev.
data
data management
And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet. And that's what I would call some next level sheet.
What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it? That's impossible. Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack. And that's exactly what I'll show you how to do today by integrating Google Sheets with Next. Js. In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node. Js application, which also opens the door to connecting hundreds of other Google APIs to your server. If you're new here, like and Subscribe and follow along with the full write up on Fireship IO. But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database. First of all, it was never designed to be an app database. It's not going to be ACID compliant and doesn't support joins or complex queries. Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds. But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production. If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits. But, most importantly, it's super easy to use. So let's jump right into things. The first step is to create a Google Sheet and add some data to it. You'll notice I have 2 columns here, one for the title and one with content in the form of HTML. Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner. That'll come into play later when we start making requests through the API. Now in order to make the sheet available to the app, we need to share it publicly. When you click the share button, it generates a URL. And inside that URL, you'll notice an d, which is the ID of the sheet itself. You'll wanna make a note of that ID for later. The next step is to generate our Next JS application. And keep in mind, you can follow the same basic process for any Node. Js server. We can run npx create next app from the command line and then open it up in Versus Code. Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node. Js. Run npm install Google APIs, guys. And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code. Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet. That means we need 2 different components in Next, which will live in the pages slash post directory. The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet. For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project. Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be. There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2. In this demo, we'll be using default credentials, which is just like using an API key. The limitation though is that you only have access to resources on your own Google account. That works fine for us, but many apps Using Google APIs want access to resources owned by other users. Like, for example, if our app wanted to access files in somebody else's Google Drive. For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication. That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it. Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API. Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account. Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services. Type sheets into the search bar, then click the enable button. That'll take a second to enable, then click manage. From there, you'll see a tab for credentials. Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys. From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud. I'm downloading that file To the root of my Next JS project as secrets dot JSON. But it's very important that you don't expose this file publicly. If someone gets a hold of it, they'll I have access to your Google Cloud account. So make sure to add it to your git ignore file before committing this to a public repo. Now that our credentials are in place, we need to make them available to our Riverside code. And, the way we can do that is with an environment variable. The Next. Js framework will automatically find any environment variables that You define in the dotenv dot local file. Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON. It's important that that variable has that exact name. In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier. And now we're finally ready to write some JavaScript code. We'll first go into the post ID dot JS file and import Google from the Google APIs package. Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props. If you're not familiar with Next. Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component. So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML. To access Google Sheets on the server, we need to authenticate the API client. That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope. That's going to look for That Google applications environment variable that we defined earlier to authenticate us. We can then use the returned object to access the Google Sheets API, by passing it as the auth param. And now, we're ready to make our first query to the spreadsheet or database, I mean. We want to fetch an individual row number, which we'll get from the URL as the ID parameter. Then to make the request, we need to specify a range of columns in cells. The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access. For example, If we want row 23, we can say column a 23 through column c 23. But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL. Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method. That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable. That'll give us a response that contains the data we want for the UI. The data is formatted in an array and I'm using destructuring here to give the values a name of title and content. Now, at this point, we can run the next app by running npm run dev. And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet. And that's what I would call some next level sheet.
What
what
if
if
I
i
told
told
you
you
there
there
is
is
a
a
database
database
that
that
is
is
100%
100%
free,
free
has
has
a
a
world
world
class
class
admin
admin
UI,
ui
and
and
almost
almost
everybody
everybody
in
in
the
the
world
world
knows
knows
how
how
to
to
use
use
it?
it
That's
that's
impossible.
impossible
Believe
believe
it
it
or
or
not,
not
Google
google
Sheets,
sheets
the
the
spreadsheet
spreadsheet
software,
software
can
can
be
be
used
used
as
as
a
a
lightweight
lightweight
database
database
or
or
headless
headless
CMS
cms
as
as
the
the
data
data
layer
layer
in
in
your
your
application
application
stack.
stack
And
and
that's
that's
exactly
exactly
what
what
I'll
i'll
show
show
you
you
how
how
to
to
do
do
today
today
by
by
integrating
integrating
Google
google
Sheets
sheets
with
with
Next.
next
Js.
js
In
in
the
the
process,
process
you'll
you'll
learn
learn
how
how
to
to
easily
easily
fetch
fetch
data
data
from
from
a
a
Google
google
Sheet,
sheet
in
in
any
any
server
server
side
side
Node.
node
Js
js
application,
application
which
which
also
also
opens
opens
the
the
door
door
to
to
connecting
connecting
hundreds
hundreds
of
of
other
other
Google
google
APIs
apis
to
to
your
your
server.
server
If
if
you're
you're
new
new
here,
here
like
like
and
and
Subscribe
subscribe
and
and
follow
follow
along
along
with
with
the
the
full
full
write
write
up
up
on
on
Fireship
fireship
IO.
io
But
but
before
before
you
you
get
get
too
too
excited
excited
about
about
this
this
amazing
amazing
old
old
tech,
tech
there
there
are
are
some
some
important
important
caveats
caveats
when
when
using
using
Google
google
Sheets
sheets
as
as
your
your
primary
primary
database.
database
First
first
of
of
all,
all
it
it
was
was
never
never
designed
designed
to
to
be
be
an
an
app
app
database.
database
It's
it's
not
not
going
going
to
to
be
be
ACID
acid
compliant
compliant
and
and
doesn't
doesn't
support
support
joins
joins
or
or
complex
complex
queries.
queries
Actual
actual
data
data
storage
storage
only
only
scales
scales
to
to
5,000,000
5,000,000
cells
cells
and
and
there's
there's
also
also
an
an
API
api
quota
quota
that
that
limits
limits
you
you
to
to
500
500
requests
requests
per
per
100
100
seconds.
seconds
But
but
even
even
with
with
those
those
limitations,
limitations
Google
google
Sheets
sheets
It
it
actually
actually
makes
makes
a
a
great
great
little
little
database
database
for
for
prototyping,
prototyping
and
and
there's
there's
really
really
nothing
nothing
stopping
stopping
you
you
from
from
using
using
it
it
in
in
production.
production
If
if
you're
you're
building
building
a
a
jamstack
jamstack
site,
site
you
you
could
could
use
use
it
it
as
as
a
a
headless
headless
CMS,
cms
and
and
it's
it's
very
very
unlikely
unlikely
Likely
likely
that
that
you'll
you'll
ever
ever
hit
hit
the
the
API
api
quota
quota
or
or
storage
storage
limits.
limits
But,
but
most
most
importantly,
importantly
it's
it's
super
super
easy
easy
to
to
use.
use
So
so
let's
let's
jump
jump
right
right
into
into
things.
things
The
the
first
first
step
step
is
is
to
to
create
create
a
a
Google
google
Sheet
sheet
and
and
add
add
some
some
data
data
to
to
it.
it
You'll
you'll
notice
notice
I
i
have
have
2
2
columns
columns
here,
here
one
one
for
for
the
the
title
title
and
and
one
one
with
with
content
content
in
in
the
the
form
form
of
of
HTML.
html
Now
now
to
to
fetch
fetch
or
or
query
query
data
data
from
from
the
the
sheet,
sheet
all
all
you
you
You
you
have
have
to
to
do
do
is
is
highlight
highlight
it
it
and
and
you'll
you'll
notice
notice
when
when
you
you
do
do
that,
that
that
that
it
it
creates
creates
a
a
range
range
of
of
cells
cells
up
up
here
here
in
in
the
the
top
top
left
left
corner.
corner
That'll
that'll
come
come
into
into
play
play
later
later
when
when
we
we
start
start
making
making
requests
requests
through
through
the
the
API.
api
Now
now
in
in
order
order
to
to
make
make
the
the
sheet
sheet
available
available
to
to
the
the
app,
app
we
we
need
need
to
to
share
share
it
it
publicly.
publicly
When
when
you
you
click
click
the
the
share
share
button,
button
it
it
generates
generates
a
a
URL.
url
And
and
inside
inside
that
that
URL,
url
you'll
you'll
notice
notice
an
an
d,
d
which
which
is
is
the
the
ID
id
of
of
the
the
sheet
sheet
itself.
itself
You'll
you'll
wanna
wanna
make
make
a
a
note
note
of
of
that
that
ID
id
for
for
later.
later
The
the
next
next
step
step
is
is
to
to
generate
generate
our
our
Next
next
JS
js
application.
application
And
and
keep
keep
in
in
mind,
mind
you
you
can
can
follow
follow
the
the
same
same
basic
basic
process
process
for
for
any
any
Node.
node
Js
js
server.
server
We
we
can
can
run
run
npx
npx
create
create
next
next
app
app
from
from
the
the
command
command
line
line
and
and
then
then
open
open
it
it
up
up
in
in
Versus
versus
Code.
code
Whenever
whenever
working
working
with
with
a
a
Google
google
API
api
on
on
a
a
server,
server
you'll
you'll
want
want
to
to
install
install
the
the
REST
rest
client
client
for
for
your
your
preferred
preferred
language,
language
which
which
in
in
our
our
case
case
is
is
Node.
node
Js.
js
Run
run
npm
npm
install
install
Google
google
APIs,
apis
guys.
guys
And
and
we
we
can
can
use
use
this
this
package
package
to
to
simplify
simplify
the
the
authentication
authentication
process
process
and
and
give
give
us
us
IntelliSense
intellisense
and
and
Versus
versus
Code.
code
Now,
now
what
what
we
we
actually
actually
wanna
wanna
build
build
in
in
next
next
is
is
a
a
blog
blog
that
that
will
will
give
give
us
us
a
a
List
list
of
of
post
post
titles,
titles
then
then
clicking
clicking
on
on
a
a
title
title
will
will
take
take
us
us
to
to
the
the
post
post
page
page
and
and
render
render
out
out
the
the
HTML
html
in
in
the
the
Google
google
Sheet.
sheet
That
that
means
means
we
we
need
need
2
2
different
different
components
components
in
in
Next,
next
which
which
will
will
live
live
in
in
the
the
pages
pages
slash
slash
post
post
directory.
directory
The
the
first
first
one
one
is
is
the
the
index
index
page
page
and
and
the
the
second
second
one
one
takes
takes
a
a
dynamic
dynamic
ID
id
parameter,
parameter
which
which
corresponds
corresponds
to
to
an
an
actual
actual
row
row
in
in
the
the
spreadsheet.
spreadsheet
For
for
now,
now
we'll
we'll
just
just
go
go
ahead
ahead
and
and
leave
leave
these
these
files
files
empty
empty
because
because
before
before
we
we
can
can
do
do
anything,
anything
we
we
need
need
to
to
authenticate
authenticate
the
the
Google
google
API
api
to
to
our
our
project.
project
Authenticating
authenticating
a
a
Google
google
API
api
in
in
a
a
project
project
is
is
a
a
very
very
complicated
complicated
topic,
topic
which
which
you'll
you'll
find
find
out
out
if
if
you
you
try
try
to
to
read
read
the
the
official
official
documentation,
documentation
but
but
it
it
doesn't
doesn't
really
really
have
have
to
to
be.
be
There
there
are
are
multiple
multiple
ways
ways
to
to
authenticate,
authenticate
but
but
the
the
2
2
you
you
should
should
know
know
about
about
are
are
application
application
default
default
credentials
credentials
and
and
OAuth
oauth
2.
2
In
in
this
this
demo,
demo
we'll
we'll
be
be
using
using
default
default
credentials,
credentials
which
which
is
is
just
just
like
like
using
using
an
an
API
api
key.
key
The
the
limitation
limitation
though
though
is
is
that
that
you
you
only
only
have
have
access
access
to
to
resources
resources
on
on
your
your
own
own
Google
google
account.
account
That
that
works
works
fine
fine
for
for
us,
us
but
but
many
many
apps
apps
Using
using
Google
google
APIs
apis
want
want
access
access
to
to
resources
resources
owned
owned
by
by
other
other
users.
users
Like,
like
for
for
example,
example
if
if
our
our
app
app
wanted
wanted
to
to
access
access
files
files
in
in
somebody
somebody
else's
else's
Google
google
Drive.
drive
For
for
Apps
apps
like
like
that,
that
the
the
other
other
user
user
needs
needs
to
to
first
first
give
give
us
us
permission
permission
and
and
the
the
only
only
way
way
to
to
do
do
that
that
is
is
with
with
OAuth
oauth
2
2
authentication.
authentication
That's
that's
beyond
beyond
the
the
scope
scope
of
of
this
this
video,
video
but
but
it's
it's
important
important
to
to
make
make
the
the
distinction,
distinction
and
and
if
if
you
you
wanna
wanna
learn
learn
more,
more
I
i
have
have
a
a
pro
pro
video
video
on
on
FireShip
fireship
IO
io
dedicated
dedicated
to
to
it.
it
Now
now
that
that
we
we
decided
decided
we're
we're
using
using
default
default
authentication,
authentication
we
we
can
can
head
head
over
over
to
to
the
the
Google
google
Cloud
cloud
Platform
platform
console
console
to
to
enable
enable
the
the
API.
api
Now
now
the
the
Sheets
sheets
API
api
is
is
100%
100%
free,
free
but
but
you
you
may
may
need
need
to
to
have
have
a
a
credit
credit
card
card
on
on
file
file
when
when
creating
creating
a
a
new
new
Google
google
Cloud
cloud
account.
account
Once
once
you
you
have
have
a
a
Google
google
Cloud
cloud
account
account
and
and
project
project
set
set
up,
up
you
you
can
can
go
go
to
to
APIs
apis
and
and
services,
services
then
then
click
click
the
the
button
button
to
to
enable
enable
APIs
apis
and
and
services.
services
Type
type
sheets
sheets
into
into
the
the
search
search
bar,
bar
then
then
click
click
the
the
enable
enable
button.
button
That'll
that'll
take
take
a
a
second
second
to
to
enable,
enable
then
then
click
click
manage.
manage
From
from
there,
there
you'll
you'll
see
see
a
a
tab
tab
for
for
credentials.
credentials
Go
go
ahead
ahead
and
and
click
click
on
on
that
that
and
and
find
find
And
and
your
your
App
app
Engine
engine
default
default
service
service
account,
account
go
go
ahead
ahead
and
and
click
click
on
on
that,
that
and
and
then
then
you'll
you'll
find
find
one
one
more
more
tab
tab
for
for
keys.
keys
From
from
there,
there
you
you
can
can
generate
generate
a
a
key,
key
which
which
is
is
just
just
a
a
JSON
json
file
file
That
that
includes
includes
your
your
credentials
credentials
and
and
can
can
authorize
authorize
another
another
server
server
to
to
access
access
APIs
apis
and
and
other
other
resources
resources
that
that
you
you
own
own
on
on
Google
google
Cloud.
cloud
I'm
i'm
downloading
downloading
that
that
file
file
To
to
the
the
root
root
of
of
my
my
Next
next
JS
js
project
project
as
as
secrets
secrets
dot
dot
JSON.
json
But
but
it's
it's
very
very
important
important
that
that
you
you
don't
don't
expose
expose
this
this
file
file
publicly.
publicly
If
if
someone
someone
gets
gets
a
a
hold
hold
of
of
it,
it
they'll
they'll
I
i
have
have
access
access
to
to
your
your
Google
google
Cloud
cloud
account.
account
So
so
make
make
sure
sure
to
to
add
add
it
it
to
to
your
your
git
git
ignore
ignore
file
file
before
before
committing
committing
this
this
to
to
a
a
public
public
repo.
repo
Now
now
that
that
our
our
credentials
credentials
are
are
in
in
place,
place
we
we
need
need
to
to
make
make
them
them
available
available
to
to
our
our
Riverside
riverside
code.
code
And,
and
the
the
way
way
we
we
can
can
do
do
that
that
is
is
with
with
an
an
environment
environment
variable.
variable
The
the
Next.
next
Js
js
framework
framework
will
will
automatically
automatically
find
find
any
any
environment
environment
variables
variables
that
that
You
you
define
define
in
in
the
the
dotenv
dotenv
dot
dot
local
local
file.
file
Create
create
that
that
file,
file
then
then
inside
inside
of
of
it,
it
add
add
the
the
Google
google
application
application
credentials
credentials
fetch
fetch
roles
roles
environment
environment
variable
variable
and
and
point
point
it
it
to
to
the
the
service
service
account
account
JSON.
json
It's
it's
important
important
that
that
that
that
variable
variable
has
has
that
that
exact
exact
name.
name
In
in
addition,
addition
I'm
i'm
also
also
adding
adding
a
a
sheet
sheet
ID
id
environment
environment
variable,
variable
which
which
is
is
the
the
sheet
sheet
that
that
I
i
got
got
from
from
the
the
URL
url
in
in
the
the
Google
google
Sheet
sheet
earlier.
earlier
And
and
now
now
we're
we're
finally
finally
ready
ready
to
to
write
write
some
some
JavaScript
javascript
code.
code
We'll
we'll
first
first
go
go
into
into
the
the
post
post
ID
id
dot
dot
JS
js
file
file
and
and
import
import
Google
google
from
from
the
the
Google
google
APIs
apis
package.
package
Now
now
the
the
next
next
thing
thing
I'll
i'll
do
do
is
is
set
set
up
up
our
our
server
server
side
side
code
code
by
by
exporting
exporting
an
an
async
async
function
function
called
called
a
a
get
get
server
server
side
side
props.
props
If
if
you're
you're
not
not
familiar
familiar
with
with
Next.
next
Js,
js
I
i
do
do
have
have
a
a
video
video
about
about
that,
that
but,
but
basically,
basically
this
this
function
function
will
will
only
only
run
run
on
on
the
the
server
server
and
and
its
its
purpose
purpose
is
is
to
to
fetch
fetch
That's
that's
data
data
or
or
props
props
that
that
we
we
can
can
then
then
use
use
in
in
the
the
front
front
end
end
React
react
component.
component
So
so
first,
first
we'll
we'll
fetch
fetch
the
the
title
title
and
and
content
content
for
for
a
a
given
given
row
row
on
on
the
the
server,
server
then
then
take
take
that
that
data
data
and
and
render
render
it
it
out
out
in
in
the
the
browser
browser
or
or
client
client
as
as
HTML.
html
To
to
access
access
Google
google
Sheets
sheets
on
on
the
the
server,
server
we
we
need
need
to
to
authenticate
authenticate
the
the
API
api
client.
client
That
that
can
can
be
be
done
done
by
by
awaiting
awaiting
googleauthgetclient
googleauthgetclient
and
and
selecting
selecting
the
the
scope
scope
that
that
you
you
want
want
access
access
to,
to
which
which
in
in
this
this
case
case
is
is
the
the
spreadsheet's
spreadsheet's
read
read
only
only
scope.
scope
That's
that's
going
going
to
to
look
look
for
for
That
that
Google
google
applications
applications
environment
environment
variable
variable
that
that
we
we
defined
defined
earlier
earlier
to
to
authenticate
authenticate
us.
us
We
we
can
can
then
then
use
use
the
the
returned
returned
object
object
to
to
access
access
the
the
Google
google
Sheets
sheets
API,
api
by
by
passing
passing
it
it
as
as
the
the
auth
auth
param.
param
And
and
now,
now
we're
we're
ready
ready
to
to
make
make
our
our
first
first
query
query
to
to
the
the
spreadsheet
spreadsheet
or
or
database,
database
I
i
mean.
mean
We
we
want
want
to
to
fetch
fetch
an
an
individual
individual
row
row
number,
number
which
which
we'll
we'll
get
get
from
from
the
the
URL
url
as
as
the
the
ID
id
parameter.
parameter
Then
then
to
to
make
make
the
the
request,
request
we
we
need
need
to
to
specify
specify
a
a
range
range
of
of
columns
columns
in
in
cells.
cells
The
the
Google
google
Sheets
sheets
query
query
language
language
will
will
first
first
have
have
you
you
specify
specify
a
a
sheet
sheet
or
or
table
table
followed
followed
by
by
an
an
exclamation
exclamation
point,
point
then
then
the
the
range
range
of
of
cells
cells
that
that
you
you
want
want
to
to
access.
access
For
for
example,
example
If
if
we
we
want
want
row
row
23,
23
we
we
can
can
say
say
column
column
a
a
23
23
through
through
column
column
c
c
23.
23
But
but
in
in
this
this
case,
case
we
we
don't
don't
wanna
wanna
hard
hard
code
code
it,
it
we
we
wanna
wanna
use
use
the
the
dynamic
dynamic
ID
id
that
that
gets
gets
passed
passed
in
in
through
through
the
the
URL.
url
Now
now
we
we
can
can
make
make
an
an
actual
actual
request
request
to
to
the
the
API
api
by
by
awaiting
awaiting
the
the
sheets
sheets
spreadsheet
spreadsheet
values
values
get
get
method.
method
That
that
method
method
will
will
take
take
our
our
range
range
along
along
with
with
the
the
spreadsheet
spreadsheet
ID,
id
which
which
if
if
you
you
remember
remember
earlier,
earlier
we
we
set
set
up
up
as
as
an
an
environment
environment
variable.
variable
That'll
that'll
give
give
us
us
a
a
response
response
that
that
contains
contains
the
the
data
data
we
we
want
want
for
for
the
the
UI.
ui
The
the
data
data
is
is
formatted
formatted
in
in
an
an
array
array
and
and
I'm
i'm
using
using
destructuring
destructuring
here
here
to
to
give
give
the
the
values
values
a
a
name
name
of
of
title
title
and
and
content.
content
Now,
now
at
at
this
this
point,
point
we
we
can
can
run
run
the
the
next
next
app
app
by
by
running
running
npm
npm
run
run
dev.
dev
And
and
if
if
we
we
pull
pull
it
it
up
up
in
in
the
the
browser
browser
and
and
go
go
to
to
the
the
URL
url
of
of
post
post
slash
slash
3,
3
it
it
should
should
give
give
us
us
row
row
3
3
in
in
the
the
spreadsheet.
spreadsheet
And
and
that's
that's
what
what
I
i
would
would
call
call
some
some
next
next
level
level
sheet.
sheet
success
The speaker explains how Google Sheets can be used as a lightweight CMS database or headless CMS for building a jamstack site. They outline the process of creating a Google Sheet, connecting multiple Google APIs, and generating a Next. The process includes finding and downloading data from a Google Sheet, authenticate the Google API, and setting up server side code and exporting an async function to get server side props. They also explain how to use the Google Cloud Platform console to enable the API and generate a key for their app, and provide instructions on how to access their credentials and set up their server side code and export an async function to get server side props.
Google Sheets… Your Next Database?
What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it? That's impossible. Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack. And that's exactly what I'll show you how to do today by integrating Google Sheets with Next. Js. In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node. Js application, which also opens the door to connecting hundreds of other Google APIs to your server. If you're new here, like and Subscribe and follow along with the full write up on Fireship IO. But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database. First of all, it was never designed to be an app database. It's not going to be ACID compliant and doesn't support joins or complex queries. Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds. But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production. If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits. But, most importantly, it's super easy to use. So let's jump right into things. The first step is to create a Google Sheet and add some data to it. You'll notice I have 2 columns here, one for the title and one with content in the form of HTML. Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner. That'll come into play later when we start making requests through the API. Now in order to make the sheet available to the app, we need to share it publicly. When you click the share button, it generates a URL. And inside that URL, you'll notice an d, which is the ID of the sheet itself. You'll wanna make a note of that ID for later. The next step is to generate our Next JS application. And keep in mind, you can follow the same basic process for any Node. Js server. We can run npx create next app from the command line and then open it up in Versus Code. Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node. Js. Run npm install Google APIs, guys. And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code. Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet. That means we need 2 different components in Next, which will live in the pages slash post directory. The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet. For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project. Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be. There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2. In this demo, we'll be using default credentials, which is just like using an API key. The limitation though is that you only have access to resources on your own Google account. That works fine for us, but many apps Using Google APIs want access to resources owned by other users. Like, for example, if our app wanted to access files in somebody else's Google Drive. For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication. That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it. Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API. Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account. Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services. Type sheets into the search bar, then click the enable button. That'll take a second to enable, then click manage. From there, you'll see a tab for credentials. Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys. From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud. I'm downloading that file To the root of my Next JS project as secrets dot JSON. But it's very important that you don't expose this file publicly. If someone gets a hold of it, they'll I have access to your Google Cloud account. So make sure to add it to your git ignore file before committing this to a public repo. Now that our credentials are in place, we need to make them available to our Riverside code. And, the way we can do that is with an environment variable. The Next. Js framework will automatically find any environment variables that You define in the dotenv dot local file. Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON. It's important that that variable has that exact name. In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier. And now we're finally ready to write some JavaScript code. We'll first go into the post ID dot JS file and import Google from the Google APIs package. Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props. If you're not familiar with Next. Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component. So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML. To access Google Sheets on the server, we need to authenticate the API client. That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope. That's going to look for That Google applications environment variable that we defined earlier to authenticate us. We can then use the returned object to access the Google Sheets API, by passing it as the auth param. And now, we're ready to make our first query to the spreadsheet or database, I mean. We want to fetch an individual row number, which we'll get from the URL as the ID parameter. Then to make the request, we need to specify a range of columns in cells. The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access. For example, If we want row 23, we can say column a 23 through column c 23. But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL. Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method. That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable. That'll give us a response that contains the data we want for the UI. The data is formatted in an array and I'm using destructuring here to give the values a name of title and content. Now, at this point, we can run the next app by running npm run dev. And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet. And that's what I would call some next level sheet.
The speaker explains how Google Sheets can be used as a lightweight CMS database or headless CMS for building a jamstack site. They outline the process of creating a Google Sheet, connecting multiple Google APIs, and generating a Next. The process includes finding and downloading data from a Google Sheet, authenticate the Google API, and setting up server side code and exporting an async function to get server side props. They also explain how to use the Google Cloud Platform console to enable the API and generate a key for their app, and provide instructions on how to access their credentials and set up their server side code and export an async function to get server side props.

        [INST]
        You are youtuber called Fireship you make engaging high-intensity and entertaining coding tutorials and tech news. 
        you covers a wide range of topics relevant to programmers, aiming to help them learn and improve their skills quickly.
        
        Given the title of the video : Google Sheets… Your Next Database? 
        and a small summary : The speaker explains how Google Sheets can be used as a lightweight CMS database or headless CMS for building a jamstack site. They outline the process of creating a Google Sheet, connecting multiple Google APIs, and generating a Next. The process includes finding and downloading data from a Google Sheet, authenticate the Google API, and setting up server side code and exporting an async function to get server side props. They also explain how to use the Google Cloud Platform console to enable the API and generate a key for their app, and provide instructions on how to access their credentials and set up their server side code and export an async function to get server side props.
        [/INST]
        
        Generate the video : What if I told you there is a database that is 100% free, has a world class admin UI, and almost everybody in the world knows how to use it? That's impossible. Believe it or not, Google Sheets, the spreadsheet software, can be used as a lightweight database or headless CMS as the data layer in your application stack. And that's exactly what I'll show you how to do today by integrating Google Sheets with Next. Js. In the process, you'll learn how to easily fetch data from a Google Sheet, in any server side Node. Js application, which also opens the door to connecting hundreds of other Google APIs to your server. If you're new here, like and Subscribe and follow along with the full write up on Fireship IO. But before you get too excited about this amazing old tech, there are some important caveats when using Google Sheets as your primary database. First of all, it was never designed to be an app database. It's not going to be ACID compliant and doesn't support joins or complex queries. Actual data storage only scales to 5,000,000 cells and there's also an API quota that limits you to 500 requests per 100 seconds. But even with those limitations, Google Sheets It actually makes a great little database for prototyping, and there's really nothing stopping you from using it in production. If you're building a jamstack site, you could use it as a headless CMS, and it's very unlikely Likely that you'll ever hit the API quota or storage limits. But, most importantly, it's super easy to use. So let's jump right into things. The first step is to create a Google Sheet and add some data to it. You'll notice I have 2 columns here, one for the title and one with content in the form of HTML. Now to fetch or query data from the sheet, all you You have to do is highlight it and you'll notice when you do that, that it creates a range of cells up here in the top left corner. That'll come into play later when we start making requests through the API. Now in order to make the sheet available to the app, we need to share it publicly. When you click the share button, it generates a URL. And inside that URL, you'll notice an d, which is the ID of the sheet itself. You'll wanna make a note of that ID for later. The next step is to generate our Next JS application. And keep in mind, you can follow the same basic process for any Node. Js server. We can run npx create next app from the command line and then open it up in Versus Code. Whenever working with a Google API on a server, you'll want to install the REST client for your preferred language, which in our case is Node. Js. Run npm install Google APIs, guys. And we can use this package to simplify the authentication process and give us IntelliSense and Versus Code. Now, what we actually wanna build in next is a blog that will give us a List of post titles, then clicking on a title will take us to the post page and render out the HTML in the Google Sheet. That means we need 2 different components in Next, which will live in the pages slash post directory. The first one is the index page and the second one takes a dynamic ID parameter, which corresponds to an actual row in the spreadsheet. For now, we'll just go ahead and leave these files empty because before we can do anything, we need to authenticate the Google API to our project. Authenticating a Google API in a project is a very complicated topic, which you'll find out if you try to read the official documentation, but it doesn't really have to be. There are multiple ways to authenticate, but the 2 you should know about are application default credentials and OAuth 2. In this demo, we'll be using default credentials, which is just like using an API key. The limitation though is that you only have access to resources on your own Google account. That works fine for us, but many apps Using Google APIs want access to resources owned by other users. Like, for example, if our app wanted to access files in somebody else's Google Drive. For Apps like that, the other user needs to first give us permission and the only way to do that is with OAuth 2 authentication. That's beyond the scope of this video, but it's important to make the distinction, and if you wanna learn more, I have a pro video on FireShip IO dedicated to it. Now that we decided we're using default authentication, we can head over to the Google Cloud Platform console to enable the API. Now the Sheets API is 100% free, but you may need to have a credit card on file when creating a new Google Cloud account. Once you have a Google Cloud account and project set up, you can go to APIs and services, then click the button to enable APIs and services. Type sheets into the search bar, then click the enable button. That'll take a second to enable, then click manage. From there, you'll see a tab for credentials. Go ahead and click on that and find And your App Engine default service account, go ahead and click on that, and then you'll find one more tab for keys. From there, you can generate a key, which is just a JSON file That includes your credentials and can authorize another server to access APIs and other resources that you own on Google Cloud. I'm downloading that file To the root of my Next JS project as secrets dot JSON. But it's very important that you don't expose this file publicly. If someone gets a hold of it, they'll I have access to your Google Cloud account. So make sure to add it to your git ignore file before committing this to a public repo. Now that our credentials are in place, we need to make them available to our Riverside code. And, the way we can do that is with an environment variable. The Next. Js framework will automatically find any environment variables that You define in the dotenv dot local file. Create that file, then inside of it, add the Google application credentials fetch roles environment variable and point it to the service account JSON. It's important that that variable has that exact name. In addition, I'm also adding a sheet ID environment variable, which is the sheet that I got from the URL in the Google Sheet earlier. And now we're finally ready to write some JavaScript code. We'll first go into the post ID dot JS file and import Google from the Google APIs package. Now the next thing I'll do is set up our server side code by exporting an async function called a get server side props. If you're not familiar with Next. Js, I do have a video about that, but, basically, this function will only run on the server and its purpose is to fetch That's data or props that we can then use in the front end React component. So first, we'll fetch the title and content for a given row on the server, then take that data and render it out in the browser or client as HTML. To access Google Sheets on the server, we need to authenticate the API client. That can be done by awaiting googleauthgetclient and selecting the scope that you want access to, which in this case is the spreadsheet's read only scope. That's going to look for That Google applications environment variable that we defined earlier to authenticate us. We can then use the returned object to access the Google Sheets API, by passing it as the auth param. And now, we're ready to make our first query to the spreadsheet or database, I mean. We want to fetch an individual row number, which we'll get from the URL as the ID parameter. Then to make the request, we need to specify a range of columns in cells. The Google Sheets query language will first have you specify a sheet or table followed by an exclamation point, then the range of cells that you want to access. For example, If we want row 23, we can say column a 23 through column c 23. But in this case, we don't wanna hard code it, we wanna use the dynamic ID that gets passed in through the URL. Now we can make an actual request to the API by awaiting the sheets spreadsheet values get method. That method will take our range along with the spreadsheet ID, which if you remember earlier, we set up as an environment variable. That'll give us a response that contains the data we want for the UI. The data is formatted in an array and I'm using destructuring here to give the values a name of title and content. Now, at this point, we can run the next app by running npm run dev. And if we pull it up in the browser and go to the URL of post slash 3, it should give us row 3 in the spreadsheet. And that's what I would call some next level sheet.
        