Web App - Load Data From Spreadsheet - Google Apps Script Web App Tutorial - Part 4
Vložit
- čas přidán 16. 01. 2019
- Learn how to run JavaScript server-side code in your HTML template, pass variables to template, and load data from your Google Sheets spreadsheet to your Web App.
#webapp
Excellent. This channel is by far the best tutorial sites I've seen on CZcams. Congrats buddy.
if you see how many people keep watching, you can see from 100% started our project , only 15% really make it , or at least it look like,, its a shame this channel has such a great value
This feels like a lecture in my class, I feel so sleepy. But I still wanna watch it because you explained it better than my lecturer's.
Want to say thank you again, based on your tutorial I began to code and a year later now I can code with Javascript/HTML/css pretty well and finally complete building my web app for personal use! Your tutorials help a lot to people like me.
Great to hear!
Здесь и здесь и
Х
This series is great. If you want tab to work in the Google App editor, toggle the auto-indent button. Auto-indent feature is basically useless anyway, so you are not losing much.
very helpful tutorial, i am enjoying your series so far and would like to learn a few more things related to this video and the previous one (which i have commented on)
1) making a field Required, so as that if trying to submit the form without filling that field, it takes us to the field with an error
2)populating fields with through sheet input( i am not talking about drop downs here)
excellent explanation! Smart and neat way to deal with the list on server side by map-html-join methods instead of on browser side by complex hybrid codes.👍🏿
Awesome! 4 days ago, js was for me like chinese characters.....but you make it very easy to understand. Thanks so much!
This is the best fucking channel I have come across. I have learned so much from this guy. I work in a small NGO and people are always amazed at the level of automation I have brought in because we don't have a lot of money to buy expensive tools, so I end up learning and creating some work around myself, and channels like these are great help. A lot of the knowledge that enables me has been acquired here. Thanks a lot man!
The best!!!!!!!!!!. I am your number-one fan..... please don't stop doing this kind of things
These modules are really awesome. I have really learned a lot from these series. Great learning experience. Thanks a lot.
Genius!...love you! Great teacher...i always can find what i need. Thank you
Excellent, easy to follow. Good job.
Hi, would you be able to add the dependent dropdown selection to these series ?
Great content. Amazing explanation. Can't wait to learn more. There not many tutorials for google scripts. I hope you cover everything I need. :))
Greetings from Ecuador. You are the best, thanks for all the knowledge.
Dude you're a great teacher!
You’re awesome. Your tutorials are the best. Well explained
Best teacher on CZcams
Hi! Thank you for this wonderful videos. A question: Materialize has a very useful select with "optgroups", and also the option of adding images at the side of each option. Do you have any idea about can I implement this function with this group select and images? Thanks!
This series of 4 videos are simply an excellent way to begin learning Web App. I finished up but now I need that lists dynamically refreshes as user adds information. How is the way to do that ? With a new function that loads the ranges again when submit button is pressed ? Already I have in the sheet the formulas that remove items from lists dinamically based on certain conditions but I need to reload the page to see the new values in the lists. Thanks in advance.
Thank you for the great tutorial :-) I have 1 question, how do I filled the listbox with column headers instead rows? example I want column header (text) a, b, c and e in the list. Beste regards, Steven
I needed this video!!! Thank you!!!
Excellent explanation, very useful!
I can't use the data I pass with the so called "render" function in the section of Html. It would be extremely useful to know how to do it. Thanks!
This is a brilliant video! Your are an excellent teacher, probably one of the best i have watched! Is it possible to get the source code for this?
Great tutorial. Thanks!
very good, I'm looking forward to see Google Apps Script Web App Tutorial - Part[i]; :-)
may you can also show how to get a 2D-Array in a html
In my array I have one column with image-urls which i like to display in the
Thanks sir your great..
I just want to ask how to change the font size of materializecss select option box..
while passing variables to the template, would that work with javascript variables? I meant the example you have explained inside the HTML DOM element. but the same functionality would work inside tag or separate script file?
Sei un grande!!! complimenti
Great Video. Thank you very much.
would you mind to post the code please? I followed your series step by step until here, but for some reason, it stoped working here. Thanks
This tutorial is amazing. Good Bye Google Forms!!!! Hello Google Web App!!!!
Google Forms are Excellent Work!, Google Apps is very useful!, Learn Google Spreadsheets!
Thanks so much for your videos! Is there a way I can do what you did with the selection fropdown but with the multiple select? I've tried but with no success doing this "" Help! Thanks in advance
super videos.
Hello, could u make a video about (CRUD Data (Create, Read, Update and Delete Data) ). i want to make a userform like in excel where i insert, edit, delete and update data. I want to do it via HTML and Google sheets Web App.
Agreed! Interested in doing exactly this
Hello, I am very interested in using this information to make a web app for a video game I play. It requires me to keep track of the equipment people are using and I would like to be able to implement this app and use a Google Sheet to pass the data through just like you have here.
Would it be possible to make it CHANGE a line in the document for a specific person and not add a NEW line on the document?
Excellent explanation! 🙌
Glad you liked it
These videos are fantastic. Thank you so much!
I'm having trouble with the autocomplete (materializecss - forms). For some reason I'm unable to pass dynamic data from apps script to the Javascript (html) file using templates as you've done here with the drop down. Your assistance would be sooo helpful!!
Here's my code on the html side:
var dynamicList =
var options = {
data: dynamicList
,};
document.addEventListener('DOMContentLoaded', function() {
var elems = document.querySelectorAll('.autocomplete');
M.Autocomplete.init(elems,options);
});
I'm not sure what your list is, but it needs to be an object with property value pairs.
Also you should be using onsucesshandler to set your dynamicList variable.
You are the best !!!
Hi! Nice videos! I made a Auto complete input and i wanted to make a another auto complete that changes the data dynamically ...There's a way to make it?
This channel is fantastic, maybe the best channel about GAS. I'd like to know where I can find support material to learn these GAS syntaxes in addition to this channel. Thank you.
Their documentation?
@@ExcelGoogleSheets Thanks for the prompt reply, but I mean about things like these:
Now a days Ecmascript 6 is available. So at minute 14:48 instead of a for loop I did a forEach as below:
Thank you very much professor!
you are so useful and i love u
Excellent Tutorial! Question: After making the changes in the final part of the video (running scrip from Code.gs instead of the HTML), there are now several spaces between all the items on my dropdown menu. How would I fix that?
Yea, i have the same issue too. Wonder why it went wrong.. 🤔 is it possible that this only happen to those using Windows OS?becoz the author demo'ed using Mac.
I found out the culprit already. It happens when we didn't put the '/' at the second syntax to "close" each item per option. It quickly resolved when I added it.
Your way of explaining things is just brilliant. You really helped me a lot. I do not get one thing: why the variable we pass to html file (e.g. title in your example) is not recognised in part of html? Does it work in html body within only? Is there way a function within the part can access the variable? Thank you!
because script is loaded using another function, so the function scope is different. if you just add the code right in the first html file it will work.
@@ExcelGoogleSheets Thank you! It works perfectly.
Can we use this feature in the autocomplete filed? How much data can be obtained form spreadsheet at most.
Amazing!
Hello, is there a different way to pass the options to the select materialize without using the template? I see the doc but I am not able to use it. Can someone explain me that?
HI, great video, but where to download the sample code you work with from the very beggining?
Thanks again.
drop down is dependent with Checkbox do you have script for that?
Good video, can you please make a dependent list from another list, on Two levels
Please!
Hello, This is a really good reference video, however I am having problem on some modifications from your code. I obtain the values from Google Sheet into Code.gs, and make it as an array variable. But I have javascript on the html file (the script is between the script tags in html), and would like to pass that array variable to a variable inside the javascript part. Do you know how to do it?
Hello I have the same question. Have you found the solution yet?
Y is it that we dont need tmp.list in the html side.
But we need tmp.list to log values of the list?
thank you so much.
Great
Hey, I have a query. Once I select a option in that option list, It will not allow to select that disabled option. How could i do that? please help me out
Hi team
Ur videos are very informative. I need a help of urs to understand how to refresh the page to get the latest data from spreadsheet for drop down list. I tried Windows.location.reload() but it displays blank page once runs. Could you please help me out in this.
Thanks in advance
THis is awesome... How would I update the dropdown items. I have created 2 dropdown dependend, but after the first dropdown in selected I cant get the second dropdown to update. Is this an easy fix in Materialize?
Is it possible to use "query" to get data from spreadsheet and to show it in html?
Great tutorial overall, but sir is it possible to link this web app form into another spreadsheet,, so for exampl, I have a spreadsheet links, when im paste or open that link, it will redirect to the app form, and then when im done submit all of those data it will redirect to the spreadsheet... so there is 2 spreadsheet, one is that i mention, the last is the present that hold of those logi in information, is it possible to do that ?
your reply is very helpfull
best regards
Two thumbs up
I appreciate your efforts.
Tried to figure out a way to make dependent drop-down lists that interacts based on user choice selection on the WebApp (updated from SpreadSheet)
Could you please explain this?
Thanks in advance.
There will be new userform series coming this month that include dependent drop-down lists. Subscribe or check back for updates.
@@ExcelGoogleSheets Thanks a lot. That would be really great!
hi, Thanks for the Great tutorial so far. Awesome. i have been following all your videos up to this one and all the code works on the OSX browsers. however, on iOS safari/chrome failed to select this tutorial's drop down SELECT-OPTION. is this normal? only browser-default works for the iOS browsers
No, it's not normal.
@@ExcelGoogleSheets just been to materializecss on iOS on the iPhone X and iPad... couldn't get any drop down to select properly. only default working for my mobile devices... its so weird
@@nchan9723 I haven't tried on mobile. Does the same happen when you go to their website? materializecss.com/select.html
Yes this is great
@ExcelGoogleSheets how can we work with a dependant dropdown of two different google sheets ? thanks in advice
That is so Great. Thank you so much. Could you show how to make a drop box with list dynamic when you write some words in box. I mean when i write "e" ( all word included "e" be showed )
Very interesting, i have followed and made all!!!, , only things that i don't understand is that when i converted the list in an html dinamic list, each option is showed at the list n the app with a like 2 or 3 spaces between each one...any idea? Any way is a detail. Thank you
Me same problem...
I found out the culprit already. It happens when we didn't put the '/' at the second syntax to "close" each item per option. It quickly resolved when I added it.
Hello, i just found you channel and it is awesome. On this particular video i am trying to do the exact same thing that you did except instead of adding a property to "createTemplateFromFile" i would like to do it to "createHtmlOutputFromFile" as an input box with a drop down. but i keep getting the error "Object does not allow properties to be added or changed." do you know why?
that's the difference between the 2. createHtmlOutputFromFile is a static HTML output, you can't modify it.
@@ExcelGoogleSheets Thank you so much. I am trying to do this exact same thing and I have been struggling with this for hours. I can't thank you enough. But thank you. This is arguably the best channel on youtube.
I have a HTML form and i want to include edit functionality. Im using app script, is there a video with this?
Coming up next month.
Your videos are perfect and the way of explanation is awesome. However , for some reason I am receiving errors such as in this video at 15:00 i am getting as value in the field dropdown. Can you please help.
For the dropdowns. If we have a spreadsheet with more than one column of values the code in this video will grab the last row of the longest column for the dropdowns leaving smaller dropdowns with a bunch of empty cells. How do you fix that so you just get the last row of the column and not the last row of the longest column?
czcams.com/video/zWcNzLbJpb0/video.html
@@ExcelGoogleSheets Hmmm. I watched that video but the still seems to get the last row of the longest column. I want the last row of each column. Perhaps I need to give that a watch again.
The video covers everything you need and then some.
how can we hide the select or add a placeholder value in place, in case the sheet column data values are not there?
The best tutorial ever thanks for all your efforts
I would like to know how to create a web app here HTML with google apps script and google sheet drop down dynamically dependent with multiple levels like 4 or 5 levels you already did it before with google sheet and google apps script.|
For example, Get options from google sheet to drop down in web app then submitted to returned it back to google sheet to store and will be a dynamic drop-down menu with 4 or 5 level
czcams.com/video/QFW2o1GA60k/video.html
@@ExcelGoogleSheets really appreciate your highly support
If I need to join for online courses how can I apply I need to improve my because i'm beginner Js and google apps scripts
If you can help me with some tips please
Thanks for this Video, Sir. How do we use Search/Filter Dropdown in the dropdown? I have 100 values in dropdown and I need an option to filter the value in dropdown just by typing
It's in one of the parts if you keep watching. Don't remember in which part exactly at this point.
@@ExcelGoogleSheets Thanks a lot. I will look into it.
How to create Dependent listbox for google apps script Web App?
Thanks
Hi, i am following this cours. It's working for me. But (alway the but!): i have this question. When i make two select boxes in my form (cars & days) and thes boxes are filled from the spreadsheet, worksheet named "options", column A &column B, i can fill in the form and sent the data to the google sheet. So this OK. But what i do not understand is why the webpage - after sending the data to the googlesheet - is not showing. There is just a blank page. I think it has to do something with the rendering of the page, but can anyone give me a clou?
How is called that used inside HTML? I have been searching more information about it's usage but couldn't find anything...
I think they call it skriptlets, but I could be wrong.
I sincerely appreciate your teaching. I am Korean and Not a developer and do not speak English well. I have a question. Is "Dependent Dropdown List" possible in webapps? I tried to solve for a week, but I failed. So I need your help.
Your English seems fine to me. It is possible. You need to use JavaScript array filter method and then clean up dups. This is not easy if you're not a programmer. Maybe I'll do a video on this when I have time.
@@ExcelGoogleSheets Thank you very much. Now Korea is lunch time. I'm very grateful for your reply. And This is how I think without JavaScript. (1) When the value of the first list is changed, it is saved in the Google sheet. (2) Create a second list corresponding to the changed values in the Google Sheet. (3) Change the value of the rendered list by taking the value of the second list. I have not solve (3) yet. However, if the browser is refreshed, the changed value will be reflected. I'm looking forward to the next video. good luck!
I am also looking forward
This is excellent. Can you give sample if i wanted to search a specific column of google sheet (say first column) using just a part of the string ("goog" will also show "google", "googl", "googly", etc) and display in tabular form.
❤👍👍💖❤
May I ask why using this it reflects as text when I run my script?
🙏🏻
Good explanation for single column but what if i want multiple columns
Very interest. How to download the sample sheet?
I'm following along with this and makes sense to me. However, I am getting an error: "SyntaxError: Unexpected token '='" and the tmp.title and tmp.list are showing up as incorrect in the editor.
This is what I have:
function doGet(e) {
//Logger.log(e.parameter);
var tmp = HtmlService.createTemplateFromFile("page");
tmp.title = "Star1 Sign In Form";
tmp.list = ["Test1","Test2"];
return tmp.evaluate();
}
And all I get on the page is an error:. I've been over it several times and cannot locate the syntax error. I did find some missing semicolons, but fixed those and still get an error. Tried google searches but really couldn't find anything useful that I could tell.
I think your error is somewhere else.
You could try this to remove error highlighting
tmp["title"] = "Star1 Sign In Form";
tmp["list"] = ["Test1","Test2"];
So check above and below this function as well as other script files you have.
hi
Found Error "TypeError: list.map is not a function (line 10, file "code") " when refresh
code was type" tmp.list = list.map(Function(r){return r[0];});"
when type tmp.list = list. map was not appeal.
That is so Great. Thank you so much. I need more clarify on this function : var htmlListArray = list.map(function(r){ return '' + r[0] + ''; }) it does not work.
Watch Apps Script Series 2 www.chicagocomputerclasses.com/youtube-learn-google-spreadsheets/
did you check the .getValues(); vs .getValue(); when declaring the list
var list = ws.getRange(1, 1, ws.getRange("A1").getDataRegion().getLastRow(),1).getValue(); doesn't work
var list = ws.getRange(1, 1, ws.getRange("A1").getDataRegion().getLastRow(),1).getValues(); works
We dont understand
I watched your apps script series 2
@@shivlen Thank You, it works
Hi i getting snap error in web app when there is huge data to display in table. ho to resolve it ...?
i want so sent a massage from sheets to whatasaap can we do that??
in code.gs file in line no. 7, variable list is defined as an array and it is taking value from the spreadsheet. In my case i want to take this value from a front end input box using some click event listener. how is this possible... basically user will pass a value from front end, i will pass that value in code.gs and will pass this value in a function which is already defined in code.gs. ultimately the result of function would be displayed on front end.
This is covered in next parts.
@@ExcelGoogleSheets got it.. my web app is about to get done with.. i will create a video explaining how i did it with the help of your tutorials .. thanks a lot for making these videos.. this will be my first tech product which actually will be used by more than 50 sales persons to log orders and raise field requests/complaints.. kind of a scrappy crm.
Excellent. Just wanted to know that how can I convert this in tag (searchable dropdown list). I tried but failed. Your reply will be appreciated.
If you watch the following parts there is an autocomplete example. It's not using datalist, but it gets the same thing done.
@@ExcelGoogleSheets All of a sudden my dropdown does not work. I can tab into it and use down arrow, but if I click on it, the names disappear and the dropdown will not work
I have a question. When I publish a html from gg app script, it gets error not found doGet function. Could u show me how to fix this? Thanks.
doGet function is required to publish a web app.
15:04 The dynamic here is hard to understand!... Because there is just one tag (wich is just part of the HTML). It is outside of the For Loop... Althought, it is interpreted as if the where two tags, thus there are two options in the drop down ... My mind is blowing trying to make sense of that!
I suggest to check out some HTML videos czcams.com/play/PLv9Pf9aNgemuYdHkUa_knXKMYZcW9WEaf.html
Hi, I keep getting
"TypeError: list is not a function, it is object. (line 6, file "Code")"
Unable to proceed. Please help.
use "function" instead of ()=>
@@immanuelsuleiman7550 i dont understand
source code available ?
can i available
How to remove the Google notice header
is the sourcecode available for this tutorial
very helpful tutorial, Thank you so much. i would like to know how to show the list in sidebar in the same way
I tried, but the option shows "" not "1 ,2 ,3".
Which step was missed during the function setting?
function showUi() {
let template = HtmlService.createTemplateFromFile("page");
let html = template.evaluate();
template .list = [1,2,3];
SpreadsheetApp.getUi().showSidebar(html);
}
html part
let template = HtmlService.createTemplateFromFile("page");
template .list = [1,2,3];
let html = template.evaluate();
SpreadsheetApp.getUi().showSidebar(html);
@@ExcelGoogleSheets Thank you very much for your effort. .This channel always can find what i need. Thank you
how can I update or modify data from web?