Automated Data Entry Form in Google Sheet & Google Apps Script - Part 2 (Search Function)

[@emilylandis5832]

For anyone struggling to get the search function to work, there's a small error in the last if statement - should be if(valuesFound == false) { ... } (the 2nd = is missing in the original code). When I fixed that, the code started working for me. (To clarify, the function was working when there was a match but I was having trouble getting the false case to work and give the alert to the user that no records were found.)

[@InnocentiaHan]

I have the same problem with you.. but when i tried to delete the record that appear from search, then I write a wrong / not match record. I got the error message

[@jijeeshpadmanabhan350]

The way you explained each step is very useful and interesting.

[@ariffdaud5332]

can we use date as id so we can seacrh any data by date its created?

[@profitrocketchannel]

For those of you getting a "Record not found" alert for each instance, make sure you have closed the loop before you write your IF statement for setting the alert... meaning close your if statement about the row values with one curly bracket } and then be sure to also close your FOR loop with a second curly bracket } before beginning your "if(valuesFound=false){" set. I also added code to clear the input values in the form if I had a false return. if (valuesFound==false){ //code to clear user input shUserForm.getRange("C7").clear(); shUserForm.getRange("C9").clear(); shUserForm.getRange("C11").clear(); shUserForm.getRange("C13").clear(); shUserForm.getRange("C15").clear(); shUserForm.getRange("C17").clear(); //alert var ui=SpreadsheetApp.getUi(); ui.alert("No record found."); }

[@data.muncher]

@hollybenedetto9256 i dont understand, which loop? still figuring out as the Record Not Found keep appearing.. can you enlighten please.

[@jeanlexcabayao]

please help me may search record is has problem they said my googlesheet get Sheet By Name is not a fuction what I will do.

[@phamhongvan6232]

Thanks so much for taking your time making this video. This is what I’m looking for 🙏🏻👌🏻 If possible, could you make a video on the search function which can search from multiple column? For example, there are 3 columns in the database sheet: name, phone number, email address; and if someone just type a partial match. Look forward to watching that video from you soon. Thanks.

[@yusufsharif9160]

Wooow this is best tutorial...just want to ask if i want to input salary records for every time i pay my employee every week..so its something that i have update data every week and keep records...please can you make a video...thank you

[@yogeshbco8049]

How can a write function or is there anyother way, which will throw alert messages if all the details are not filled in entry in employee data form.

[@pematenzin166]

Hello "TheDataLabs", i have been seeing our videos and have been helping me a lot but however i need your help further. It is in regard to the submit button since the data is saving it self when hitting no button instead of clear the contents...Would be great if you can teach me that.

[@shermanscs]

This is very good and helpful, but my issue is two people using this "form" at the same time. They will compete with each other on the data entry, no?

[@wan5682]

for the Search function, how to make multi search option such as Cell A1 and Cell B1

[@richardrose8453]

are you selling services to make this?? I want the search function only

[@BobbeLabastilla]

On my end after the first search the search function stops working. What could be the problem? thank you.

[@seniordom5278]

I want to add two condition for for search option. please guide.

[@educationaltips7743]

Well explained....😍

[@ManpreetSingh-dl6og]

What can use multiple users in this programming????

[@salinrajkunnummal8018]

Well Explained.... Thanks a lot

[@dgrolfe]

How does one do a partial search? For example, if I'm looking for "John Doe", I want to be able to type "John" or "Doe" to find the data... Also, if there are multiple "John"s in the database, I want to be able to type "John", then select from the list of "John"s....

[@adityanaufalghifari9112]

How if we want search with 2 criteria (ex: i want search sales product a in 1 January 2020)

[@mohammadhasnain313]

Thanks for the amazing tutorial! Is there any way to add a next button which goes to the next entry in the database with the same employee id (Asking incase we do double entry of the data)

[@marchrian2512]

Thanks for sharing this video, my question is how if we need to upload picture for entry data?

[@ranjangpengantin6300]

Awesome. Can you help me with with automated data entry "Google Form" from Google Sheet please?

[@MeyissanLEGBA]

Good job

[@thedatalabs]

Thanks for your feedback!

[@findingidentity]

How to trigger an email notification for the same as soon as a user submits the form with all the details submitted by the user?

[@mohdillyas4899]

When I write the script for search function it is giving I'd is I'd for first name as 1 last name as 2 etc please help where is mistake I used your same script

[@ecolemontessori7102]

Thanks for tutorial, very useful

[@thaigentcompany1044]

Can I use name to search instead of number

[@Aaron-xn9ym]

Could you please amend this to include how to set up the html page, and the "doGet" code? I spent a day and a half following this to the letter, and can't deploy it because of the doGet error. I've spent the other half of today researching this, and can't get it right. Also, when searching, no alert is given stating "No data found", it just does nothing. EDIT: Fixed. Alert being given now. My code had 1 equal sign, needed 2. if(valuesFound==false)

[@littleclover2334]

Nice video! Want to ask something sir. If the employee id is alphanumeric, is the search button be not case sensitive? Because when i tried it it only searches the same case used when you record the employee id in the database. Thanks in advance

[@ravshanrakhmatullaev6731]

H sir. I have a question. if there is a sentense "finished script" i presses search button, what should i do? It is not looking for available data

[@mamasusan3992]

i have the same question. I hope you still remember the answer to this 😂

[@zeeshanpasha-bz5xu]

buttons are not clickeble on google sheets mobile

[@fbestchannel7919]

Thanks for useful tutorial. can u please teach me how to deploy this App Script As a WebApp. when i try to deploy it and just error come out with no "doGet" and blank page

[@educationvilla]

I have to run script thrice because I have 2 dependent drop downs

[@pedrorevolorio6960]

Awesome videos! What about a partial search match? For example if a user searched "123" but the ID was "12345"?

[@admercs762]

How do I find 2 different sheets of data?

[@elvicendzi9297]

Please sir i have a serious problem. when every i tried to inset a new record it some give an error {Method ' _Default ' of object 'Range' failed

[@mohapatra007bisu]

Hello Sir .... Hope you are doing well... I am very much thankful to you on sharing such tutorial with us... It is a great help to my company project... I have zero knowledge in JS coding... But after watching your video I am able to write code... Thank you for that.... Sir could you make a tutorial video in this Google Form series about Dynamic Dependable drop down list (single level and multi level). Date of last entry of the customer (person) in search option... Please sir... 🙏🙏🙏 Also the way you explaining things is really remarkable.... So could you please make a complete tutorial series of videos from Beginner level to Expert level for Google App Script.... Please.... If you are providing any online class then please let me know... I would like to join that as well... Thank you Sir....

[@62135134]

when i set the valuesFound to false at the bottom for the alert it is not working, but when i set it to true it is now working. hmmm, im confused

[@RakeshSingh-yd7xo]

I am also facing the same issue and he has not covered this part in the video so please help with this.

[@laurawilliams8852]

This comment. You are my hero!!!!!!!!!!!!!!!!

[@enriicochiiesa5022]

how to solve sir ?

[@mamasusan3992]

I only get “Finish script” when I press search. How to proceed?

[@jacquilynramilo4711]

i follow every step but still, i get the error of Record not Found

[@МираидаКалбаева-в5в]

var myGoogleSheet=SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm=myGoogleSheet.getSheetByName("User Form"); //declare a variable and set with the User Form worksheet var datasheet=myGoogleSheet.getSheetByName("DataBase"); //declare a variable and set with the DataBase worksheet var str=shUserForm.getRange("C4").getValue(); var values=datasheet.getDataRange().getValues(); var valuesFound=false; //variable to store boolean value for (var i=0; i

[@sirfondeck651]

thanks Sir I follow the steps but got an error that 'values is not a function'

[@NotJustInfo]

me too

[@sonupatelstudyhi9303]

My sheet is not working

[@AnaMariaStoi]

one of the errors is with var ui: undefined. How to fix this? I am noob

[@Andiszamm]

Data Not Found Please help me the solution sir

[@miyagenco.ltd.3100]

Search function not working

[@noorazraei9400]

same here

[@miyagenco.ltd.3100]

@@noorazraei9400 solved

[@md.rafiulalam6514]

@@miyagenco.ltd.3100 how did you solved?

[@miyagenco.ltd.3100]

@@md.rafiulalam6514 solved . Thank you

[@crimsonk91]

@@md.rafiulalam6514 replace < with a

[@archanapatil3358]

I tried this same bt showing the error for response..

[@meakkakda514]

Pls make video How to create button insert photo

[@IvanEng747]

If strings is more than one(on database), script does not work(

[@vedavalli9745]

can any of u get me search... it's not working for me..

[@tayyabzohaib63]

Only if you would have added image to the form, it would have been very much helpful.

[@uctrongvu9408]

your code run but it didn't show the results :V

[@Vavgamefmindia]

what is your nu ?

[@AnkitKumar-bk7ik]

search not working there is an error in the script

[@IvanEng747]

Error on site. Do as in the video.

[@mont9150]

@@IvanEng747 what?

[@solartechdocument419]

How to open a google sheet using google apps script?

[@ORMINEMPLOYMENTFACILITATIONOO1]

SEARCH function did not work

[@user-dn4yz3xe1t]

Good tutoral but there is an error on the row coding

[@AnkitKumar-bk7ik]

can you please share where's the error

[@AnkitKumar-bk7ik]

I'm having trouble with the search function

[@khairul-islam-khan]

@@AnkitKumar-bk7ik me two ,is there any solution

[@AnkitKumar-bk7ik]

@@khairul-islam-khan Use Below Code:- function searchStr() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var formSS = ss.getSheetByName("Form1"); //Form Sheet var str = formSS.getRange("C4").getValue(); var values = ss.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues(); for (var i = 0; i < values.length; i++) { var row = values[i]; if (row[SEARCH_COL_IDX] == str) { formSS.getRange("C7").setValue(row[0]) ; formSS.getRange("C9").setValue(row[1]); formSS.getRange("C11").setValue(row[2]); formSS.getRange("E7").setValue(row[3]); formSS.getRange("E9").setValue(row[4]); formSS.getRange("C13").setValue(row[5]); formSS.getRange("C19").setValue(row[6]); return row[RETURN_COL_IDX]; } } }

[@johnloisbanez4746]

Hi, I've made a data entry form using this tutorial. There are instances that ID will be the same for different entries, how can I make the "search function" search the next records with the same ID? Thank you.

[@jojoafable2755]

Not working

[@educationvilla]

Please help me

[@neildelgado3054]

The script you provided in your website for search, delete, and edit functions are for VBA.

[@thedatalabs]

It's for Google Apps Script. Thanks!

[@neildelgado3054]

@@thedatalabs but it doesn't work. I just followed the one you typed in the video.

[@neildelgado3054]

@@thedatalabs There are few things that I'd like to ask regarding the script you made. 1. In the submitData(), why did you use the clear() instead of clearContent()? The latter won't reset the formatting of the cells and it will save you the trouble of typing the code of bringing it back to the original formatting, 2. In the submitData(), is there a way to prevent duplicity of data entry? As when you use, the search function the data encoder may accidentally click the save button and would duplicate the entry shown in the user form. 3. In the searchRecord(), you did not test the script when the entry is false. As when the data entry is false, the UI dialog just says the script is finished.

[@umarhakimin]

@@neildelgado3054 Thank you for tips number 1

[@mont9150]

"Script function searchRecord does not found". Dang I thought you are sifu but you left us cliffhanging like this.

[@mont9150]

I found it guys, make sure every function(){ } has its ending. like {....................} make sure that. thats solve my problem. done amigos

[@anamikasatyam1096]

What is your typing speed bro?

[@zucbsivrtcpegapjzwrf2056]

text

[@koungsareth8623]

Too small to see.

[@noemimesesan1054]

There are so many comments from people saying it's not working is unbelievable that he doesn't even try to reply and help. You made a useless video for so many people and you don't care to make another video to sort the issue. I followed everything step by step and it doesn't work. If I set valueFound=true; it will give me the alert of No record found even if there is record. If I set it to valueFound=false; it will run the script and not give me any alert even if there isn't a record. I don't get it, sometimes it finds the record, other times it doesn't, but it doesn't give me any error, just runs the script without any alert and it doesn't even find the data I asked for (to mention that the data is present in the database sheet) If someone can help I would greatly appreciate it.

[@Aaron-xn9ym]

I too do not see the message about "No data found" when searching for a non-existent record. I also cannot deploy the app, because I get the "doGet" error. I know that you have to have an html file along with the code file, but no one, and I mean no one can explain how to set up the "doGet" in plain english, and what to put in the html page.

[@khairul-islam-khan]

why my search fuction code not working? here is the code i am using- //Function to Search the record function searchRecord(){ //declare a variable and set with active google Sheet var myGoogleSheet=SpreadsheetApp.getActiveSpreadsheet( ); //declare a variable and set with the user form reference var shUserForm=myGoogleSheet.getSheetByName("User Form"); //declare a variable and set the reference of Database sheet var datasheet=myGooglSheet.getSheetByName("Database"); var str=shUserForm.getRange("C4").getValue(); //getting the entire values from the used range and assigning it to values variable var values=datasheet.getDataRange().getValues(); var valuesFound=false; //variable to store boolean value for (var i=0; i

[@danielcoello1146]

In the line "var str=shUserForm.getRange("C4").getValue();" its ".getValues();" without the s it's not going to search for the data

[@sanjayksharma102]

Please help I am getting error "Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Sheet.getDataRange."

[@mellacristobal445]

There's an errror: TypeError: myGoogleSheet.getSheetByName is not a function PLEASE HELP

[@jeanlexcabayao]

Same me too

[@harshitha.vk.h.venkatesh315]

There was a problem ,Showing ''I not defined? '' can you please help me with this.

[@harshitha.vk.h.venkatesh315]

That is ''Ui''

[@sonupatelstudyhi9303]

Not working