Google Sheet Multi-user Data Entry Dashboard | Data Entry Form | No Coding Knowledge

in this video you will see how to create a multi-user data entry form on Google sheet so what it does we have this dashboard here and whenever I click on this enter button it will enter the data to this worksheet I can search for a student since this is a student data entry form I can search the students with the role number if I search for it I'll see the data for this role number if I want to edit this data I can edit it I'll add some text with the name and I'll click on this edit button and it will edit you see samel all to if I want to clear this Fields here I just need to click on this clear button and it will clear all the fields you can share this worksheet with other people and they can also use the same worksheet to enter the data and we have an operator cell so that when the person is entering the data they can select their name so on the database we have the name for the operator right you can create multiple operators name here this is a simple drop down and we have the gender selection dropdown here and also the date of admission Pier like date picker function I have designed this completely without any code like it needs code but you don't need to know how to code or something you don't need to understand the code the code you see here I haven't written this code the code I have generated using chat GPT if you have chat GPT access you can write prompt to generate the codes here you don't need to have GPT 4 access if you have GPT 3.5 access you can create this functions otherwise you can sign up for cloud. a here and create the functions so what I have done here I have created simple prompts to get the functions and you see all the functions are working perfectly so let's start how I have created this multi-user data entry form I will start with a blank Google sheet here and I'll rename it to student data entry now I will Design the dashboard I'll create the entry Fields here one by [Music] one I am creating the fields for a normal students data entry but you can design this form whatever you need like you can create for employee data entry form or Inventory management it is the same now let's design this whole view a little [Music] [Music] bit I have marged all these fields above and I'll create a header I'll create one more field here for the operator because this is a multi-user data entry form right so I will create a single field and I will make this operator okay for the grade I'll create a drop down to right click on the grade text box and select drop down here I'll put first second third and fourth okay I can select some colors for the different grades here if you want to select different color you can select them or you can leave it as it is okay done so when you select one grade here you can see the different color options okay I will create one more column above this because I have missed one that is gender for the gender I'll also create a drop-down so again I need to select dropdown and I'll do male and female okay for the date of admission I need to create a date peer so whenever I click on it it should create a date Pier option it should show the month so again I need to create some validation here select the sale go to data and data validation here add rule I'll create is validate that's it reject the input okay done so whenever I double click on this it should display this calendar right now we are ready with the design okay for the operator I'll create another drop-down so when we are sharing this dashboard with multiple people so that they can select their name when they entering the data on our records we will have the operator's name done okay we have the operators you can create multiple operators for this demonstration I have created only three now we will create some buttons to perform the actions so I'll add some drawing the first button will be for entry so I'll make it enter and I'll make the background color white save and close now I'll drag this button somewhere here again I'll create three more buttons drawing one is for search and another is for edit and one is for clear the fields now I will align this button here the size of the buttons are not perfect but you can resize them with this arrows right okay now we need to create another worksheet where all the data will be saved I'll rename this to data and the input worksheet to input so that we understand data and I have to create the header for all the columns we want here so the First Column we will add role number and the name and then grade and then gender date of admission remarks and entered by and date of Entry okay these are my colums so I'll fill this worksheet using this dashboard we just created for that we need to create some functions right so where to add the functions if you click on this extensions menu you'll see this app script I'll rename the script to student data entry the name doesn't matter you can keep it Untitled project if you want but I'll rename this there's some blank function but we need the functions so that we can make this button work right I will use chat GPT to create all the functions chat GPT can create the functions we want here if you can explain what you want it will create the functions in a moment so I already have the prompt ready here you can see the prompt here I have mentioned that I have the role number name grade on this cell if I make it side by side you can see I have mentioned all the sales where I will start entering the data and what functions I need so the first prompt you see here is for the enter button I will create a function and assign the function to the enter button to make it work so basically what I have asked it I have asked that I have the role number name grade gender date of admission and remarks all these celles and also this operator cell this operator cell is different like it is not associated with this student data but it's for our record and also I have mentioned that I want to record the date of Entry what you see here you see here date of Entry so we don't need any field for the DAT of Entry column and I'll run this prompt if you don't have gp4 access you can try gpt3 3.5 it is not like gp4 but it can create pretty good functions another option you have if you want to get good results but you don't have gp4 then you can use cloud. a if you are in the US or UK you can get access of cloud. for free I will show you how cloud. a works you can see the prompt here if you want to copy the prompt you can pause the video and copy it and it is creating the functions we need it will create only one functions the function name is enter data okay in the me time I will show you how cloud. a work so this is cloud. a I have put the exact same prompt here and if I send this prompt I'll get similar results here this is free if you don't have gp4 access you can use this cloud. a okay but for this video I will use chat jpt outputs it created the function if you don't understand any of this what you see here you don't need to just follow the instruction here so what I'll do I'll copy this function here here and I'll come to this appscript and I'll delete the existing blank function and paste it here and I'll save this now we want to assign this function to the enter button so what I'll need to do select this enter button we just created the drawing and click on this three dots and assign a script now here you need to enter the name of the function that is enter data if I come here I'll see the name of the function enter data okay and also chat GPT has mentioned that assign the script type enter data as the function name so you have proper instruction what to do now what I'll do enter data okay saving let's try it how it works I'll enter a rule number 1 2 3 name grade second mail date of admission let be remarks I'm just adding a sample remark and I'll select the enter button for the first time when you are running the script you need to authorize the script so what I'll do I'll just click on the continue button and I have to authorize this script click on this Advanced and select go to student data entry unsafe the name of the app script allow okay it is ready now again I will click on this enter button and it says new data entered and if I go to the other worksheet and I see all the data with the date of Entry this is the current date and this is the operator name you see the operator name here let's select another operator here and create another record 1 2 4 enter new data entered you see the new operator name what we selected right and the good thing is we already have the duplicate option so if I try to create the same role number like 1 2 3 and with a different student name it doesn't matter if I have the same role number it will not create the record you can select anything here it will check the same role number if it is present in this column all right so let's try to enter the rule number 1 2 3 enter it says role number exists and what it does when a it found a role number while you are entering a data it will bring the existing record you see one 2 3 name Samuel and all it will bring all the existing records from this row that is very useful and what you can do with this you can edit this data we haven't assigned the other functions with the other buttons so these buttons will not work let's create the other functions let's go to chat GPT and create another prompt this time I will create the prompt for this three button what I need for the search button edit button and clear button so what I have asked I have asked that when there is some role number in D5 the search button will search for the role number and bring all the data for that role number in this pH builds right if the rule number is empty or it's not found it will display message and another function is to edit the entry you can see the prompt here and the last function what I have asked is for the clear if I click on the clear button it will clear all these fails right so I want to create all these three functions at once let's send the prompt just remember to write the cell names exactly what you have designed if you have different type of data like multiple entry at once it doesn't matter it works perfectly so it created the search data for this search button and now it's creating the update function for this update edit button okay and the last one is clear function so what I'll do I'll copy one by one I'll copy the first one search function and I'll scroll down and come here let's make it big okay I'll come here and paste the function again copy the next function that is update function and paste it here and the last one is clear Fields okay copy it and paste it perfect save it now what you need to do we need to assign the functions one by one to the buttons Sarge data I'll copy the function name and assign this to this search button paste it okay and the next function is update data update data and I want to assign this to edit button update data and the last one is clear function okay clear this Fields okay assign a script okay assign the function with this clear data okay make sure to save all the functions you just entered now let's try with the clear button when I click on the clear button it cleared all the field now let's try to search with the rule number 1 2 3 search okay it didn't work because the field was selected so what you need to do you need to enter the data that is role number here and click outside this cell so that the function understands that it has some data otherwise it cannot detect because when you are editing a cell Google sheet doesn't update that cell now I have selected a different cell and I will select the search button and it brought all the data for rule number 1 2 3 so what I can do here if I make it female currently Samuel is mail I just want to see if it updates and I want to edit this student data and I what I want I just make this gender female and click on edit entry updated okay and you see it is female now let's try to find something that is not present in our database so I'll try one to5 and search rule number not found perfect one more thing if you enter a duplicate role number for the new student Samuel new and click on the enter button it will show you role number exist and it will bring the data for this one 2 three and here you can update this student with this edit button entry updated you see now what I'll do we will share this worksheet with other person and we will see if it is working for that I'll select the share button here and I'll put the email ID of the other person and click on this send so I have received the invitation to edit that worksheet I'll open that and I'll see all the buttons If I click on extensions and app script I'll see all the scripts here I do not need to do anything with the app script but what I need to do if I run it for the first time one 125 and select the operator name since I am now a different person I need to select the operator field here and click on enter for the first time I need to authorize this script we created this needs to be authorized for all the persons you have shared the Google sheet with allow now it is ready click on enter new data entered let's see how it looks it looks good so our multi-user data ENT form for Google sheet is ready this is completely working like I have tried with other fields it works great you can create as many fields as you need okay thank you for watching if you like the video please subscribe to the channel if you have any suggestions or feedback please write in the comment [Music] section

Channel: Technologiate

Views: 65,817

Keywords: Google sheet data entry form, google sheet data entry dashboard, google sheet data entry with edit, google sheet student data entry form, google sheet employee data entry dahsboard, google sheet crud dahsboard, google sheet multi user data entry form using appscript, google sheet dashbaord without code, google sheet dashbaord for non coders, google sheet sing and multi user data entry for, create google sheet data entry dahboard without any coding knowledge, google sheet multi user

Id: 2i70xlDoGS8

Length: 16min 55sec (1015 seconds)

Published: Mon Oct 09 2023