Google Sheets | Data Entry using HTML Form 🔥🔥

[@tech-lever]

const DATA_ENTRY_SHEET_NAME = "Sheet1"; const TIME_STAMP_COLUMN_NAME = "Timestamp"; var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(DATA_ENTRY_SHEET_NAME); const doPost = (request = {}) => { const { postData: { contents, type } = {} } = request; var data = parseFormData(contents); appendToGoogleSheet(data); return ContentService.createTextOutput(contents).setMimeType(ContentService.MimeType.JSON); }; function parseFormData(postData) { var data = []; var parameters = postData.split('&'); for (var i = 0; i < parameters.length; i++) { var keyValue = parameters[i].split('='); data[keyValue[0]] = decodeURIComponent(keyValue[1]); } return data; } function appendToGoogleSheet(data) { if(TIME_STAMP_COLUMN_NAME !==""){ data[TIME_STAMP_COLUMN_NAME]=new Date(); } var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; var rowData = headers.map(headerFld => data[headerFld]); sheet.appendRow(rowData); }

[@ShayT9019]

Very good tutorial, fully understandable, short, perfect video and audio quality.

[@nagarajdoddamani697]

it's good. thank you

[@ericgroch7989]

This was the perfect tutorial. Perfect audio and easy to understand, clear instructions, short enough for my attention span! Thank you so much!!!

[@tech-lever]

Glad it helped!

[@DiomarCosta-p1u]

I LOVE YOUUUUU

[@mawimainternacionales6877]

Incredible teaching ability. Congratulations!!

[@tech-lever]

Glad you think so!

[@lashmaker8895]

Yes, it would be quite useful to the part 2 = populating this fotm from a Google sheet directly.

[@ortamatama5479]

simple and to the point, thank you it worked!! (dec 2023)

[@tech-lever]

Thank you 🙏

[@lance768]

is there a way to get the input in the columns?? (A1 is the question and A2 is the input) pls help P.S. Love your Video You earned yourself a new Subscriber

[@eealliance5997]

Why is it when the web app was deployed, it was set as anyone? Is it that html data will not populate the sheet if it isnt set to "anyone", why cant it be set to "only me", since it is only serving as a backend to receive the data??

[@tech-lever]

Yes you can set as me also. Depends on your use cases. When you want it to be public then set it as anyone.

[@ArvieAverion]

how can i use the form using multiple devices via web browser?

[@danielarthur2329]

I think is better to use a server-side approach than this approach. This method exposes the data received to anyone who gets the link in the front-end, because it's set to “Anyone”.

[@tech-lever]

For personal usage you can set it to myself.

[@danielarthur2329]

@@tech-lever If you set to myself, how then do people fill the form? The whole idea is to use a custom form to send data to Google sheet. It would be better to use the custom HTML form -> Google form -> Google sheet post method, by grabbing the name attributes of the Google form input elements (which is now hidden by Google and difficult to find).

[@awali016]

​@@danielarthur2329 when you deploy it u have to use environment variables in your deployment service provider.

[@MarieFaubert]

Thank you so much !

[@tech-lever]

I am glad you liked it ☺️

[@MohamedAbdo-md4fg]

Awesome

[@MathWords-wp4mi]

😀Excellent!!!

[@tech-lever]

Glad you liked it!

[@ep4r4]

2:42:49 Error TypeError: Cannot read properties of undefined (reading 'split') parseFormData @ Code.gs:16 doPost @ Code.gs:9

[@zerosvale3732]

same as me'

[@mmmanzoor]

Very impressive one I need to make a two forms first the data will be populated from one Google sheet and select any of the class and then this one which you have put it there so I need when someone has selected the glass from the first page of the form then how to populate the data of a name field and the father name your last name field automatically in the second page of the form so that the user cannot manipulate their names and only can enter the data of the max it will be helpful if you give me the guidance

[@dapcubalangir5011]

Awesome . But how can I edit to my entered data later ? And is it a multi user form which can be used at a time by more than 100 people ?

[@tech-lever]

Plzz check my othere videos title CRUD web app.

[@ashutoshtripathi-ze6hn]

Great help 👍

