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.
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
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.
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).
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.
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.
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.
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.
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!
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
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!
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
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
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 ❤
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.
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.
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.
Not acid compliant, but also not base compliant - not a dbms. What about synchronization (data races)? What occurs if two users request to update the same cell at the same time? Whos request "wins"? What happens to the additional request? Are data race conditions a concern? If so are they resolvable?
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.
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😅
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...
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!
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)
Welcome to Google Onsite Interview Interviewer : Hey what Database you use for your projects. Candidate : I use Google Sheets... Interviewer : STOP! You are hired.
hello Do you use this Google sheet extension which allows you to connect an API? I'm not sure what appscript is? does it only allow you to integrate an API like the extension I talked to you about above? do you also use make?
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.
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 😂
"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!!
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
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
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!
This is some next level sheet! 👌
Thumbnail said that
Oh sheet!
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.
"next" level "sheet".
I could not hold in my laugh there.. What a perfect end to this perfect video.
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
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.
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
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.
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.
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
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.
Was not expecting this AT ALL, but man you gotta believe me when I say this is going to CHANGE MY LIFE
👀
👀
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.
Very quickly becoming my favorite dev channel
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).
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
The real mystery is how can you release back to back 7min edutaining videos. Always amazing 🙏⚡️🔥🌊
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
Holy sheet this is awesome!
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.
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.
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.
We need FireSheet
Sheet is Fire, yo
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!
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
At 2:00 😂. "Holy sheet"
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've used it before with an Android app, but it becomes very slow when amount of data increases, so I changed to Firebase.
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...
Thanks for always informing us of the latest hot sheet
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
I just spent 3 hours trying to figure out the auth part of this. Thanks a ton.
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
Thank You so much! it was actually better than looking over the boring documentation.
that ending was such punch to the guts
amazing, 10 out of 10
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 ❤
Incredibly high quality content!
GERN stack. 😂
Man I love your videos, keep up the good work, I find it very useful!
jeff you are love
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!
The information is useful and the puns are hilarious 😊
That is some serious programming skill 😯
1:52
> npx create-next-app holy-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.
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!
I'm so happy I subbed to this channel lol
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.
thought it was a super early or a bit late april fools vid, but it wasn't and i love it !!
Your sheet is the best sheet of all the sheet!
Thank you for the advanced tips it helped me with my project.
Your course on next js may have changed my life
UI Prototyping: Google Slides/Draw
Code Editor: Google Docs
Database: Google Sheets
Hosting: Google Drive
Dev Computer: Google Pixelbook
Hotel: trivago
I was looking for this yesterday and you made this video 🔥🔥🔥
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.
Oh yeah, I used Goggle sheets for one of my projects too. But the API limit was a huge issue
Not acid compliant, but also not base compliant - not a dbms. What about synchronization (data races)? What occurs if two users request to update the same cell at the same time? Whos request "wins"? What happens to the additional request? Are data race conditions a concern? If so are they resolvable?
What do you recommend?
*Warning* - High level of sheet jokes in this video
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.
Oh my, this is perfect for a personal project of mine
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!
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
I've been waiting for this sheet for so long..
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...
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!
This also means you can use sheets as file storage if you base64 encode your binary files
Just imagine putting Google Sheets database in your resume and employers read it😂
Only do that if you don't really want that position.
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!
I love the "Next" level "Sheet" and the title Your "Next" Database.
Anyway, great video!
The internet: Nooo you cant use google sheet as a database
Data Scientist: data go rrrrrrrr
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)
Love your Next.js content!
wow, this is so nice, I will definitely give it a try, this weekend. thanks.
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
Tell me why when I was looking for database solutions my first thought was “I wish I could just use excel”
Your content is dope ❤️
Thank you for making a video for the solution that's not about using a third party with an affiliate code.
How would you use this with React Query? And how would you update the spreadsheet?
what do you do if you dont have an app engine default service account?
Please do a series about this topic ,iam waiting ❤️❤️
creating a 'holy-sheet' next app 😍
😂😂😂😂😂😂 I saw your joke 4:30
Hell yeah. I do this as well but I do static generation with it. Gotta go fast on those CDNs
I once made a survey using sheets as the datastore. It worked really well! was just a bit slow..
I did the same thing using PHP. great stuff
Welcome to Google Onsite Interview
Interviewer : Hey what Database you use for your projects.
Candidate : I use Google Sheets...
Interviewer : STOP! You are hired.
This would really be ideal for small personal custom projects when you're the only user and want to make your life easier!
Holy “sheet”!!! 👀👀😝
Just what I need. Thanks :)
Damn i just needed this, how can you read my mind
The acid compliant bit got me laughing.
hello Do you use this Google sheet extension which allows you to connect an API?
I'm not sure what appscript is? does it only allow you to integrate an API like the extension I talked to you about above?
do you also use make?
That's some next level sheet bruh