Google Sheets UserForm - Send Data from Form to Spreadsheet
Вставка
- Опубліковано 21 кві 2020
- Learn how to connect the userform to Google Sheets spreadsheet and use Bootstrap as a user interface.
This tutorial is Part 2 from Google Sheets Userform - Level 2 series
• Google Sheets Userform...
#userform #googlesheets #bootstrap
Thank you, Thank you, Thank you. I am waiting for more videos of this series. I am sure, this shall add immense value for sheets in my organization. Thank you
Estoy aprendiendo con mucha dificultad por el tema y el idioma.... Estos videos son de mucho valor para mi. Gracias por subirlos.
Thanks for the content. Clear and for some reason it always drops in when that is the exact problem I might be having.
Great!!! Can't wait for the other videos. . .
Cant wait for this...Amazing...
Your video tutorial is very helpful and clearly explaination.
Great n usefull content...
Thanks bro, need this content more
Terrific! Thanks so much !
Friendly tip: using hyphenated variables prevents double-clicking on entire variable name. I prefer underscore (so item_name instead of item-name). Great series.
It's hard to break old habits :)
@@ExcelGoogleSheets oh I know. I still sometimes catch myself typing DIM instead of VAR 🤦🏻♂️.
Thanks bro, well explained and clarified, awaiting for rest of the topics.
Thanks! Check the playlist in video description.
I will check. Thank You👍
More power!! keep it up!!
Biting my nails waiting for the rest of the series.
Você é o cara, man! =D
Awesome content.
Amazing! Thanks
Danke sehr, Merci beaucoup, Thank you. Lord this is fascinating
How to add a unique number automatically in the first column for each row like the automated date you created? please
you are the man..
Thanks. Good video. Please show how we can update data we have send to the Google sheet via the user form. Thanks
I'll have to double check but I think the AppScript editor has a lot of the same key binds as VS Code. You should be able to alt + shift + F to format the entire document, indentation and all!
Yea, they updated the editor since.
Excellent tutorial thanks.
Glad you enjoyed it!
I did everything the same way but button click is not working. Also the html code from had some changes in the script part. I don't know why it is not working, can you help?
I got the same Problem, did you found a way to solve this Problem?
Thank you, it's very good series. However I have not any idea how to create email with clickable button and function which can modify value in spreadsheet.
thank you
Thank u for those wonderfull tuto
Glad you like them!
@@ExcelGoogleSheets i send u a mail could u check please and thank u very much.
Very helpful serie ! Thanks again
I come back to this video because i need to add an "radio" input to the form but there is a different ID for each option so i don't know how to process this with the JS
Would be gratefull if someone can help !
ua-cam.com/video/65G2_qJYjC0/v-deo.html
It is really amazing, but if we want to make an interface form then we need to have reset, delete, search, update...etc as well as sometimes we may need to have drop-down list in the interface form. I really love ur videos and if you can make these things that would be awesome. Thanks in advance.
I believe all of that is covered in Userform Level 2 and Userform Level 3 series that are being edited and uploaded now.
@@ExcelGoogleSheets you are awesome bro thnx a lot
Hi, Great content. Thanks for this. How can I have access to rest of the 3 videos in the series?
The videos are being edited and uploaded now. It will take some time until all of them are available.
one year after. But is there a way to create a load of info to be able to save it as directory? so from a google form we collect name, email, phone. and get the data to paste into a directory.
Hi whatever you did its goes well for me but what i want here to do is i want to run this code in every sheet of the workbook in same range so can you help me please to modify the code
Good day sir! I have a question sir. What if i wanted this form to automticatically open when i open the google sheet file would it be possible? I tried it by not puting the createMenu & onOpen function but it didn't work. What should i do? Thanks in advance
how to correct indentation ? did you use shift+ tab ?
Gréât video. How would one go about having a sidebar entry form in which you could add lines of items before possibly validating the form . I have a spreadsheet for sales, with one sale having potentially several items (not more than 5) : the form would basically need date and sale number as fixed fiels and then have one or more items that would each be sent onto a separate line of the « saleitems » sheet?
You are a god :)
Good job,
I have a special question and I am not sure which of your video is the right one:
I want to create a chart and I have different sheets for January, February (...)
Every sheet has the same layout and in the Final sheet I want to get all the data from there but I don't want to copy all of them. Is it possible to use a formula to change just the sheet name?
e.g.
January!A2
January!A3
and then I need for every other sheet the same
February!A2
February!A3
(...)
I didn't find a solution yet to change sheet reference in the formula. Thank you in advance!
The solution is here ua-cam.com/video/Vuh7lh2mhQk/v-deo.html
Hi, wonderful tutorial explained very well, congratulations!!!
About showModalDialog or showSidebar I have a question that I can’t solve: how suspend the server-side script while the showModalDialog or showSidebar is open?
Basically I would like the script resumes after the user dismisses the dialog.
Sorry for my bad English I hope I made myself clear.
Thanks bye
Gabriele
I don't think this is possible.
@@ExcelGoogleSheets 😔
Thanks anyway! And congratulations on your work again!
Thanks for this amazing video. I did and it works. But when I create with many data in column and i have followed step by step as seen in video, the data cannot add from form to spreadsheet. Please help me.
I am not able to get the button to work. Has there been update since this video was recorded that changed how we would run this code successfully?
Is there any way to limit the userform to a particular tab? Or at least make the userform sidebar much larger? My idea was to limit the userform to the first tab in a workbook, where all you can see is the userform.
You can load it as a dialog box, but you can't limit it to a particular tab. It's either open or closed.
I want to set a user have permission to use form but can't edit on worksheet. Any way to do that?
Does someone know how to make input value as text in this google sheet form?
How to add more items to user menu with different html files
Does the form work for mobile input?
Great content! Can I download the script and html file somewhere? I got stuck in the end and the button didn't work. Thanks!
Solved ..used "clicked" instead of "click" in the eventhandler. =/
I did everything, but my data entered cannot enter the spreadsheet. I need help!
Same issue. I went over the video and recreated twice but no luck. Works up till the last step.
I followed along and it worked.
Having Same Issue
same issue ..all good until the last step
me too. same issue here
Thank you for the video. I did exactly the same code as the video, everything works almost but at the end when we get the input in the texts Item and Qty and click Add button, doesn't insert any data to the Google sheet. I tried checking the UA-cam video Google Apps Script & Web App Debugging of yours to debug but when I am trying to publish the code as "Deploy as web app", the web app gives the error - "Script function not found: doGet". Please help.
You don't need to deploy as web app. Just right click on the sidebar and inspect.
google.script.run.doSomething(values) is not working with sidebar I am using new code editor. Showing type unknown and status failed in executions. Please Suggest something....
Hi. I want to add a droplist like this on a hmtl form and send results to another google sheet. Can i use this method? This example save in the same sheet. Thank you for all the assistance.
Just change the range in the code.
Can I add two item? Replace qty by item 2?
that is great but i want to know more about how to make modify & delete button working as well
Watch Level 3 Series.
@@ExcelGoogleSheets thanks
Hey, thanks so much for this content, its super helpful. I am having some trouble though. I copied everything exactly as in the video, and re-did it again. Everything works up till the button click. But, I cant get the click to run the script.
Please watch this
ua-cam.com/video/flxj-QB50zo/v-deo.html
@@ExcelGoogleSheets Thanks so much, that video was also incredibly helpful. I found it was an authorization issue. After authorizing it in my default google account, its working perfectly.
@@clairmontmiller6582 Que tal, exactamente en donde distes las autorizaciones, yo aun no logro que corra y esta todo correcto.
I watched the video and used the console. The form works as a standalone web app but not as a side panel. It says that access is denied.
@@clairmontmiller6582 Can you please tell me how to get the missing authorization?
Thanks
Could you also show how to reset the forms after clicking add.....so that they Form is empty after clicking it
It's all coming up in the next parts of these series.
TypeError: Cannot Read properly appendRow off null error message received. Please help
First of all, thank you for the tutorials! really helpful however i have a question.
1.My data is inserted just fine however, when i added arrayformula in the sheet itself , it kinda fucked up the whole sheet and i cant insert data into rows anymore. How do i fix this?
Basically what im tryna achieve is when i input data into the sheet, the data will be calculated in a different collumn, so everytime i input data, it will automatically calculate the data i input into new rows. Thank you
Easy way around this is to use a copy sheet, I have an example here ua-cam.com/video/0v-hQ3EecdE/v-deo.html
You can also use array formulas that don't overflow the range.
@@ExcelGoogleSheets Thank you kind sir,
Although the first solution sounds interesting, i feel like using the second since it fits my project more.
Therefore, how can i use the array formulas that dont overflow the range? is there any tutorial youve made?
TypeError: Cannot read property 'item' of undefined
This error is finding at the end
Please let me know how can i solve it
have the same.. mine it works only with the first example, by specifying the value of the constants. otherwide the last method gives me 'undefined item'
Hello Sir, Please can you tell me How can we send data from spreadsheet to form?
Hello!.
Is it possible to upload an image in the form?.
Thanks so much!!.
Hi guys. I'm just starting with gs. I'm stuck right in the beginning (first running the app ~ 5:05). It shows the sidebar, but it doenst show the "Hello World" beneath. I just copied the starter template from bootstrap like you did. Any tip?
sry I just forgot to save the html file, because I am used to the google auto saving feature
Awesome!
Thank you again sir. Question: Can the user be able to mess with already entered data in the sheet? Is there a way to lock it so they can only enter in from the form?
Yes , use protect range for your sheet
Stuck for 1 month
how i can calculate 2 date from html input ?
I want do checkout date - checkin date input.
please help
I don't know what calculate means.
on ws.appendRow({rowData.item,rowData.qty]); i get typeError cannot read property 'item;
Can you please tell, which video playlist to see first, to understand the code ?
ua-cam.com/video/PsIuSwSj3CM/v-deo.html
Then ua-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Thanks for your tutorials.
is not working properly with Bootstrap.
Is there any other way to achieve it?
What's not working properly? What have you tried?
@@ExcelGoogleSheets ibb.co/Sdsbm1W
My drop-down has around 100 items which is not easy to select. So I chose to use . But when the form loads it also shows all options on top of the form. Without the everything works fine.
After doing a quick search I found that Bootstrap isn't compatible with datalist.
Screenshot doesn't help. What's your HTML?
@@ExcelGoogleSheets Bootstrap isn't compatible with datalist at least on chrome as said in this post. stackoverflow.com/questions/48722834/how-to-use-html-datalist-with-bootstrap
Fixed it using www.w3schools.com/tags/tag_datalist.asp
datalist {
display: none !important;
}
How can we read spreadsheet row into form?
Sorry, but again, when search for "Starter template" in getbootstrap, a different example show up with what appear to be arabic characters. Any advice? Thanks
Dear all, I tried to reproduce the program, but at the point in which I have to write the addrow function and the button function nothing happens. I checked the dubugs, I inspected and I have some warnings.
Here is thaty part of the code. I attach some warnings as well. the id of the button is defined and correct.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Results");
function bottonadd(){
var itemName = document.getElementById("item-name");
var top = document.getElementById("topic");
const aa = itemName.value;
const bb = top.value;
const currentDate = new Date();
ws.appendRow([aa,bb,currentDate]);
}
document.getElementById("main").addEventListener("click",bottonadd);
debuggs:
- Uncaught ReferenceError: SpreadsheetApp is not defined at userCodeAppPanel:2
- Failed to load resource: net::ERR_QUIC_PROTOCOL_ERROR.QUIC_PACKET_WRITE_ERROR
Someone had the same problem and solved?
Please help me. I surrend :(
Thank you
Thanks for your video. As soon as I click the button on the HTML, I receive an error 'ERROR: Authorization is required to perform that action.'. I have checked the authorization and it looks fine. I went here -> myaccount.google.com/permissions and checked the authorization status and it looks fine.
Has access to:
Google Docs
- See, edit, create, and delete your spreadsheets in Google Drive
Additional access
- Display and run third-party web content in prompts and sidebars inside Google applications
Please guide - what am I missing?
i have the same problem did you ever find out how to fix it
How do you tab in multiple rows at the same time? @ 9:22
Select multiple rows and hit tab a couple times
The video was awesome! It worked while using hard codes. But when procedure for get form data it stops working. Debug returned: "Cannot read property 'item' of undefined
addNewRow @ funcs.gs:5" - I checked every character and evereything seems to be exaclty like shown in the video. If someone have a hint I would appreciate. Thank´s.
I thing we both are facing same issue. Showing function type unknown and status failed in executions.
i'm facing the same issue as well. hopefully someone can help on this.
@@OswinTan Log into your browser with same email which you are using to make user form. You need to log in with same account into chrome(if you are using)and Gmail. 👍👍
Below is the script I wrote per your instructions. However I am getting this error message
"HtmlService.createTemplateFormFile is not a function "
//...
function loadForm() {
const htmlForSidebar=HtmlService.createTemplateFormFile("uform");
const htmlOutput=htmlForSidebar.evaluate();
const ui = SpreadsheetApp.getUi();
ui.showSidebar(htmlOutput);
}
...//
Can you help
not createTemplateFormFile
createTemplateFromFile
how to make the form work on mobile?
Your tutorial is great but the menu doesn't appear when I refresh the sheet =(.
I checked every line but I couldn't find anything wrong.
Sometimes having a good night sleep and checking the next day works wonders.
It would be more helpful if you provided the code in a copyable form.
why theres error? TypeError: Cannot read properties of undefined (reading 'item')
i follow all scripts and code, why i cant insert data in the databae, pls help
Hey, I wrote the script same as yours but I am getting the following error while running funcs script file:
"TypeError: Cannot read property 'item' of undefined (line 7, file "funcs")".
The line number and codes are exactly same as yours. Please do help!
Check again, it can't be the same.
@@ExcelGoogleSheets Figured it out. I had to manually change the function to be run on the top bar to anotherFunc(). Thanks.
@@jithinsamuelthomas8578 i have same problem: "TypeError: Cannot read property 'item' of undefined (line 7, file "funcs")". Please do help
@@jithinsamuelthomas8578 hey jithin would you please let me know how exactly you solved the problem
Hi Jithin, I understand that you have resolved it for the "TypeError :Cannot read property 'ítem' of undefined. I am having the same error like yours. Can you please share how you have resolved it ? Thanks a lot.
this is piss me off. I replicate this entirely with no mistakes, and when i run ADD button, do not work. The problem is that Somethimes eventListener do not listen or i do not know. Purley do not work. Stuck after add button and the event listener. That step is not working. ANY IDEAS!? I went over the video and recreated twice but no luck. Works up till the last step. Then Done!
Watch this lo learn how to troubleshoot your code ua-cam.com/video/flxj-QB50zo/v-deo.html
I Get.a ReferenceError " Htmlservice is not defined", what does this mean??
it's case sensitive HtmlService
@@ExcelGoogleSheets thank you very much ...
I'm having problem running the funcs.gs file can you help me with this?
TypeError: Cannot read property 'item' of undefined (line 7, file "func")
function addNewRow(rowData){
const currentDate = new Date();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Results");
ws.appendRow([rowData.item,rowData.qty, currentDate]);
}
I copy all the data you did in uform.html. The form did came up on spreadsheet but when I try to put some data and press Add it doesn't went to my spreadsheet. I'm a newbie on this but I'm very interested on learning it..
The mistake isn't here. Share your html file.
@@ExcelGoogleSheets
Item Name
Quantity Received
Save
function addRecord(){
var item = document.getElementById("item-name");
var qty = document.getElementbyId("qty-received");
var rowData = {item: item.value,qty: qty.value};
google.script.run.addNewRow(rowData);
}
document.getElementById("btn").addEventListner("click",addRecord);
...
addEventListner is misspelled, should be addEventListener
I change it, but still got the same error and can't add the entered data into the spreadsheet.
Don't run the function manually, you'll get an error. You need to use the button in the form.
Code?