[@tech-lever]

Glad it helped

[@techshopdz3477]

Beautiful work thank you very much

[@tech-lever]

Thank you very much

[@user-dj5fx6od5b]

Can you please add code for dropdown in HTML

[@dimitrisvoudouris]

many congratulations on your channel

[@tech-lever]

Thank you so much! This really motivates me😊

[@FANITUBE-kn8ii]

Love asnd salute you always. You give an awesome Solutions. Whynot you build and Tracking system ofan item. And also student fee management system using Googlesheet and app script and beautiful web interface???

[@tech-lever]

Thanks for the idea! Noted👍. Could you provide more details so that I can actually convert it into video in future.

[@FANITUBE-kn8ii]

@tech-lever student Fee Management system: it will work as By using html Form i can enter student details one by one or upload bulk record by using csv file. Then by using html file i can see pending fee details of students and i can generate their fee vouchers for one student or for whole class. The vouchers i can download as pdf and also if i wanna send them to each students parents by using their email addresses which is available in student records. When i receive fee submissions vouchers i will enter them into one by one or choosing whole class or i can enter by using voucher id. I should have updated records related to fee pending or submitted successfully. An smart Dashboard with details will be most helpful. This whole system will work by three persons one is admin who can view report status and my accountant who will generate fee challans or collect it and third person my admin officer who will add , modify or disable any student with his record. Male sure i have facilities to wave of whole fee of a student or can give a discount too.all system will start by proper logins of admin amd accountant and administration officer.

[@a22abcd]

thans alot😊

[@pouyapj1jan122]

❤❤❤❤❤❤❤❤❤

[@tech-lever]

Thank you very much.

[@pooz78]

lovely you content :) thank you so much

[@user-cc4jm1nw6o]

I love your videos. Clear explanation and up to the point. Is there a way to add a signature pad, capture the signature and add it as a new column to the google sheet for each user? Also would it be possible to use "select" in html for multiple and single options and transfer the data for it to a new column in the google sheet as well?

[@user-rd4gf7qy8q]

What about multi sheets? Logically, in the HTML input form there is an option for the sheet destination to be filled in. Hopefully there is a solution. Thank you 🙏

[@ElenaBG3]

This is so good

[@tech-lever]

Thank you very much

[@jawwadumar2100]

you earned a subscriber :)

[@tech-lever]

Thanks a lot🙃

[@KarimKouadria]

Thanks for your video, really impressive. Is there a way to select the sheet where the data will be posted. I've appended a value "sheetname" in the form on it submission, however, i could get it running. Is there any tips that you can share with us ? Thanks again.

[@tech-lever]

Yes definitely. Please watch it again. The instructions are give. You can configure the data entry sheet.

[@angelchiro]

hello, excellent tutorial, thank you, is there a way to avoid a duplicate data entry?

[@tech-lever]

Not is this version. Maybe you can watch my other CRUD videos. Browse my channel page.

[@atharkhanpk]

Is it possible to resize the text fields to fit two or three fields in one row on HTML form? Can you share your email address for further guidelines?

[@EcoDev-Solutions]

How wonderful and the best thing is that it can be used on a mobile. Do you have videos of how you do it from the beginning? I am from Venezuela and I am learning to use this great tool. My respects!

[@tech-lever]

I will do that as a part of its extension and show the process of how to use it on a mobile device. I hope I understood your question correctly. Thank you very much for taking interest in my video. 😊

[@EcoDev-Solutions]

@@tech-lever Hello Thanks for answering. Sorry if I don't make myself understood. I don't speak English and I use Google Translate. The idea is that you can see the creation of this project from start to finish. Thank you so much.

[@tech-lever]

@@EcoDev-Solutions Sorry but I am unable to do full length video. It's a skill that I currently. Doing project while recording my voiceover is currently difficult for me because of my bad english.

[@EcoDev-Solutions]

@@tech-lever It's a shame because it is an excellent project that is worth making known.

[@juneldelacuadra5973]

Thank you!

[@CaptainAnime-fw2mj]

If i type any columns Manually In Z4 and Than next data comes in Z5. Wole row skip How to solv this ?

