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.)
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
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.
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."); }
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
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
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....
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....
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.
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)
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
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)
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
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
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.
@@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.
@@only1guidecom305 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) {
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.
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.
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.)
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
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.
The way you explained each step is very useful and interesting.
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.");
}
@hollybenedetto9256 i dont understand, which loop? still figuring out as the Record Not Found keep appearing.. can you enlighten please.
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
can we use date as id so we can seacrh any data by date its created?
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
Thanks for sharing this video, my question is how if we need to upload picture for
entry data?
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?
for the Search function, how to make multi search option such as Cell A1 and Cell B1
Awesome. Can you help me with with automated data entry "Google Form" from Google Sheet please?
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....
Well explained....😍
Awesome videos! What about a partial search match? For example if a user searched "123" but the ID was "12345"?
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....
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.
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)
Well Explained.... Thanks a lot
Thanks for tutorial, very useful
On my end after the first search the search function stops working. What could be the problem? thank you.
are you selling services to make this?? I want the search function only
Good job
Thanks for your feedback!
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.
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.
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
What can use multiple users in this programming????
I want to add two condition for for search option. please guide.
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?
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)
How if we want search with 2 criteria (ex: i want search sales product a in 1 January 2020)
Can I use name to search instead of number
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
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
Pls make video How to create button insert photo
i follow every step but still, i get the error of Record not Found
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
I only get “Finish script” when I press search. How to proceed?
How do I find 2 different sheets of data?
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
i have the same question. I hope you still remember the answer to this 😂
thanks Sir
I follow the steps but got an error that 'values is not a function'
me too
buttons are not clickeble on google sheets mobile
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
In the line "var str=shUserForm.getRange("C4").getValue();" its ".getValues();" without the s it's not going to search for the data
Only if you would have added image to the form, it would have been very much helpful.
I have to run script thrice because I have 2 dependent drop downs
one of the errors is with var ui: undefined. How to fix this? I am noob
If strings is more than one(on database), script does not work(
your code run but it didn't show the results :V
Data Not Found
Please help me the solution sir
I tried this same bt showing the error for response..
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.
SEARCH function did not work
can any of u get me search... it's not working for me..
Search function not working
same here
@@noorazraei9400 solved
@@miyagenco.ltd.3100 how did you solved?
@@md.rafiulalam6514 solved . Thank you
@@md.rafiulalam6514 replace < with a
My sheet is not working
what is your nu ?
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
I am also facing the same issue and he has not covered this part in the video so please help with this.
This comment. You are my hero!!!!!!!!!!!!!!!!
how to solve sir ?
How to open a google sheet using google apps script?
The script you provided in your website for search, delete, and edit functions are for VBA.
It's for Google Apps Script. Thanks!
@@thedatalabs but it doesn't work. I just followed the one you typed in the video.
@@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.
@@neildelgado3054 Thank you for tips number 1
Good tutoral but there is an error on the row coding
can you please share where's the error
I'm having trouble with the search function
@@AnkitKumar-bk7ik me two
,is there any solution
@@only1guidecom305
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];
}
}
}
search not working there is an error in the script
Error on site.
Do as in the video.
@@IvanEng747 what?
Not working
Please help me
"Script function searchRecord does not found". Dang I thought you are sifu but you left us cliffhanging like this.
I found it guys, make sure every function(){ } has its ending. like {....................} make sure that. thats solve my problem. done amigos
What is your typing speed bro?
text
Too small to see.
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.
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.
Please help I am getting error "Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Sheet.getDataRange."
There's an errror: TypeError: myGoogleSheet.getSheetByName is not a function PLEASE HELP
Same me too
There was a problem ,Showing ''I not defined? '' can you please help me with this.
That is ''Ui''
Not working