As someone who works somewhere where we are struggling to bring everyone out of excel hell and use real databases, I feel the need to hide that this exists.
I'm currently working on my first ever paid assignment and the people I have to deal with had a terribly big excel sheet that they called a 'database'. I was shocked and advised they needed to hire another person that could convert that into an actual database. I ended up only working on the frontend thank god.
We used this technique once in production for a one-off live event website, with 1.5M visitors making 200M requests combined (100,000 concurrent views). With a bunch of caching and prerendering in place, we only make Google Sheets API calls like once per minute, so _it does scale!_ 🚀 6:50 - To cache the result of `spreadsheet.values.get` call in-memory, the `p-memoize` package from npm can be used. 1:36 - Making a Sheet public is convenient, but not required to make it available to the app via the Service Account. At 4:05 notice how the Service Account looks like an email address. You can share your Google Sheet to that email address, and then the Service Account will have access to the Google Sheet. This way the sheet can be kept private. Great video as always!
To cache results is a great idea, since google doesnt provide a way to query data in sheets, and if you don't know which number of row of the sheet you data at you basicaly have to get all data of sheet and filter results. I had a project where estore had its products data in sheet, and app had to display data by a product SKU which was in a column. Ended up saving syncing sheet data in a real database every minute and query that database (which opened door for more advanced app functions)
it does not scale. caching is not scaling. Also don't forget to use a library for something that can be achieved in 2 lines of code, very classic for javascript development
Lol using actual mockup software can't be me! But in all seriousness, using Google Slides is actually pretty decent for mockups, especially when collaborating with a less technical team
Thank goodness for this video. I've been wanting to set up a website for a family friend's company where they can manage their inventory that is visible on their website without needing to be super tech savvy. I came up with the idea of using Google sheets, but just didn't have the time to figure it out. This video is going to save me a lot of time. Thank you!
Fun fact, you shared the sheet publicly with anyone who has the share link but you can also add your service accounts generated email to the sheets permissions instead so you don't have to make it public! This also works for anything in your google drive :)
We used Google Sheets a few years back to quickly build our design tables for mobile games. This allowed us to quickly rebalance and make major data changes without worrying about interfacing with the DB directly. We even built a sync tool that will do a two way sync between the sheet and the database. Was such a time saver in the end, totally recommend using it as a data admin tool.
In 2017, I have created my college project "Google sheet as database" and actually we had used spreadsheet as database using java servlet. In that project, through admin panel you can perform CRUD operation in googlesheet.
A WARNING TO ANYONE THINKING OF ACTUALLY DOING THIS! (even for prototyping): The caveats mentioned in this video cannot be overstated. I actually did some freelance work on an existing app built fully around a google sheets "database", so would like to share my experience. The app was only being accessed by a few dozen users at a time but was already grinding to a halt in production due to the API rate limit. Queries would randomly fail with unhelpful errors, leading to endless retries and a terrible UX. Even when not rate limited, both read and write queries are many, many times slower than a real database. I would argue it isn't good for anything, even prototyping, because it makes the feedback loop painfully slow. I ended up having to rewrite the entire backend of the mentioned app because it was tightly coupled to the sheets API. If you are going to use this for prototyping (and I would strongly recommend you don't), you MUST use the repository design pattern, otherwise, you will bake in the dependency on sheets and likely end up shipping it to production, which trust me, you really do not want to do at any scale.
Haha me too. It feels weird to use it sometimes but in a pinch it works really nicely lol (when I am too lazy to self host a db or use something like mongo). But it become impractical after that
Holy crap. I did this as a project in college to make it easier for random people to manage a DB. I thought it was a really jank idea but this is much better explained. Kind of gave me some nostalgia.
This seems like a nice solution for managing a product catalog for a store (if it's not rate-limited, otherwise things like Netlify are still infinitely better).
About 3 years ago I wrote a site that used the HTML directly from the sheet to render components directly into the front end, no server. Wildest thing I've ever done and the site is still operational.
The most underrated channel, this guy is working as an amateur, not as a professional who only works for money he researched and mix different topics that nobody can use in their company love you bro ❤
We use Google Sheets in production to interact with Firestore and change data in the Firestore DB. Any changes in the Firestore DB would be reflected in the sheet. We used something a lot simpler though called Appscript.
There are some things which you have missed 1. if you are using a service account you need not make the sheet public. just give access to the service account. 2. Google sheets has a text per cell limit of 50,000 which is big but if you want to store big JSON it's a bit of trouble. 3. Also OAuth2 is more prefered than a service account since you cannot access sheets created by service accounts whereas sheets created by OAuth are easily viewable. 4. Also if anyone wants to use it only on the client-side and not on the server-side. There is an npm package called gapi-client which is helpful but really painful to use. 5. Also if you are going to use public google sheets then there is no need to authenticate or creating a google cloud account. You can directly make a call to the sheets.
I have a client who's data I've been saving in GS for a long time. I was looking to create a frontend for the same. Finally found the best solution. Thank you
And here I was thinking of making a series of using GQL along with Firebase for a better Read experience. Guess we'll use Sheets instead. Dope video Jeff!
Why so much hate comments? This tutorial showcases how you can use google sheet as a database and how you can access the google sheet data via rest api's. That doesn't you should all do that. This can help someone who might work on a hobby project without money to buy servers.
This is the equivalent of using HTML/CSS instead of professional Image Editing software, which is something I definitely, 100% do not do. At all. Ever. Fantastic video as always!
I actually used google sheets in a hobby project last year I skipped to whole authentication process though. Each sheet has a public readonly API that can be used if the sheet is shared publicly. Just make sure only you can edit it
Would've made it more realistic if you were developing Google® Sheets based server in Google® Docs with Google® Golang & store them in the Google® Drive😅
Missing these types of videos. I don't like tech news, because somewhere or the other I got to know about them, but videos like these are priceless @fireship
Connecting to google apis using a service account key is one of the most pain in the butt things I've ever done. This is a great tutorial on how to do that. I wish I had this a long time ago.
You can also write a script in the google sheet itself and then just call it like an API. We actually recently implemented a similar thing. The sheet acts as a Dashboard for our clients.
I use google sheets as admin console, all backend services have separate sections in a single sheet, where i can configure things + see some stats and metrics that services update themselves, perfect and super easy to do no need to write an entire admin web app or something...
Google sheets is the perfect Dynamic Config or a database for product details if you're building a site for a mom and pop shop (allowing them to update themes and product details on the fly)
Beyond the fact that this is totally awesome, I just learned how to authenticate any service in GCP for my personal acct and wire up a boilerplate Next.js project. Three for the price of one!
I have learnt a lot of new things that I have never worked with from your videos, like AWS, nginx, kubernetes, graphQL(and other dbs), typescript to name a few. One thing that I don't understand is how they work together. I would really love to see a video where you design a mock system using all of these (and possibly more) and explain each of their roles and why you chose it (kinda like your reverse-cloud migration video using raspberry pi). Whenever I think of a software architecture I think of them as several layers that interact with each other. However, I am unable to assign which layer what belongs to by watching a stand alone tutorial about a single tool. Btw, I am a college senior pursuing CS major and I love your content. Thanks for all the awesome contents.
Believe it or not I have used this for a presentation to the managers in my company to get more money in our budget. I used Grafana running in AWS to graph the data from the sheets. I told them all the data they are seeing is from actual databases, they were impress with the presentation and increased the budget 😂
next video:
"why i switched to powerpoint as my code editor"
actually, there is a video of some dude recommending Word as a code editor 😂
@@kjn5991 joma 😂😂
Actually word not pp
it *is* turing complete
@@phatakom 😂
As someone who works somewhere where we are struggling to bring everyone out of excel hell and use real databases, I feel the need to hide that this exists.
I'm currently working on my first ever paid assignment and the people I have to deal with had a terribly big excel sheet that they called a 'database'. I was shocked and advised they needed to hire another person that could convert that into an actual database. I ended up only working on the frontend thank god.
@@ssholum I think even Excel experts hate Excel
@@eric.m5790 huh GitHub is from Microsoft? And they didn't develop GitHub but simply paid for it? Lol
@@lucasbudega too late, they already hired someone =)
@@MM-vr8rj as an ex Excel "expert", Excel sucks. As soon as i could i changed careers.
"this is some serious gourmet sheet"
😂😂
Google fiction
We used this technique once in production for a one-off live event website, with 1.5M visitors making 200M requests combined (100,000 concurrent views). With a bunch of caching and prerendering in place, we only make Google Sheets API calls like once per minute, so _it does scale!_ 🚀
6:50 - To cache the result of `spreadsheet.values.get` call in-memory, the `p-memoize` package from npm can be used.
1:36 - Making a Sheet public is convenient, but not required to make it available to the app via the Service Account. At 4:05 notice how the Service Account looks like an email address. You can share your Google Sheet to that email address, and then the Service Account will have access to the Google Sheet. This way the sheet can be kept private.
Great video as always!
Great results 💯 manh can you share some resources or code snippets on how you managed to do the caching bit?
U r genius!!!!
To cache results is a great idea, since google doesnt provide a way to query data in sheets, and if you don't know which number of row of the sheet you data at you basicaly have to get all data of sheet and filter results.
I had a project where estore had its products data in sheet, and app had to display data by a product SKU which was in a column. Ended up saving syncing sheet data in a real database every minute and query that database (which opened door for more advanced app functions)
it does not scale. caching is not scaling.
Also don't forget to use a library for something that can be achieved in 2 lines of code, very classic for javascript development
Thanks for the info about keeping it private! Awesome!!
This is some next level sheet! 👌
Thumbnail said that
Oh sheet!
next video:
why chrome's search bar is the best JSON editor
not gonna lie, I use Chrome's console as my main json renderer
@@holonaut isn't it devtools?
I'm using same LoL
Totally NO, 😂😂😂 why u need google sheets when u can save all your data in word files with colors and fonts 🤩🔥
Sheets has colors and fonts too
How about writing the data down on paper and manually sending back the requested data by retyping every single time like things are supposed to be?
@@zeroww7 how about writing that data in tatoos made in your skin so that it is never lost unlike paper that can fly away?
@@migueldomingos4570 How about just remember 4Head
@@kbabe3915 That is way too non painless
Next video:
"Why using Google Slides is the best way in designing applications"
but... figma is free (with limits but free)
Lol using actual mockup software can't be me! But in all seriousness, using Google Slides is actually pretty decent for mockups, especially when collaborating with a less technical team
Um. I actually used to develop ui when I started c# in 2015
I think I saw someone built circuits using PowerPoint...
I'm using it before I know Figma and Adobe XD. lol
No sponsor bs, no merch gread, pure teaching, pure learning 👌🏻
no useless intro, no cancer thumbnail, no talking boring and irrelevant shit. i hate people.
Thank goodness for this video. I've been wanting to set up a website for a family friend's company where they can manage their inventory that is visible on their website without needing to be super tech savvy. I came up with the idea of using Google sheets, but just didn't have the time to figure it out. This video is going to save me a lot of time. Thank you!
"next" level "sheet".
I could not hold in my laugh there.. What a perfect end to this perfect video.
0:52 If you're just going to read from Google Sheets, you can publish it as a CSV, get the URL and parse it. It won't eat up your API limits.
nice! im just need this!
And no credit card to set it up, if i understand it right.
Fun fact, you shared the sheet publicly with anyone who has the share link but you can also add your service accounts generated email to the sheets permissions instead so you don't have to make it public! This also works for anything in your google drive :)
Good call!
Tried it out & this works. This small fact would probably convince more people to try it out rather than making your data public.
Next video:
"Why using google contacts is the best solution for user accounts"
Next video: What made me prefer notepad over VS Code.
😂 exactly,
Big brain
Notepad? Lame. I prefer coding into a stone tablet using hieroglyphs.
Ram Size
I use nano file to code
Was not expecting this AT ALL, but man you gotta believe me when I say this is going to CHANGE MY LIFE
👀
👀
We used Google Sheets a few years back to quickly build our design tables for mobile games. This allowed us to quickly rebalance and make major data changes without worrying about interfacing with the DB directly. We even built a sync tool that will do a two way sync between the sheet and the database. Was such a time saver in the end, totally recommend using it as a data admin tool.
In 2017, I have created my college project "Google sheet as database" and actually we had used spreadsheet as database using java servlet. In that project, through admin panel you can perform CRUD operation in googlesheet.
The real mystery is how can you release back to back 7min edutaining videos. Always amazing 🙏⚡️🔥🌊
The cursed stack
Sheets for database
COBOL compiled to WebAssembly binaries for server side
XHTML and in line css for client side/styling
Client-Server communication via SOAP over WebSockets
Sounds good, have many clients running this Stack, I call it the India street Stack
Hey at least such stack would be secure as heck no hacker would even believe such stack to exist in the first place
now, we have tail wind css and bootstrap 5, which is basically in line stylesheet.
I never worked on the Excel/COBOL stack but I already have software dev generational PTSD reading this.
Very quickly becoming my favorite dev channel
Our team at Microsoft already using it for more than a decade, love to see it in action by new developers
Can it handle 100k user per second?????
A WARNING TO ANYONE THINKING OF ACTUALLY DOING THIS! (even for prototyping):
The caveats mentioned in this video cannot be overstated. I actually did some freelance work on an existing app built fully around a google sheets "database", so would like to share my experience.
The app was only being accessed by a few dozen users at a time but was already grinding to a halt in production due to the API rate limit. Queries would randomly fail with unhelpful errors, leading to endless retries and a terrible UX. Even when not rate limited, both read and write queries are many, many times slower than a real database. I would argue it isn't good for anything, even prototyping, because it makes the feedback loop painfully slow.
I ended up having to rewrite the entire backend of the mentioned app because it was tightly coupled to the sheets API. If you are going to use this for prototyping (and I would strongly recommend you don't), you MUST use the repository design pattern, otherwise, you will bake in the dependency on sheets and likely end up shipping it to production, which trust me, you really do not want to do at any scale.
hi, im a beginner developer in need of a database that can store unique codes for a store franchise. What database do you recommend ?
@@awakenwithoutcoffeeMySQL or PostgreSQL.
+1 for the repository pattren so it is easier to switch to an other data store with ease
@@awakenwithoutcoffee supabase
lmaoooo
I've been doing this for years my dude lol
Seriously, what could be a good use of it,,, i mean i can make a json file in the backend and serve all the data
@@dailymeow3283 not having to manage a whole backend for simple content.
Although this really should never be used for anything important.
@@dailymeow3283 I could use it to serve some static files
Haha me too. It feels weird to use it sometimes but in a pinch it works really nicely lol (when I am too lazy to self host a db or use something like mongo). But it become impractical after that
@@dailymeow3283 usually the excel comes from user or something
next video:
"Why Google Docs is the best IDE"
@@tsnos2 wasn't it Microsoft doc? the JOMA one?
Guess he's going for a job at Google, good on him
We need FireSheet
Sheet is Fire, yo
Holy crap. I did this as a project in college to make it easier for random people to manage a DB. I thought it was a really jank idea but this is much better explained. Kind of gave me some nostalgia.
This seems like a nice solution for managing a product catalog for a store (if it's not rate-limited, otherwise things like Netlify are still infinitely better).
Holy sheet this is awesome!
About 3 years ago I wrote a site that used the HTML directly from the sheet to render components directly into the front end, no server. Wildest thing I've ever done and the site is still operational.
The most underrated channel, this guy is working as an amateur, not as a professional who only works for money he researched and mix different topics that nobody can use in their company love you bro ❤
We use Google Sheets in production to interact with Firestore and change data in the Firestore DB. Any changes in the Firestore DB would be reflected in the sheet. We used something a lot simpler though called Appscript.
There are some things which you have missed
1. if you are using a service account you need not make the sheet public. just give access to the service account.
2. Google sheets has a text per cell limit of 50,000 which is big but if you want to store big JSON it's a bit of trouble.
3. Also OAuth2 is more prefered than a service account since you cannot access sheets created by service accounts whereas sheets created by OAuth are easily viewable.
4. Also if anyone wants to use it only on the client-side and not on the server-side. There is an npm package called gapi-client which is helpful but really painful to use.
5. Also if you are going to use public google sheets then there is no need to authenticate or creating a google cloud account. You can directly make a call to the sheets.
The last point was a violation. Lol. Appreciate the feedback though!
UI Prototyping: Google Slides/Draw
Code Editor: Google Docs
Database: Google Sheets
Hosting: Google Drive
Dev Computer: Google Pixelbook
Hotel: trivago
I have a client who's data I've been saving in GS for a long time. I was looking to create a frontend for the same.
Finally found the best solution. Thank you
And here I was thinking of making a series of using GQL along with Firebase for a better Read experience. Guess we'll use Sheets instead.
Dope video Jeff!
Why so much hate comments? This tutorial showcases how you can use google sheet as a database and how you can access the google sheet data via rest api's. That doesn't you should all do that. This can help someone who might work on a hobby project without money to buy servers.
This is the equivalent of using HTML/CSS instead of professional Image Editing software, which is something I definitely, 100% do not do. At all. Ever.
Fantastic video as always!
I actually used google sheets in a hobby project last year
I skipped to whole authentication process though. Each sheet has a public readonly API that can be used if the sheet is shared publicly. Just make sure only you can edit it
Would've made it more realistic if you were developing Google® Sheets based server in Google® Docs with Google® Golang & store them in the Google® Drive😅
Lol
Your course on next js may have changed my life
Incredibly high quality content!
A few days ago working on a Google Sheets plugin I thought exactly the same thing!
If only Google App Script were a little bit more stable...
I just spent 3 hours trying to figure out the auth part of this. Thanks a ton.
I've used it before with an Android app, but it becomes very slow when amount of data increases, so I changed to Firebase.
that ending was such punch to the guts
amazing, 10 out of 10
Missing these types of videos. I don't like tech news, because somewhere or the other I got to know about them, but videos like these are priceless
@fireship
Thank You so much! it was actually better than looking over the boring documentation.
You may love google sheet or hate it but some bi4 s clients want to use it. Video is straight to the point. Thank you!
Thanks for always informing us of the latest hot sheet
Connecting to google apis using a service account key is one of the most pain in the butt things I've ever done. This is a great tutorial on how to do that. I wish I had this a long time ago.
You can also write a script in the google sheet itself and then just call it like an API. We actually recently implemented a similar thing. The sheet acts as a Dashboard for our clients.
jeff you are love
I use google sheets as admin console, all backend services have separate sections in a single sheet, where i can configure things + see some stats and metrics that services update themselves, perfect and super easy to do no need to write an entire admin web app or something...
Oh my, this is perfect for a personal project of mine
That is some serious programming skill 😯
1:52
> npx create-next-app holy-sheet
This is a great idea, think of all the companies down the road trying to decouple and re-normalize their data inside these piece of sheet databases!
I've been waiting for this sheet for so long..
Your sheet is the best sheet of all the sheet!
"Next level -shit- sheet"
I am pretty sure the whole purpose of this video was to make that joke in the end.
that would actually be awesome XD
1:58
Holy sheet
It's better when you remember he was using a Next.js app to demonstrate it, so it's literally a Next level sheet.
thought it was a super early or a bit late april fools vid, but it wasn't and i love it !!
GERN stack. 😂
Thank you for making a video for the solution that's not about using a third party with an affiliate code.
Oh yeah, I used Goggle sheets for one of my projects too. But the API limit was a huge issue
Google sheets is the perfect Dynamic Config or a database for product details if you're building a site for a mom and pop shop (allowing them to update themes and product details on the fly)
I'm so happy I subbed to this channel lol
Beyond the fact that this is totally awesome, I just learned how to authenticate any service in GCP for my personal acct and wire up a boilerplate Next.js project. Three for the price of one!
I really used Google Sheets for the database of my school project(about doing mobile app), glad that I am not the first person doing it
The information is useful and the puns are hilarious 😊
I have learnt a lot of new things that I have never worked with from your videos, like AWS, nginx, kubernetes, graphQL(and other dbs), typescript to name a few. One thing that I don't understand is how they work together. I would really love to see a video where you design a mock system using all of these (and possibly more) and explain each of their roles and why you chose it (kinda like your reverse-cloud migration video using raspberry pi).
Whenever I think of a software architecture I think of them as several layers that interact with each other. However, I am unable to assign which layer what belongs to by watching a stand alone tutorial about a single tool.
Btw, I am a college senior pursuing CS major and I love your content. Thanks for all the awesome contents.
This also means you can use sheets as file storage if you base64 encode your binary files
This would really be ideal for small personal custom projects when you're the only user and want to make your life easier!
Thank you for the advanced tips it helped me with my project.
That's some next level sheet bruh
I once made a survey using sheets as the datastore. It worked really well! was just a bit slow..
I was looking for this yesterday and you made this video 🔥🔥🔥
Man I love your videos, keep up the good work, I find it very useful!
sheets.new is a quicker way to create new sheet
🔥🔥🔥
@@Fireship 🔥🚢
Works for all other google software (Docs.new, sheets.new etc..)
wow
docs.new and slides.new too!
creating a 'holy-sheet' next app 😍
I love the "Next" level "Sheet" and the title Your "Next" Database.
Anyway, great video!
IDE: Google Docs
Front End: Google Slides
Database: Google Sheets
Please do a series about this topic ,iam waiting ❤️❤️
Love your Next.js content!
I love modern compute technology. It's gotten so easy to use, and absurdly cheap.
Microsoft Access is still my #1 DB
Yeah I ran a whole business with sheets and Python. The gspread library is awesome.
this guy is single-handedly teaching me what my professors should've taught me in college lol.
This is something I'm already using in my website as a database to accept data.
That ending, holy sheet.
Believe it or not I have used this for a presentation to the managers in my company to get more money in our budget. I used Grafana running in AWS to graph the data from the sheets. I told them all the data they are seeing is from actual databases, they were impress with the presentation and increased the budget 😂
Find this implementation rather complicate when you can create an Apps Script to generate a json api from a google sheet and read the data from it.
what do you do if you dont have an app engine default service account?
NEXT LEVEL SHEET
6:25 I was expecting him to say "... followed by a BANG! ...."
that is some next level sheet
At 2:00 😂. "Holy sheet"
Hey, would be awesome if you could cover some more niche js stuff, like generators, Proxy or some cool esnext features
This is so disgustingly hacky ...I love it
Just what I need. Thanks :)
I did the same thing using PHP. great stuff