[@christopherangeles4916]

Much better if use the google form instead og html and etc.

[@ghassanibrahim9008]

HOW CAN I GET THE GOOGLE SCRIPT CODE?

[@cubesolver543]

can you please share how you stored the data of radio button i am getting problem storing radio button data

[@tech-lever]

I will try to do that. Please check if you have properly set the name attributes. Check MDN docs for Radio buttons.

[@cubesolver543]

@@tech-lever sorry for this I Didn't update the URL in google sheet app script which was not allowing radio button data to get stored once again sorry

[@jaywillcee4364]

Thank you. Is there a tutorial on how to populate the form from Google Sheet?

[@tech-lever]

Yes. I have got two such videos. Please browse in the home page.

[@eealliance5997]

What would be the use case? I'm just wondering why a form would have to be populated from a spreadsheet which is serving as a database.

[@willnolan1]

Trying to create a roll call for a class. Multiple people taking the roll. Any ideas on how to host the html easily for this to input into the form?

[@edwinmuiyuro6496]

Thanks it worked

[@tech-lever]

Glad it helped😊

[@EDIKS-el4us]

Really nice, Thank you. can we read the data submitted?

[@FireheadLazzo]

If you open up your developer console, you should be able to see the POST request in the Network tab

[@Kids-tunn]

Sir please tell me highlight second highest number formula in condition format

[@ikankembung6839]

it doesnt work when i add vlookup function in the sheet. it will fill the cell where it doesn't have the function in it. is there any way to prevent this?

[@justthoughts8254]

Hey, thanks for this. When using mobile phone to access to html, what link should I use? I want to pass along the form to the team member so that they can easily input.

[@tech-lever]

You can share the html file directly via WhatsApp or Google drive. When they double click on it after downloading, it will open in the mobile browser. Just to let you know I am making more robust web app based interface for data entry. Please wait for few days. I would recommend enable bell notification so that you get notified. Thanks 🙏 for showing interest in my videos.

[@justthoughts8254]

@@tech-leverThank you

[@osanbrazil999]

Cancel button doesn't work. It submits the form data just as the submit button does.

[@osanbrazil999]

Alright, I fixed it. Added an event listener for the CANCEL button for a "click" event and function inside to reset the form.

[@osanbrazil999]

I'm getting a CORS error. How do I set the Access-Control-Allow-Origin Header?

[@osanbrazil999]

Well, I copied the script from your sheet and it worked. I wonder what I typed wrong, because now I already deleted the old code that I typed in from the video image, LOL.

[@mik8639]

Thanks, when i sign up, data entered sheet. But no success message. Why

[@ebrimchamdemba7367]

Data to store offline when u got internet u can sync them all online?

[@emmanueltembo4968]

how do i edit this with my own spread shit in my google account as in linking the spread shhet link in the code??

[@jeffb1886]

Thanks so much for this! I got it to work even with some changes I needed. But I'm definitely a newbie. What are the steps needed to take the finished HTML file and load it onto a mobile device so I shows up as pretty as on my desktop??? If I send it to my phone and launch as a test, its tiny and B/W...no formatting. Also, can you point me to one of your videos (if you have one) that shows how to link one of the HTML form fields to a separate Sheet tab with a LONG list of items in a column as a pulldown in the app. I'm not sure what the code is and if it belongs in the HTML file, the Google apps script Code add on, or both.

[@tech-lever]

Send the finished html file to your phone using WhatsApp may be. Then open the file using chrome.

[@FireheadLazzo]

@@tech-lever Is that not a bit silly though? Why bundle the HTML file with the App Script if it can't be used without downloading the file?

[@tech-lever]

@@FireheadLazzo Sorry for your incovenience. Its just for easy look up. You should watch my other videos also. I have got several videos of forms.

[@FireheadLazzo]

@@tech-lever Okay, thank you. I will learn more

[@Sydyee]

Here Im getting the App script Error.!

[@orkhanhuseynov2134]

