Use a google sheet as your web app database (Slingcode)
Вставка
- Опубліковано 14 жов 2024
- Learn how to use Google Sheets as a simple database for your single-page progressive web app. This technique is easy to set up and doesn't require complicated authentication, libraries, or APIs. Go to bettersheets.co to get more out of Google Sheets.
PS You can follow along using the editor at slingcode.net
![[UA] NAVI vs MOUZ | BO5 | IEM Rio 2024](http://i.ytimg.com/vi/CMBYk0cbGoA/mqdefault.jpg)
Thanks for the shoutout Chris! Great way to make a quick website with dynamic data that you can fill in with a google sheet.
No problem, glad you liked the video!
Your method is so much more efficient for read-only!
Thank you, Chris! from the bottom of my heart for creating such an exceptional learning video. Your expertise and dedication have made a significant difference in my understanding and overall learning experience. I eagerly look forward to exploring more of your content and continuing my educational journey with your guidance. Thanks again.
Thank you I'm glad it was helpful for you.
Thank you very much for making something so simple and yet useful.
Thank you, I'm glad it's useful for you.
It worked. Beautiful! Now gotta figure out how to post.
You should be able to upload the contents of the zip file to any web hosting provider.
THIS IS A LIFESAVER! LOVE IT!
This is great. I am looking to create knowledge base. Can I add a search feature on the front end linking to the google sheets database?
can you share the code to search please, thank you very much
it’s a funny way of doing this.
looks like a cool hack to avoid the process of requesting credentials, setting up auth, writing some backend code to serve it all, etc. yes, you can’t modify the data this way, but you may not need to.
Great video, thank you! Is it possible to switch between different tabs in the google sheet using this method?
Yes it is possible to download a different sheet. Here's how:
1. When you switch sheets in Google Sheets, you will see the URL change with a parameter like `#gid=12345` on the end.
2. Copy the `gid=...` part.
3. Paste it onto the end of your fetch URL in the web app with an & like this: `&gid=...`.
After that when you make your request it will be for the sheet you targeted.
@@mccormix worked perfectly! Thank you very much!
Thank you for this video, very informative
Thank you, I'm glad it was useful!
Thank you very much, it's a great video
Hi this is great! You solve me a problem that tooka me more than 3 hours te resolve!.
Any idea how i can select/print a specific cell with this method? Thanks!
Yes, you can select a specific cell. Once the CSV is parsed you get an array of objects. Each object is one row in the sheet. So if you wanted to get the 3rd row you would do `csv[2]`. Then to look up the cell in that row you simply find it by name. For example: `csv[2]["price"]` will get the 3rd row and the column named "price". Best of luck.
Thank you sir. Can you add search box, so when I write "sword" in search box its only display Sword description and price? Hope for next tutorial🙏
You an do this with an "onchange" event listener. When the change comes in you filter the list.
@@mccormix Thank you sir
@@arylima6116 can you share the code to search please, thank you very much
if you want to perform CRUD action with this google sheet database then you would need to write some function at google sheet apps script side too. and in that case this won't gonna help. although nice tutorial
Can you read and write via the Web App to Google sheets?
No unfortunately you can not. I don't think even reading is working any more since they changed something on the server side.
thanks ! it is useful ... at my job they based the company computer logistic on googlesheet. they are getting bigger and they need a proper ERP.
BTW question : what if we have multiples sheets in a csv ?
Hello from India.. superb tutorial...easy to understand..very helpful... is it possible to write data back to sheet as well? If so kindly show us the way. Highly appreciated.
Is it not possible with this technique. You can only read. To write you need an API key and use a different method.
@@mccormix thx for replying...
i'm facing this error, kindly give me solution if you have?
from origin 'null' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.
It seems like they changed the CORS settings on the server and this technique no longer works.
Thanks for this great video and explanation. Can you suggest a way to do exactly this without using the "fetch" command? Unfortunately my hosting provider does not allow fetch.
You will need to find a hosting provider that allows fetch. Almost any hosting provider will allow this. For free you can use GitHub pages.
how to fetch google sheet data to github hosted website? i am facing cross origin problem
When I move the script code in script.js file and provide the src in script tag in html the code doesn't load table data, However when the same code is run in one single html page it works properly, any idea why it's not working or suggestions how to make it work
Try to open the developer console and see if there are any errors printed. If there are no errors make sure your script.js file is being loaded by putting `alert("hi");` at the top and see if the message shows.
thank you so much sir. best.
No problem!
i followed your code to a T, but when i get to the first step where it dumps the cell data into the page (7:05), nothing happens on my page. no data. Sheet is public, sheet link is correct, quadruple checked all the code... no idea why, but mine isn't pulling date into the html...
fyi, if i click the link it does download the csv file, so that appears to work... but the javascript doesn't appear to be working.
same here..
yeah same here, double checked code. On inspect i get the CORS policy error. Is this a new thing since this video tutorial.
It seems like they changed something on the server and this no longer works.
Thanks, do you know is it possible to export Google sheets table directly to some mysql table?
Yes it's possible. Here are some ways to connect a Google sheets table to a database: www.quora.com/Is-there-a-way-to-connect-Google-sheets-to-MySQL?share=1
Thank you, sir.
Do you know how to address the other sheet if there are multiple sheets in the document?
Nevermind, found it.
Where did you find the code for other sheets?
@@brianrompis7467 do you have example of using the other sheets in google sheets?
It is possible to download a different sheet. Here's how:
1. When you switch sheets in Google Sheets, you will see the URL change with a parameter like `#gid=12345` on the end.
2. Copy the `gid=...` part.
3. Paste it onto the end of your fetch URL in the web app with an & like this: `&gid=...`.
After that when you make your request it will be for the sheet you targeted.
Yes, you must use the `gid=` parameter, copied from the original Google Sheet URL when you switch sheets. See my other comment for more details.
Hi admin
!
THis code do not work with Browser Usage from 10'39
I did exactily like you
How to fix???
Please
I guess they have changed something on the server side and this no longer works.
Gracias por este gran aporte! Solo que estamos trabados en como colocar un filtro o un search Box de los datos que traemoa de gsheets, muchas gracias!!
Great video! Thank you, Chris!
I'm trying to add a css to create different "cards" for each row data (I'm also trying to add buttons). But when I tryied, the CSS was applyied just once to all the data in the page. I tryied to create a div, but when I do this, I only receive the data from the 1st row. Exist another workaround to create those tipes of "cards"?
What I did:
csv.forEach(function(row) {
main.classList.add('card');
div= main.innerHTML += row.Title + "" + row.Date + "" + row.Tag;
You need to use something like this:
div = document.createElement("div");
div.classList.add("card");
main.appendChild(div);
puedes usar bootstrap ejemplo: main.innerHTML += `
`+row.NCONTRATO+`
`+row.TIPO+`
`+row.SERV+`
`;
Sir, how can i write, edit, delete google sheet rows and cells using this tricks.
Using this technique it's read-only. If you want to write, edit, or delete you will need to use the Google Sheets API.
This is so useful. Thank you so much.
Glad it was helpful!
can you share the code to search please, thank you very much
Thank you!
No problem!
Thank you this great video sir. But, can you please do it with a dropdown choices? Thank you.
I'm sorry, I don't know what you mean. A dropdown of choices for what?
Dropdown lists of items for example
Could you please help? I need to know how to transfer data from google sheet to g-suit admin directory by using submit button for example! or when I submit a google form, the google form inputs go automatically to g-suit admin directory! I hope that I explained what I want correctly, please advise, thanks.
Hello! I'm sorry, I don't know how to do this. Best of luck finding a solution!
Can you suggest how to automatically update data on web page as soon as there is some updation on excel sheet. As refreshing the page again and again is not a good option nor is setInterval or timeout function. Kindly reply ASAP, a bit urgent
Apart from polling using setInterval, the way to accomplish this is to use the Google Drive push notifications interface. You can find more info about that here: developers.google.com/drive/v3/web/push
very nice and everything but it did not work for me, difficult to put together the library as you are.
Hello Maicon, sorry it didn't work for you. I intentionally move fast in the video so it doesn't get boring. If you let me know what error you are having maybe I can help.
(Had to edit my comment as I thought you were replying regarding a paid product, whoops!)
Its good . now also tell us how to change values from database.
It is not possible using this technique. You'll have to use the Google Sheets API.
Doesnt seem to work now.
Yes they must have changed something on the server side unfortunately.
Can someone please tell me what did i do wrong?
Could you please describe your problem in more detail so that people can help you.
You mean a static single page *website*. You know, the bit with the html extension.
Subswibed