Google Sheets Form for Data Entry - Apps Script
Вставка
- Опубліковано 30 чер 2024
- Learn how to create a data entry form in Google Sheets using Apps Script.
00:00 Google Sheets Data Entry Form Demo
02:00 Build Form UI
07:50 Create Records
17:30 Clear Fields
20:25 Build Form Search Functionality
40:25 Edit Existing Data
51:30 Delete Records
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
Support the channel on Patreon / chicagocomputerclasses
Make a one time donation: www.chicagocomputerclasses.co...
#form #googlesheets #script
Google Sheets Web App ua-cam.com/play/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l.html
Google Sheets Sidebar Userform ua-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Google Sheets CRUD Userform ua-cam.com/play/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx.html
please send the code
sir i got right my first level of data thank you very much appreciated your help
unction myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const formWS = ss.getSheetByName("Form")
const settingsWS = ss.getSheetByName("Settings")
const dataWS = ss.getSheetByName("Data")
const idValue = formWS.getRange("B3").getValue()
const fieldRange = ["B8","B10","B12","B14","G8","G10","G12","G14"]
const fieldvalues = fieldRange.map(f => formWS.getRange(f).getValue())
const nextID = SettingsWS.getRange("A2").getValue()
fieldvalues.unshift(nextID)
console.log(fieldvalues)
}
2:41:47 PM Notice Execution started
12:41:50 PM Error
ReferenceError: SettingsWS is not defined
myFunction @ Code.gs:14
I copied everything in your script but run button is not available and it says error.
fieldRange.map is not a function
Thank you thank you thank you!! I've been watching your video for at least two weeks because I work long hours and I watched it over and over and I finally paid the patreon membership and was able to complete our new form. I was doing something wrong some how but I just copied and pasted everything in the end and modified it to our form's needs. lol. Keep up the great work!! Looking forward to your other videos.
Thanks for supporting on Patreon!
I am so glad I found your video, very informative easy to understand and the logic you present behind the scripting very helpful. I watched the video to do this following it step by step and came out with a better understanding that when I started,. I have subscribed to your videos and am excited to learn more
Thank you for the time and effort you put into all your videos, you have helped me impress many of my coworkers
Appreciate that!
I love when you "make mistakes" since we then get to see how you fix them! I have been able to automate quite a few spreadsheets and workbooks thanks to your lessons!!!!
Aprendo muito com seus vídeos, muito obrigado por compartilhar seus conhecimentos de forma brilhante e didática. Continue assim.
thank you so much for this video in particular. With this and a few rabbit hole references to chase down the other functionality I need, I've been able to get a new RMA system up and running in three days with nothing but a spreadsheet and some fancy JavaScript, even importing the old data from the previous system on a dying XP box. I appreciate this.
👍
Just extraordinary explanation. You explained everything according to necessity. First problem then the solution.❤❤❤
Your videos are the greatest, always so insightful. Thank you
Thanks!
Such an amazing video. Thanks a lot for sharing your knowledge!
Always a pleasure to watch your tut´s. Espacially the ones from Apps Script. Thank you, sir!
Thank You!
@@ExcelGoogleSheets ยินดีต้อนรับยังคง
Thank you. I used this to right my first Script. Works great
Excellent!
Your series of videos are extremely useful. They are so simple and properly explained that the tips given by you can be used at other places.
Questions:
How can the user jump from one field to next without much hastle or using tab.
How do you ensure that entry to a field is completed before a script is executed.
@How can the user jump from one field to next without much hastle or using tab.
Don't really have a good solution for this.
@How do you ensure that entry to a field is completed before a script is executed.
You would need to validate everything in the script itself before actually making any changes to the spreadsheet.
@@ExcelGoogleSheets what if you protect unused cells and disable clicking on same, i think this will jump protected cells and go directly to enabled once
This was AWESOME!!!!! THANK YOU SO MUCH!!! You have made my day!!!!
wow... you answered a ton of my questions all in this one video... wow... thanks
I am really grateful for this video . I could apply all these tips in a new form in my job. Thank you
👍
I am doing something similar, but in a much more complicated way, with arrays. This is simple and clean code, thank you very much.
:)
Great video, as always.
I wish we had the same functionality as Excel, when you protect one cell and set it in a way that the user cannot select it. This way the user could navigate only through the fields of the form. I've built great forms in Excel like this. When I try to do the same in Google Sheets, it just looks terrible...
same here too
This was really helpful! Thank you!
WOW! This is fantastic. Thank you!
Exactly what I was looking for, Thank you man
:)
This was an amazing video, if i could like this a thousand times i definitely would
:)
Very informative, wish you included a wa of creating PDF's of the data recorded to serve as invoicing system.
Hi, thank you for all your tutorials; I´ve started coding with your help, still a beginner but I´m so thankful for all your guidance. I have a question: will this entry form be something that any user that the google sheets has been shared with can use?
Also, do you have any tutorials on how to automate getting the last row from a database to fill a google docs?
Super helpful, thank you!! 🙏
Very helpful! Thank you!!
Wow it's Great Tutorial. Thank You So much
Thanks for your tutorials! Its perfect! 🍺👍🙏
Glad you like it!
Dude I had to stop watching, you're really good with how you explain!
Great Video wonderful. More such type of real life example videos especially on google sheet.Thanks Sir
Great video, as always. Please, make more tut´s about Apps Script!
You are doing very great work bro.
love from india.
i like your all videos.
Thank you 😀
Great job!!
Awesome!! TQVM!
Very Helpful, Thank you
Thank you so much! You're the best
This is great! Thank you :)
Thank you for sharing. I learned so much🥰.
:)
You are my hero in google scripts
:)
Your tutorial is awesome! I follow this guide and adjusted according to my needs. It works like a charm. For the onEdit(), I have to make it an installable trigger instead of simple trigger in order to make openbyID() to work. BTW, how can 2 or 3 users use the same Page to enter record at the same time? Make 3 duplicated data entry pages and each of them use different page? will it work? Thank you so much for your great work
This will help small-time businesses that don't have enough budget for a system.
Or people like me in a large business looking to simplify their job. Honestly at this point though I’ve gotten good enough at manual entry it might not be too much faster
Iam Almost a Layman in scripts, Buy Enjoyed what we can do with them , THANKS A LOT !
Awesome!
Many thanks!
:)
Great tutorials, thank you....
Can you please make a video on How script and set triggers work in a copied spreadsheet
excellent !! thank you very much!
thanks for your informative video
This tutorial was super useful. Thank you very much for sharing. Is there any chance that you could advise how can we adjust clearing content while leaving formulas in certain cells? It clears everything including formulas..
Excellent and helpful video. Thanks!
Hi! Thank you for all of your amazing videos. With your help I've been able to build a system for keeping track of my entire brewing schedule!
Now I am looking for a solution to automate and simplify the inpu of data in that same system and this is almost it!
Instead of doing a form and populating the data with the push of a Save button, is there a way of doing it with an onEdit function? I am thinking of having a row for inputs at the top of a sheet and then when the last cell in the input-row is edited it sends all the data to another sheet and clears the cells used for the input. Maybe even to send the cursor back to the first cell for a new set of data inputs?
If the onEdit triggers on the clear function that might set of a loop, but if it doesn't trigger this would be perfect for my system since I am trying to erase the need to use the mouse to click the button and at the same time speed up the inputs.
I know this is a lot to ask from someone who has no obligation to help a stranger more than he has allready done by uploading the plethora of videos you've uploaded in the last couple of years but I thought it was worth a shot. And if nothing else I also got to thank you for your hard work!
Best regards!
Thanks so much for your videos, I've seen them before and they are very concise. I've subscribed. I built this as described and modified to my needs works great!. One question I would like to have the deleted items go to a record ("deleted"). tab. I would like when the delete button is activated to have "CopyTo" function activate and send a copy to the "deleted" tab. What script do I need to add to the deleteRecord function to make this possible.
this is awesome!
Thank You!
Thanks a lot, very useful for me
Glad to hear that
Hi there! Thanks for the useful videos! Question: what if we have more than 4 cells to read? A column of 50 for example. And we need to store that data in a row (transpose)?
Thank you sir, i applied this to a database with 90 data points per row divided in 9 categories and searchable by each. but without your example i would have struggles allot longer
Verry helpfull for beginer like me, thanks for share
😊
Thank you very much, I'm trying to incorporate this form in my spreadsheet, but I don't have the ID column in the data, can I use a second column with formula to create some sort of id for each row or maybe I can use the same search column as ID for the saveRecord? thank you
Thank you very much
Great....thanks
I learned so much from you. Thank you. I am building a production output database and this is exactly what i need. Where can i reach this file to make a copy?
Thanks so much for this great tutorial. I have a data entry form with several cells. When I use the cost getRange = [""], the command enters the next line due to the several cells in the data entry form. Which command can I use to make the command run as one line without breaking? Thanks
This was awesome! Using it to create an inventory of my home bar and it is working great! Quick question: I have one dropdown on the Form tab called "Bottle Status" with options of Unopened, Opened, and Empty. Is there a way when "Empty" is selected the data can be moved from the Inventory tab to a "Past Bottles" tab? Trying to keep track of all of my previous bottles, but don't want them on my Inventory tab once I finish them.
I found a couple of examples, but they use onEdit which I cannot figure out how to do since this script already uses onEdit. Hope that makes sense!
Great video again. Anyway to do a form approval process from sheet?
Awesome 👍
Thanks 🤗
This is wonderful learning from the video. Is it possible to download the script and try myself?
Great instructions. I have a question..... How do you amend the Search Function to allow for multiple responses, getting the Form to move from each response to the next? I am trying to use this for a database, for inventory, where it is possible that the search (text box with 80 characters) will return multiple records. I need a way to move from one response to the next.
Thank you so much for your great tutorials. May I know how can my data entry be converted to all capitals when it enters the database? Thanks in advance, keep up the great work.
Hi there! Thank you so much for this tutorial! Is there a way to also add a picture to the form?
Nice tutorial, learned a lot from this (script wise) and made it work for my own purpose 😀
However I've got 1 question though:
How do we get the buttons to work on Mobile App?
Due to it's purpose we've got mobile users but tbh I think it's a limitation of the Mobile App 😕 Like so many things
This may be a bit late, but what I did after a tip was to use a check box/tick box, and get the value of TRUE as a trigger which would save the columns I wanted, and at the same time reset the check box to FALSE if it wasn't already set to FALSE. This 100% works on mobile, I've used this as a time sheet at work for myself and multiple others for years, mobile and computer.
Wow what a great tutorial! It was just the thing I was looking for to help with a data entry solution for our staff at a non for profit organization. :-) I'm seriously stuck though and was wondering if you or anyone here had some ideas, as there are a couple of issues I'm wondering if there are solutions for?
(1) I've created a button in the sheet via drawing and attached a script, but it's behavior is not like a regular UI button, there is no hover highlight or any feedback that it is working until the script processing message dialog pops up. The issue here is that I found myself clicking multiple times and executing multiple script calls :-( Is there a way to prevent multiple clicks - maybe present a dialog box saying the action was successful? Some workaround, maybe not using a button but some kind of dialog box or other trigger to "submit"/(call the script) the entry.
2) Also, it all works okay (except the multiple clicks) for ME, but this is for people in the organization to do their own data entry, and when I try the sheet from a share link, I get an error message after the script tries to run and stalls? Are there special permissions I need to setup for the worksheets, I'm not sure why it's happening?
Here's the link:
docs.google.com/spreadsheets/d/1tNBxkRFfuOhSycgZNX3Cv2xvUZOo4xadYfg457wS1x8/edit?usp=sharing
Thanks in advance for taking a quick look!
Hi. I really like your work and I am totally fascinated, how you deliver your content in a comprehensible manner. I still have a question and I hope you can help: How can I extend this functions to multiple tabs in one document? I want to edit and enter data in severel other tabs and want them to be stored in the data tab too.
Same problem. How can we make this script file for multiple database pages? I have multiple data saved pages due to construction.
Your videos are very educational and have raised my awareness and skills tremendously! Thank you for that.
My question: do you also share the code that you create during the video? Where can we find your sample code? It would help in learning even more.
on patreon
@@ExcelGoogleSheets I’m not familiar with Patreon. Do you have a link?
@@HaroldHorsman I see the link in the description above, there also is a link for some code snippets.
I too would like a copy of the code so I can follow along with the video and once I have a working example I can edit to fit my needs.
I don’t see a copy of the code in any of the links I clicked on.
Is there another way to get it besides watching the video … stopping every few mins and writing it down?
bare and in the mouth! check in the video itself
Thank you
Thanks for making this great tutorial. Just wondering if I can get the link to scripts please?
Could you pls make update record button, Navigation buttons, and print button on form. or already have pls suggest a video.. your videos are BEST!!!
Hello! Excellent video! I would like to set some required fields, how would you do that? Thank you very much!
SIR YOUR VIDEO SO MUCH HELPFUL FOR ME WHO DOESNT KNOW ANYTHING REGARDING THIS..TQ SO MUCH..BUT CAN I ASK U SIR?..HOW CAN WE USE SAME SCRIPT AND SAME SHEET BUT ACCESSED BY MULTIPLE USER IN A TIME..?
aht I just need, now i need to figure out how to make a data entry for intructors scheduling, and that will be anble to tell me which date and time my instructor is available to assign the class to him/her
:)
Hi, thanks for the wonderful information you share here, I follow you step by step on a google sheet that I want to key in and store data on. and the when I reach to the console.log part, The Info there show [Function], what do this mean?
Hi, thank you for the helpful video! I was wondering if you could help me figure out how I can automatically add a timestamp as well to the Data sheet when saving a record.
Until your video I didn't know what a script was. Fantastic and easy to understand. But, like always a question. My data sheet contains 8 columns including currency, numbers and dates. When I feed info in, all are ok, numbers with 2 decimal points, amounts with $ symbol and correct decimal places however, my dates show on my data sheet as "month/day/year". No matter what I format or what I enter the format is always the same. Help!
Thanks
Thank You!
Great as usual.
What if the source and the target are in two different spreadsheets 😇
for source instead of getActiveSpreadheet() use openById("abc1234567")
the rest is the same.
Hi, Thank you for the many videos. Do you have any videos on updating multiple data records from Web Apps to Google Spreadsheets?
> I have a Google sheet data with just Ids and Name - (only 2 columns)
> I have extracted the data to the Web App in a table format using the CRUD codes and include about 4 Checkboxes for each record in the Web App table using create table array methods. (using only one HTML page)
> the objective is to easily see the rows of records and tick the appropriate Checkboxes. User may tick different Checkboxes for each row.
> How do we get these multiple Checkboxes Boolean values from the different records and place these values into the Google Sheets and append to the new columns?
I understand from your video the update is to edit for individual record. But my data just require checkboxes only to update Boolean values to the data and thus the use table format.
Really appreciate if you do have such videos. Sorry for my long request. I'm just a beginner! And thank you again for all the videos.
Question for you, we're looking to keep track of our members and this script is fantastic, however we were also wondering if there was a way to create a another tab like data named "LOGS" and individually track changes to data inputs. In example, the most current information for that member would be displayed in "DATA", however the overall slew of changes to all members would be listed line by line in "LOGS" so we can see what was done to it?
The short answer is, it's possible.
@@ExcelGoogleSheets could u make a vid on it?
Thank you for your videos, I am a new subscriber and they've been very helpful. I have just customized this data entry to my particular needs, and it's working great. I have just one question for you: Do you know how to embed this form so it is editable by anyone with access to the link? I have already tried two options, the one with the appearance of a proper form isn't editable (File=>Share=>Publish to the web), and the one which is editable (Share=>Anyone with the link=>Editor=>Copy link) doesn't have the appearance I´m looking for... Can you shed a light? Thanks again!
Love your videos. Great help. I did the entries and all worked as planned. Then something went wrong. I add some setFormula's to a different function and now the saveRecords function will not append my entries to my data. I checked and triple check the entries for the function saveRecords. Would you be able to help?
Thank you for your insights. And I would like to know how this APP Script can work on Mobile phone or Tablets?
Great videos thank you. This is the first script I've tried, I can not get the fields to populate when using the search box, Ive double checked but with no luck so I've moved on to 47mins in and I am getting an error saying ReferenceError: row is not defined, but I am unsure how to fix this and if its related to the search box not working. I appreciate its hard to comment without seeing the script, but thought I ask as its taken all day so far... is anything you can suggest that I can check? Thanks in advance, regards Sam.
Great thanks for this tutorial. Can you provide a link of example sheet or tempelete for us to download? So , to avoid typo, debug.
Really good thank you. Is there a place where we can copy the code to try ourselves? thx
Wonderful.
Thank you so much for your efforts.
This works well for my needs.
Having said that , I can't use this on my mobile app. (Which is important for me) I have searched online but nothing seems to work. Can anyone recommend a solution, please.
Web App.
Hi .... 🙋♂🙋♂ thank you for sharing the valuable Edu. video for us. I learn lots from that video. but I have one quotation, if the file share with another person and i want to only he update the data but not delete or edit after save the changes then how I can do, its possible ??
Teacher... Nice Video!
Thank you! 😃
@@ExcelGoogleSheets
What were your thoughts to my Invoice query?
I'm not sure what invoice query you're referring to, but if it's about printing, then there is no way to print with a script without external service.
@@ExcelGoogleSheets
Teacher...it was about Adding another Dynamic Table below the existing Dynamic Description Table for Non-Taxable Entries. In the Total Portion, a Non- Taxable Cell is Added. I can't work out a Method to where that Cell keeps track of the First and Last Cells in Both Tables to get Correct Amounts in the Totals Table.
@@Electric-Bob OFFSET function method outlined in I
INVOICE video should work, but it of course depends on the layout.
Hi, great video, was very helpful! I created my own database using this as an example but i am however, having an issue with saving a field "Mobile Number" as the number being saved is loosing the leading 0. Doesn't matter if the field on the "form" page or the working spreadsheet is formatted as text. It appears the process of extracting the data from the "form" page is treating it as numerical and thus the leading 0 is being removed. Any suggestions on how I can correct this?
That may have to do with the formatting of the field. If it is a generic number field "001" will display as "1", for example
Thank you! It is a great lesson for me, But this is enter data one by one, can it enter several duplicate data one time?
thank you so much for saving our time. i'm having an issue while writing onEdit. it says e is undefined. pl guide
Do you have a video where you discuss the pros and cons of using a Userform vs making a data entry form as in this video?
I don't think I do.
🥰 Nice Video 😘
Thanks 🤗
@@ExcelGoogleSheets sir please upload app script releted videos
@@ExcelGoogleSheets Google drive files works from sheet value with onedit function
How this possible sir?
Thank you very much! Your tutorials are really informative and clear but I have a questions:
Can I use Google spreadsheet as cloud date base to be accessed from Android Application?
If this is possible, please guide me how to do it. Thanks
Can you: yes
Should you: NO
@@ExcelGoogleSheets
Thank you for your answer.
Is there any free alternative better than Google spreadsheet?
Try Firebase free version.
Thank you for your great videos!! Can you show how to create Telegram bot using google sheets as a data storage.