Google Sheets - Search DataTable using Bootstrap, DataTables, JavaScript with Sorting & Pagination
Vložit
- čas přidán 16. 05. 2022
- Learn how to build search functionality with sorting & pagination in Google Sheets using Bootstrap, DataTables, JavaScript (Apps Script).
Bootstrap 5: getbootstrap.com/docs/5.1/get...
DataTables: datatables.net/manual/styling...
#datatables #search #javascript
Original Web App Series czcams.com/play/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l.html
Google Sheets Form for Data Entry czcams.com/video/ZKYvrD-3Ksc/video.html
CRUD Form in Google Sheets czcams.com/play/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx.html
I am trying to find a way to do something in Google Sheets but none of your videos have had what I need. What I have is a sheet with 2 drop downs that a user selects a choice. Lets say that is on sheet A and the drop downs are J8 and J9 cells. I have a second sheet, we'll call sheet B that has all the data in 7 columns. Sheet B column A has all the choices that can be made with the J8 cell on sheet A, sheet B column B has the choices that can be made with J9 on sheet A. What I am trying to do is have a formula or query or something that I can enter in sheet A cell D15 that will take the selections made by the user and return the value from sheet B column C where column A and B are the values selected by the user. Ultimately I want 5 cells on sheet A that would be D15-D19 that would pull the respective values from sheet B columns C-G that align with the sheet B column A and B choices made by the user in sheet A J8 and J9. Sheet A J8 would be a text value for a name and J9 would be a number. As time passes, the user would increment higher the number in J9 and all the cells D15-D19 would update with the new numbers. Any help would be appreciated. Thanks.
@@musicinthemachine Check out VLOOKUP & FILTER functions. I have videos covering both on the channel.
Hey man!! First of all, congratulations for your videos, they are amazing!! On this video, I have done all the code on my apps script and for some reason, the datatable does not load the data, only the title. The menu and item works, the ui shows up on the screen when called, but not loads the data, and I have no idea why. When I open the executions I get a Failed in the function that gets the data (getSpreadSheetData), but no info attached to the error. If I console log the function, it works fine and in the execution logs I can see both the headers and the data. I’ve looked into all the code almost letter by letter, cannot find any issue or difference from yours. The only differences are in the links that we get from the bootstrap and datatables.net, I guess at this point they have newer versions but that shouldn’t cause any trouble right? My dataset have 8 columns all with text, no dates or any other data type. Can you give me a hint please? Thank you
@@RFVic1 Try to redeploy the app and see if that makes any difference.
@@ExcelGoogleSheets Thanks for your time. Not sure if I understood your tip. At any moment I deployed this script as an app. What you got in minute 29:40 I’m never able to see, basically I’m stuck in the screen of minute 08:37. Can’t seriously understand what could be wrong. Or the table is not being design for some reason, or the data is not being collected correctly, but the code is exactly like yours so…
OMG the timing could not of been any more perfect!!! This is exactly what I'm looking for and I've been assigned this project for work too! Thank you
Great tutorial as always. You have condensed a great many techniques (HTML, CSS, GAS, etc.) along with using 3rd-party services and platforms. So encouraging to see you develop and expand a project -- even with occasional bugs that you resolve as you proceed. Such a wealth of understanding highlighted in a functional and instructive tutorial, easily adaptable to different requirements.
Always the best videos for spreadsheet and google drive. I am just inspired for a lot of my Dashboards by your lessons. Your videos are the best videos in you tube
OMG! I can't believe you can do this in Google Sheets! Thank a lot!
I could not find anything to write except the comments here.Born to teach, amazing. Thank you so much.
:)
Thanks for demonstrating this. The way the different languages and sevices interact can be overwhelming.
I know the feeling. This is exactly how new web developers feel, there are many different puzzle pieces.
Your content is amazing! Thanks for everything.
Truly an amazing tutorial!
Thanks a lot man!
This is one of your best videos. Thanks!
Thanks!
Many thanks for spoon feeding me on this one!
Amazing video! Always useful!
wow just amazing as always...
hi thanks for the amazing tutorial (as always). youve once created CRUD on a table UI videos though (it helps me alot) but not on data tables. so i was wondering.. it would be so helpful if you bring us CRUD data tables tutorial, which are already packed with feature such as in this tutorial. thankyou
Thats Amazing!
Nice video like every times
This is exactly what I've been looking for! Thank you
Thank you ! Always the best!
great thing it would be great to be able to add the delete update and edit options
Pretty well explained as always a great tutorial!
nice videos, ur the best! will be nice to have some video using server-side datatable for one big sheet data (+20k rows). Because i'm trying without success
Amazing !!! How nice it would be if you could add crud buttons, to edit , add or delete from the spreadsheet, even this example is great. Good to follow you, thanks.
I have CRUD series on the channel.
Hey Man. Always been following your tutorials. You're amazing man as always. Learning a lot from youu. Thank youuu. Hope you can have another tutorial for this as Web App.
check out my tabulator video
I'll check that out. Im not familiar with tabulator yet. But do you have similar video on web app, wherein once cell data was clicked it will populate some data through a dialog box.
This is perfect!!! bro thank
Awesome!!!. If we want to add a logo(image) on the search window, how to do it. If possible please let us know.
It is truely beautiful work😊😊
👍
You are amazing.
I got to the section where you run loadSalesData but the table does not show up after the header. I get a console error saying that "There was an error during the transport or processing of this request. Error code = 404, Path = /wardeninit" not sure how to fix.
Sir I need to contact you .I have about 6 google spreadsheets in one google drive and those sheets are updated daily with different names I mean sheets names plus current date and I need to add all data in those spreed sheets to one spreadsheet located in different google account.so could u help me is such complected script
Great video. I believe this should also work very well with Tabulator instead of Datatables.
Never heard about Tabulator. Looks interesting.
Yup it works but then tabulator needs data in form of object and while in Datatables it is accepting data in array!
How do i add button to each row?
The table didn't load in my form. I already check in deployment and there are no error appear.
Can i get source code of this project
I had developed same by following the instructions but the table is not visible while running the program
Any idea how to add tfoot with this method? I searched all over the doc I couldnt find a way to add footers
I guess it depends on what you need in the footer, but here is one example datatables.net/extensions/fixedheader/examples/options/header_footer
I'm in awe..
:)
you are awesome man! do you teach or something? i want to learn more about appscript, can you help me with that? where do i start?
Cheers from Argentina!!
Thanks!
Thank You!
Useful video thanks. What other videos we would like to see ? May be an authorization app (username and password) to allow access to a Webapp.
I fear this type of video would be 4 hours long since there is no built-in apps script support for any of this at the moment.
Error with Permissions-Policy header: Unrecognized feature: 'ch-ua-form-factor'.
unable to load the table. after putting all the codes, the table shows blank. Need Help..
Excellent tutorial. I'm really wondering can a tutorial be any better!
Why are you using _const_ instead of _var?_
How could I add to that data table the possibility to download the info that’s shown into a pdf or xls ?
Here you go datatables.net/extensions/buttons/examples/initialisation/export
Videos are very helpful ,it improved my excel and spread sheet knowledge...
But I have one doubt. Can sheet get automatically update if we update master sheet ??
You can use setInterval function to run every x number of seconds to update your data. developer.mozilla.org/en-US/docs/Web/API/setInterval
Hi Sir, congratulations for this awesome video of yours, it really helped me a lot. I am a big fan! Thank you! May I request to see a video of this integrating the CRUD function for each row in the data entry i was some kind of puzzled with the prior video of your i.e. CRUD Form in Google Sheets. I was trying to build a CRUD web app GAS for my project.
and also how can I hide those columns that are not necessary to show?
I am the AP Coordinator for my school and I have been trying to find a way to allow students who are on our low-income list access to the low-income payment website. If they aren't part of the list I don't want them to be able to progress to the website. Is there a way to do this with Google Sheets and Apps Script?
Depends. If you have Google Workspace and they are a part of that domain, then it should be possible. Otherwise it will be very difficult, but still possible.
what do i change to make the table appear on open without creating a menu?
I think you should try to put createDialog function in onOpen function.
Yes, that's what you should do, but I'm not sure if this is going to work, there may be some restrictions on what is allowed on open.
are the code examples available anywhere for download?
on Patreon
Hey, i've been watching several videos of yours and find this datatable tutorial and use it.. But the table didnt show up like yours.. Can u help me?
Note : i didnt find any error in the log, andthe only difference is my data come from importrange formula. Triedto copy the data manually but same result
Which log did you look at? Did you check under Executions pane?
@@ExcelGoogleSheets Yup and already find the problem, my document.EventListener is at a wrong section -_-" 🤣
Thankyou for replying though you are an awsome teacher 🔥🔥
There you go!
Can I ask for a copy of the sheets with scripts?
It's available to channel supporters on Patreon
www.patreon.com/chicagocomputerclasses
How to search box outside data table? please
datatables.net/reference/api/search()
Hi. First of all, GREAT and comprehensive video!!! Kudos. Need your opinion. Im a part time teacher. Lets say I have a database of my students’ details (phone number, address, etc.) on sheet 1 and their attendance’s timestamp (class attendance using qr scan) on sheet 2. Is it possible to link/export their details automatically from sheet 1 to sheet 2 everytime they scan for attendance instead of just their name? If possible, cud u explain how?
Yes. VLOOKUP.
@@ExcelGoogleSheets Thank u!!! Much appreciated
Can you please make a project with multi level(4 to 5) dependent drop down(data coming from spreadsheet containing large data) in google form or in google sheet.
It's not possible in Google Forms.
@@ExcelGoogleSheets We have to read a column of spreadsheet from ,take the data and display it in the drop-down of the Google form. Is it not possible to read from Google form.
@@vinodkumarPrajapativnd No, not dependent.
You can just load once with a script on one dropdown, but it won't be dependent on another dropdown.
@@ExcelGoogleSheetsOnce a value is selected in one drop-down, we have take that value and search in the spreadsheet and show all unique values that appear in the next Column in spreadsheet , as a drop-down to the next filed in form.
There is no onEdit events in forms, therefore it won't really work.
That’s great BUT it’ll be greater if you could expand this tutorial to enable kind of simple crud operation.
If I write getRange(
'3:3') it works
Bro for me data is not loading at 29:50.2 kindly help. I tried doing everything again and again still not working. Fixed :) Thanks to Chat GPT & You :) working with charm!
👍
getRange('${rowNumber}:${rowNumber}') Why does this not work in my sheet
You have to use backtick. (Template literals)
@@Thongtele Thanks man !!!!