hello, thank you for your great content! i appreciate it. I faced a problem. I have a google sheet which already has formulas and some special vaules that should be there. and i want to just fill the blank columns on this rows. But i cannot manage code for that on app script. Lets say on AR13 cell i have a invoice number that it shouldnt be change, but AR14 cell is empty. when i add new data, it adds to row 14 because row 13 (AR13) has value but Row A,B,C,D are empty and i want add data from form until N column. I would be really happy if you would help me solve this problem.

[@ikankembung6839]

i faced this problem too..

[@user-cs9kl3rm4z]

Great video. Question, what if they need to attach a file? How to attach files when submitting?

[@tech-lever]

In this version it's not possible. But I will post another video with File attachments also. Please wait for few days or so

[@ep4r4]

An error occurred while submitting the form.

[@user-rd9iu1ly3t]

After following the instruction completely, the data does not populate back to the Google sheet, what do I need to do? Everything worked all the way up to getting a "data submitted successfully" response but the datas did not transfer into the Google Sheets doc.

[@tech-lever]

Did you first tested with my shared spreadsheet? Remember you need to deploy it, get the url, and then replace the url in the HTML form as well.

[@user-sq7oq2ux2d]

hello first i want to say thank you for your work!! can you please tell me how can i display time and date of the submission

[@tech-lever]

I have updated the code. Now you will be able to see date and time of Submission. Please Subscribe and share this channel if you want to support my work. 😊

[@HayrullahEsadEsen]

thank you! what if we use google forms instead this hard work? isn't it the same thing? I'm asking to understand the difference

[@tech-lever]

You can layout many form controls in whatever manner you like. You can have have 2 or 3 or 4 controls per row. Most importantly you can configure dependent dropdowns here also. However, I have not provided support of dependent dropdowns here. You can see my other videos for that. This tutorial just introduces you the basic workflows. For simple forms Google form should be the preferred choice. But if you want to build really complex form, with different phases or steps, and whose dropdowns are populated using the spreadsheet content and that too are dynamic then you will have to take help of HTML and Javascript or other front end tooling. Please watch my other CRUD videos to better understand the benefits of custom building your forms. If you can put more effort into this, it can do a lot more than google form can do. Because you will be in full control of the logic. Obviously you will have to bring in some HTML and Java script and it would be good for learning also.

[@HayrullahEsadEsen]

omg

[@Samu-gz3qj]

Is the Google form cannot handle it?

[@gururaghavendran7794]

Can we do it for election? School election. Head boy, head girl, school caption vice captain, sports caption. Etc. The students will give one picture for them. Can you make one video for that.

[@tech-lever]

Google Form will be best suited for your purpose.

[@esatkurtul]

hi thats great. I want to add a popup and show the data we send in the popup, how can I do it? as a table

[@tech-lever]

For simple popup you can use alert method. But complete CRUD style video will come soon. In that you can view the data in a table and perform update and delete as well.

[@JosephPokera]

Can this be use Offline and then sync to Google Spreadsheet once there is an Internet Connectivity?

[@tech-lever]

Sadly No😞

[@Peter-ib3es]

Can you assign an email alert to 2 users everytime the form is filled out with a snap shot of the form?

[@tech-lever]

No. You may contact me in case you require any customized version.

[@edgarignite2601]

Can you explain this case to me? I changed the name of my google sheets page to and replaced that name in the variable DATA_ENTRY_SHEET_NAME but the log I get is "An error occurred while submitting the form". Meanwhile, with the variable name of DATA_ENTRY_SHEET_NAME being "Sheet1", everything works normally.

[@tech-lever]

I will take a look at it.

[@guehanefoltran4435]

how can I add a logo to the green banner please? can I change the colors of the green banner too?

[@tech-lever]

Yes possible. You will have to edit the HTML of that page. You can refer Buefy docs for that.

[@edhahaz]

Interesting approach, but can't you put this into the sidebar ? I know it can take HTML+css+js just fine, so is this fixing any sort of limitation ?

[@tech-lever]

I have already done that in a separate video. Watch one of my videos titled Sidebar.

[@panpesek8036]

will this work with google docs?

[@timbrowntechlife]

I get an error on the sheet link sayingWe're sorry. You can't access this item because it is in violation of our Terms of Service.

[@tech-lever]

