Web App Example w/ Google Sheets, Tabulator, JavaScript, Apps Script, HTML, CSS Tutorial
ฝัง
- เผยแพร่เมื่อ 25 พ.ค. 2024
- Learn how to build a Web App using Google Sheets, Tabulator, JavaScript, Apps Script, HTML & CSS.
Web App Tutorial Links:
Tabulator tabulator.info/
Google Sheets www.google.com/sheets/about/
CSS used: gist.github.com/chicagocomput...
All Import links: gist.github.com/chicagocomput...
Original Web App Series • Google Apps Script - W...
00:00 Google Sheets Web App Example
01:07 Web App Demo
03:39 Create Google Apps Script Google Sheets Web App Project
05:02 Get data from back-end / spreadsheet
08:35 Web App Tabulator core setup
13:02 Web App Head Deployment & Dev URL
16:00 Connect Front-End HTML, JavaScript with Back-End in our App
30:04 Tabulator Data Table Pagination
32:20 Create Drop Down option in our Web App
34:25 Edit Data in Our Spreadsheet / back-end
51:39 Status Messages
58:41 Sort Tabulator data by Date
01:03:27 Change the Theme
01:05:20 Edit other fields in our Web App
01:14:58 Autocomplete Dropdown Field
01:18:55 Checkbox & Task Progress
01:32:18 Web App Styling using CSS
01:40:46 Add Number & Currency Columns with Validation
01:56:05 Add Search Functionality
02:08:06 Add New Record Functionality
02:23:12 Deploy the Web App
#web #app #javascript
Original Web App Series th-cam.com/play/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l.html
CRUD Userfrom th-cam.com/play/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx.html
Clock in/Out Web App th-cam.com/video/KGhGWuTjJwc/w-d-xo.html
You are probably the most comprehensive apps script channel on TH-cam. Keep it up 👍
Thanks!
Yes
Your style, clarity and attention to the learners/beginners dilemma is bar none. You’ve helped me professionally and personally for many many years! Thank you!
Congratulations! I love all the WebApp Series and Apps Script tutorials
😀
Best Google scripts channel on TH-cam, hands down
Thank You!
Best soft soft tutorial for beginners on TH-cam! I'm an absolute beginner and all the other tutorials I've found on TH-cam have been so
Congratulations on your achievements. I'm retired from IT but enjoy learning, so having subscribed to your channel for a couple years now has been rewarding. And I like this tutorial format better than publishing separate video files. Thanks so much.
Thank you very much!
Your channel is the best for google sheets guides in my opinion you helped and still helping me understand a lot of concepts in google sheets and I am really happy for you channel growth! and I still think you deserve way more recognition.
Well deserved! Thanks again for the great lessons! I am sure this is only the beginning. Cheers from Brazil!
😀
Congratulations dude! Thank you so much for everything that you do!
😀
It's well deserved! You're an amazing teacher and your content is amazing. Hugs from Brazil.
Thank you! 😃
Happy to see you're getting credit. You're my goto channel for spreadsheet stuff. Started a new position in Quality cx support and your videos have help me build quite complicated and extensive documentation for my work and impressing my bosses... so thanks
Great to hear!
This is awesome stuff! Best down-to-Earth tutorials / crash course. Thanks a lot. Keep up the good work!
Thank You!
I think this is the content where you give to the point you feel unconformable. Thanks dude
Fantastic channel! Keep up the great work - truly exceptional stuff
Congrats on the well deserved accolade
Thank you! Cheers!
Your videos seem well prepared with a good sound quality, wich I think is important too.
Congratulations! Well deserved! Amazing video, extremely usefull
Thanks a ton!
Congrats on the play button, thanks for all your teachings 👍
😀
This is such a great tutorial! Thank you so much for creating excellent content like this!
Thank You!
Congrats on 100k and thanks for all the help!
Thank you!
Congrats on the 100k! I really love your videos 🤩
Thank you, Alex!!
You channel is amazing. Congrats for the prize! I love the way you teach us.
Thank You!
You are the best teacher in yhis subject.. very informative and precise... great job boss
Thank You!
Thank you a lot for the great tutorials! I've been watching your videos from the beginning and learned a lot. I started to learn JS because of you:)
Got an idea for next Tabular video if it will be :) Will be cool to build a dashboard combining Tabulator and ChartJS.
Cheers from Ukraine and wish you to reach 1M subscrubers till the end of the year :)
How exactly would charts be used with Tabulator?
@@ExcelGoogleSheets Thank you for your reply. Tabulator has filters and after data will be filtered it will be sent to Charts JS to build charts. In other words, Charts will retrieve data from Tabular. I'm not sure if it is good practice to build like that, but the idea was to build a dashboard, for example, for the sales department. You often use such data in your videos. I thought it would be convenient to use Tabulator to filter data and build tables, and Charts JS for charts :)
I've learned a lot with your content, I'm really grateful for your work I can only hope you keep it up... Best regards!
by any ans, but I can make what I envision, and that's the greatest gift to . You are, without a doubt, an expert teacher. You may
Congratulations professor! You deserve it!
Thank you!
Your videos have helped me so much professionally, I have received multiple recognitions at work for projects i based off your teachings!
👍
I learn a lot with your videos! Congratulations!!
Awesome! Thank you!
Congratulations man! thanks for all your hardwork!
I appreciate it!
I love all your videos! Thank you.
Thank You!
million thanks from Algeria
Great video, thank you for sharing!
Congrats！210 000 210K subscribers achievement！Love Learn Google Spreadsheets
Thank you so much for listening, I love all these scripting related videos ❤️
😀
Hi, Awesome video as always. Much appreciated how you do this for us having desire to learn.
Playing with the dates seems to pose a lot of problem when I try this.
Congratulations and thank you for all the great tutorials videos. I learned a lot.
Great to hear!
Thank you for this in-depth dive, love how you go back and forth and search for the different parts of code. Is there a place where I could just copy all of the code from this tutorial?
Congratulations.... 🌸🌸
The way you explain the details of GS app script writing I love the most.....
Thank you for making such wonderful tutorial videos..... ❤️❤️
Thank you so much 😀
Learned a lot from your tutorials, thank you 😊
😊
I learned a lot from you and your videos are the best ❤️ I'm very happy for you getting the shield and wish to get more.
:)
Excellent!
This is really what i want! Thank you!
👍
Great video, thanks
Amazing!!! thank you cant wait to try this.
👍
Congrats , thanks so much for everything you do in this channel, really learn a lot 👍from the channel
I appreciate that!
really like your channel !
what a great tutorial. ... what a great timeline you provide for us. THANK YOU so much for everything you do! .... if there is once again a little "coverage" about a WebApp and APPScript, may i kindly ask to cover or show us how to show images or links (provided by a google sheet). Either as a card or a table ;-)))
You doing an amazing job, I am learning a lot.
Thank You!
Thank you !!! And its really helpful !!
👍
You're Amazing, MASTER
🙂
fantastic tutorial @
Learn Google Spreadsheets
Thank You!
Great tutorial
Great video, very useful, thank you.
👍
Congrats Teacher!
Thank You!
Congratulations on your well-deserved success.
Many many thanks
Awesome your channel has helped me much
👍
Amazing content!
Appreciate it!
Awesome!!! Content. I have been following your videos on App script for the past few years, and it would be an understatement to term your videos as the BEST for app scripts. Please continue your great work. And in case if there is a follow up video for this tutorial (which I think will surely be, because of the useful & simple way of teaching), kindly consider adding User validation, add record with validation in a pop-up, print options.
I do have a query regarding this tutorial, I am not able to create a list items (like a dynamic dropdown) which would be fetched from a different sheet dynamically. Your feedback would be of great help.
What script did you try that didn't work?
MANTAP SEKALI 🤘🤘
Congratulations!👍
Thank You!
This is incredible, thank you very much. How would you go about enabling an input that allows for line breaks in a single cell i.e. what you'd usually achieve when using "Cmd + Enter" in Sheets on a single cell to produce multiple rows of text within this one cell. Have attempted to do so with {editor:"textarea"} however, this reverts to single line at the point you enter the break
Vey Useful
Thank you
thank you for all
long but worth
:)
thanks man
Congratulations and wish you all best to reach 1 million subscribers 🥳
😀
awesome!
Thank you! Cheers!
Thanks!
😀
Congrats dude!, this channel help me save from webhosting! HAHAHHAHA Kiddin'!
:)
Congrats
Could You please help me, this tutorial worked for me till 14:51, my work was working exactly the same as yours. Then at 14:53 yours had a table and I had no table. I commented to you just before with my scripts included, but TH-cam did not accept that comment. Thank you for your videos, I have watched quite a few of them and they have really helped improve on my google sheets. Your a great teacher and I enjoy learning from you.
- Your student in New Zealand
NICE!!
Thanks!
This is fantastic! I've gone through the whole tutorial. Could I ask you if you could continue to build on the tutorial. I would be interested in how I can install "Nested Data Trees". Since I can give the children the parent ID here and thus create row dropdowns. This brings me very close to a database I need.
If this video gets enough views then maybe I'll do follow up.
@@ExcelGoogleSheets Thanks, you are doing a great job. I am very grateful to you. you are one of the reasons why i am so successful in my job as a project manager today.
Congratulations!
Thank you!
congrats well deserved.
Thank You!
Sir,
You make the awesome tutorial practical.
Please make the how to filter date and time spreadsheets to app script from and today date.
:)
needed to go learn by acting!
wow nice
Thanks!
Hi! I’ve been following your lessons and they are very much helpful! Thank you so much! Can you also teach us how to automatically create a google spreadsheet file from a template google sheet file automatically using apps script?
What you mean by "template"?
Can we create functionality of using filter as we can do this in google sheet like you do SORT etc
Holy Cow, Teacher!!!
:)
Hello, Thanks for the video series a lot! I have a question, I hope you get to see this.
What if we have more records with same ID and so the user can click on either one and when that field is edited, it changes that row only. The instructions in video uses findNext(). which only returns the first instance so it throws an error whenever you try to change second or Xth row. I was trying to access onEdit even the Row number so I can put it in the editCell function but no luck. Thanks for the videos again and help would be appreciated! :)
Thanks So much sir . really you do great things. Literally i was waiting for this video... Plz do an flavor for me....
I want to learn add more button so that we can put more than 1 items like that.. For order punching Form
use a 2D array with getRange(""),setValues()
Useful tutorial as always. Open question : What is the next step when the spreadsheet is no longer enough to handle a lot of data ? I have many routines in Javascript working but data in spreadsheets are growing fast. Google Cloud maybe ?
Use a database. MySQL or PostgreSQL should be a good place to start. You can of course set these up on Google Cloud if you want.
Hello there, I have a question that is bothering me. I have a spreadsheet that works like an "app" using input from two fields and returning a calculated query (from the "database" sheet) while using the input fields for calculation and filter parameters. This app could be used by many people, but sharing it as a spreadsheet does not allow simultaneous "app function" use. I have no coding knowledge and only do intermediate to advanced sheets. Any ideas how to "share" a sheet as a simple web app? I googled extensively and somehow can't find an answer. Thank you.
if there is another video about tabulator, I would be happy about the following topics. Using Modules (probably not supported by Apps Script, but there is a way if I understand correctly), Nested Data Trees (Parent/Child with Sheets), Download/Print selected row by Tickbox selection, Set different views by button/dropdown (hide/show columns). Thanks
🤔
Nice tutorial. The problem with the State selection opening up instead of down is because of the height of the table, it will work if you increase it, I'm also having this problem when the selection values size is greater than the height of the table. I'd ask for a fix in the github issues page.
I've figured it out by the end of the tutorial :)
Hi!
Is there a way to build a matrix for multiple choice grid as in google form? Or Is it possible dynamically modify a gForm?
The Idea is like built a attending list pass (means a list of students) with multiple options like [Absent, Here, Delay], ...] used by different teachers with their assigned students.
I already built the lists in a ss from a general/master (whatever the name) db but publishing with a form is the pain.
Do you have any recommendation? Or a video already explaining something like that?
Also. How to distinguish when it is better to use, those css tools like bootstrap, materialize, and so. They all seams to be nice tools but wonder.
Thanks lot.
No video that I can think of.
Bootstrap, materialize are useful when you don't have time or don't know how to write your own CSS.
Materialize is good when you need a custom datepicker, but developers seem to have abandoned the project. Bootstrap has good support, but no custom datepicker.
Please make video on dynamic serial number which auto update with filter or addition blank row etc
1:17:40 Probably has to do with the pagination size, as the area is really small.
Hi there! (Sorry. I don know your name)
First of all. I want to thank you for such fine work you are doing these series. They are very instructional and helpful. Thank to that it becomes clear the relation between the side server and client implementation as well as the interface.
Second, I had a trouble though.
I didn't finish all the behavior of the data-table in this tutorial because it is more of configuration (the css stuff and filtering mainly). The problem is at "editCell" part. Once it is working in the test-mode, I am very disappointing after the table is perfectly working with two or three cells alerting: "Success modifying!". But then it starts alerting "Fail modifying!" very often.
I implemented some "console.log()"s to trace some values and they seem to be stable. Tried to modify the failure values at the ss and the sequence of editing at the front webapp. All behaves the same.
I started think of the quality of the internet connection, but the place where I intend to use an application of your contribution has a worse connectivity.
Well. Do you think I am doing something bad? Can it be improved?
This is the codes' segment where it seems to be troubling:
==================
table.on("cellEdited", function(cell){
//cell - cell component
const stdRef = cell._cell.row.data.stdRef
const field = cell._cell.column.field
const val = cell._cell.value
// console.log(stdRef)
// console.log(field)
// console.log(val)
if (["lastnF","lastnM","firstN","gender"].includes(field)){
elements.alerts.textContent = "Guardando cambios ..."
clearAlerts(elements.alerts)
//console.log("processing ...")
google.script.run
.withSuccessHandler( () => {
elements.alerts.textContent = "Se guardaron los cambios!"
clearAlerts(elements.alerts)
// console.log("transaction successed")
})
.withFailureHandler( (er) => {
elements.alerts.textContent = "Error guardando cambios!"
clearAlerts(elements.alerts)
// console.log("transaction failed")
})
.editCell({stdRef: stdRef, field: field, val: val})
}
==================
then at the server side... :
===================
function editCell(p){
const url = "docs................"
const pttrn = /[0-9a-zA-Z_-]*(?=\/edit)/
const ssId = url.match(pttrn)
const ssa = SpreadsheetApp.openById(ssId)
const ws = ssa.getSheets()[0]
const idCellMatched = ws.getDataRange().createTextFinder(p.stdRef).matchEntireCell(true).matchCase(true).findNext()
const columnCellMatched = ws.getRange("1:1").createTextFinder(p.field).matchEntireCell(true).matchCase(true).findNext()
console.log("idColmn: " + columnCellMatched)
console.log("idCell: " + idCellMatched)
if( idCellMatched === null) throw new Error("No matching record")
if( columnCellMatched === null) throw new Error("No matching field")
const recordRowNumber = idCellMatched.getRow()
const recordColNumber = columnCellMatched.getColumn()
ws.getRange(recordRowNumber,recordColNumber).setValue(p.val)
} // END editGender() : update value at server side
===================
Hope! You can take the time for give me an advise.
Regards.
Well, what does the error say? Did you check in Executions?
@@ExcelGoogleSheets Hi!
Thank you!
The console. At the web execution window, display consecutively these messages.
2099142304-warden_bin_i18n_warden.js:99 Net state changed from IDLE to BUSY
2099142304-warden_bin_i18n_warden.js:99 Net state changed from BUSY to IDLE
And that's all. Actually everything seems to be working well until it starts running on the " .withFailureHandler " part of the execution.
... But says nothing, only those messages all the time.
I mean Executions pane in apps script editor, not browser console.
@@ExcelGoogleSheets Hi there again!
Ooops! ...And Solved!
The message at server execution panel said
Aug 5, 2022, 11:27:55 AM Debug idCellMatch: null
Aug 5, 2022, 11:27:55 AM Error Error: No matching record at editCell(dataServerSide:51:37)
My data in the sheet is like
-----------
id, stdID, lastnF, lastnM, firstN, gender, stdemail, instemail, stdRef
1, 202101406, ACOSTA, MANZANARES, MARIANA, M, none, 202101406@ibpuebla.edu.mx, 202101406 :ACOSTA MANZANARES |MARIANA
-----------
Where "stdRef" is a dynamically built field. So I realize that it's me asking "editCell", searching by "stdRef", to find a cell that no longer exist after a prior editing.
... I used "stdID" instead. It is smooth working now. Love it. Now I can finish your tutorial to make it pretty.
Thanks a lot!
Hi, congratulations for the excellent work. A question, how would it be possible to search more than one column?
use a custom function.
function customFilter(data, searchVal){
return data.name == searchVal || data.age == searchVal
}
table.setFilter(customFilter, searchVal);
@@ExcelGoogleSheets Thank you very much, it helped me a lot in my studies. The code looks like this:
function searchData(e) {
const searchVal = e.target.value;
elements.table.setFilter(function(data) {
for (var key in data) {
if (data[key] && data[key].toString().toLowerCase().includes(searchVal.toLowerCase())) {
return true;
}
}
return false;
});
}
can you make a video on connecting spreadsheets with bigquery?
I want to create a QR check-in that will give an eligibility status by using a spreadsheet as a backend.
Thanks for the video, A small suggestion to make if it is okay with you.
After (verbatim) following your video, I got stuck in the second 29:13. The browser does not show anything. Not sure if it is related to the "google.script.run" function or the document.addEventListener("DOMContentLoaded", pageLoad). The back end is working well, by bringing up the data into an object but it does not populate the HTML. Anyone having the same issue?
can u make a delete function as well?
Buongiorno, grazie per la lezione; è stata utilissima. Potrei chiedere cortesemente se potesse implementare la funzione di stampa descritta in tabulator? ho provato in tutti i modi ma non sono capace di farla funzionare. Se vuole le condivido il foglio google che ho creato seguendo la sua lezione. grazie mille Davide
hi, the checkbox not working on mobile browser (safari browser)! how can i fixed? Thanks
Hey I've been watching your videos and learnt a lot. But I'm stuck at a problem. How can I get values of filtered columns and modify it?. It would be great help if you could make related video on this.
Thanks and keep growing 👍🏻
What you mean by "values of filtered columns"?
Like then way use get values to get the values of selected range. Can we do this after applying filters and get those values?