I will look at the issue. I did receive an email from Google about the violation.

[@thilakeshraj.a1093]

When i click run the script, it asks permission, but it is not going further says that the app is blocked. i also changed the initialsetup to dopost still facing the issue. caould you answer me

[@tech-lever]

Have you first made a copy of the Spreadsheet and then followed all the instructions?

[@user-rt4hg6ji4g]

So forgive my ignorance, but its my first time. I copied everything in the script editor, but when I try to run the script, i'm getting an error! How would i determine...what line I may have deleted thats causing the error?

[@tech-lever]

Start by making e a copy of the Spreadsheet from the link given in the description. Then do the deployment as per the instructions given. Hope it helps.

[@pooz78]

How to add upload file can be?

[@tech-lever]

You can watch my other videos for file uploading

[@biankazieba5372]

while trying to Deploy the Google Sheet, I'm getting the "This app is blocked" without the option to deploy it anyway... do you know why?

[@tech-lever]

Did you first make a copy of the Spreadsheet? Then you need to deploy it as per the instructions.

[@tracybuford9243]

DATA

[@user-ew1mx3uc2y]

Can any one help me to solve CORS problem to regarding this project

[@tech-lever]

Please start by making copy of the Spreadsheet. And in the access type select anyone. Hope it helps.

[@user-ew1mx3uc2y]

@@tech-lever thank you for your reply, access type is selected as anyone and also in fetch API header Access-Control-Allow-Origin set to '*' , but not resolved

[@tech-lever]

@@user-ew1mx3uc2y I tested the attached HTML form by opening it with chrome. And it worked. And also CORS setting is not required here. Are you using NEXT JS? Maybe you are trying to integrate it with other project. Or you are deploying the HTML form somewhere. Please send me screen recording. Take my email id from About page.

[@gkpgeo]

Can we retrieve data from sheet to html

[@tech-lever]

Yes. That video will come in few days.

[@rachidabourayyan5465]

I need this video please@@tech-lever

[@sherwinalfonso6846]

IS THIS ACTUALLY SAFE? YOU'RE GIVING PERMISSION TO ANYONE?

[@tech-lever]

This is suited when you want the form to be public one. If you want restrictions then refer my other video titled CRUD application. That can be deployed with restrictions and is even more powerful.

[@henry_343]

yes, is safe

[@user-rb6nv3qk8k]

Hey, its very nice and it was my first try and I am having an Error TypeError: Cannot read properties of undefined (reading 'split') parseFormData @ Code.gs:14 doPost @ Code.gs:7 please help me

[@user-rb6nv3qk8k]

Script function not found: doGet also this error i am getting

[@tech-lever]

Make sure you have deployed the script correctly and then pasted the deployed url in the html file also. You can first try using my HTML file and Spreadsheet. And then convert it to your own. Give it one more try. Then you can reach out me. You can find my email address in About section.

[@collab.stream]

That was very helpful, one question how do you add required to a text area? tried con class, didn't work

[@tech-lever]

You need to add 'required' keyword within the input tag. Search for the textarea input and add required. That's it. I would recommend to refer to form section of the Buefy docs.

[@collab.stream]

@@tech-lever as in: type="text" , -> type="text required"?

[@tech-lever]

@@collab.stream No. type="textarea" required

[@Dzchallenge]

It's work

[@auDelaDemaJusteMesure]

hello, i get this => Error ReferenceError: postData is not defined parseFormData @ Code.gs:16 doPost @ Code.gs:8 !!!

[@tech-lever]

I have updated the link. First make sure if it's working with the file links that I have attached in the description. First you can download the HTML file and open it in the browser. Then open the attached Spreadsheet also to confirm if the entry is happening. Then you can modify it as per the instructions. Just to let you know, I have checked it myself and it's working. Please watch the video again and try.

[@auDelaDemaJusteMesure]

@@tech-lever yes, it works. But how do I link it to my own account?

[@auDelaDemaJusteMesure]

Thank you. Now it works

[@tech-lever]

If you liked it please subscribe. It will support a lot.

[@auDelaDemaJusteMesure]

@@tech-lever how to send image ?