Search button is not finding any data

[Narc Angel]

So I've tried many scripts so far, none works.

I have a user form, first two buttons are working fine, Created an Entry and Post that entry into Database.  Here's a picture of the form.

Now here's a picture of the Database where the search should be able to retrieve info from only column E, no other cells, only the cell where the names are.

 You can clearly see there is a "Yuan", I've tried also "Yvon", any names, search always says "Nothing found".  Obviously my script isn't working, I've also tried ChatGPT and it's a fail.  Here's the script from ChatGPT.

The form is in User Form and the database spreadsheet is in Database.

[Keith Andersen]

You're searching for Yuan, but the name in the database cell is Yuan Lachance. Your script therefore compares the two which do not match at all.  Yuan != Yuan Lachance

Either separate the names in the database, putting the first and last name in separate columns and then search only one column, or create a script that can find Yuan in Yuan Lachance.

Cheers
Keith

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8850fb49-b8ba-4385-9288-2b12058d3ae2n%40googlegroups.com.

[George Ghanem]

A simple solution would be to just search the string you are looking for to see if it is anywhere within the longer string.

Use the if statement as follows:

if (datarange[i][0].toString().toLowerCase().indexOf(searchterm) >= 0 )

For Searchterm, fix it outside the loop to lowercase so you don't need to redo the work every row.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGLwqEMZmETdz3hCcNWk-h6Dt5x%2BkD_XnqSOmJto4ki7w%40mail.gmail.com.

[Narc Angel]

Ok so this is the entire script:

https://justpaste.it/8kwqt

Now I got 2 problems, search works but now it's case sensitive I would prefer it would it would search for lower and upper case letters.

Second problem, call detail section, C16:20 never works, nothing is filled out there.  Search work, search is able to fill most cells except for the call detail.

Also, another problem I noticed, a bit annoying, the CLEAR script clears EVERYTHING, text+style, I don't want to lose style and cell merges each time I clear the form, it's deleting all the text, the cell merges and the font styles.  Is there a way for CLEAR to only delete text?

[George Ghanem]

I will take a look at what you changed later when I get in front of PC. But I had told you to change searchterm to lowercase before the loop. I suspect this was missed.

Deletes can be remove values only. Just need to use right option.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/aacab5f2-7fc9-434c-ad14-dcaecf0d1431n%40googlegroups.com.

[George Ghanem]

Hi,

 Ok I looked through your code and updated it to fix the search as well as fix the clear. However, I do not know what you are referring to with respect to details. Perhaps it may be easier if you share a sample spreadsheet.

 Here is a spreadsheet I created with your code and my modifications to test it out. Make a copy of it so you can see what code changes I have made.

 Search Example - Google Sheets

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/aacab5f2-7fc9-434c-ad14-dcaecf0d1431n%40googlegroups.com.

[Narc Angel]

Ok sorry for the delay, I work in a none profit organization.  Ok so here's the 2 spreadsheets which I have exported to a blank project it's isolated now.

https://docs.google.com/spreadsheets/d/16DrtbRwI8zTZeN5gDyEEjhSWwez-uYKqDFdrp5GQbC0/edit?usp=sharing

I've decided to wait to make your modifications just in case I don't mess up things even more so let me recap the issue.

1.  Right now the Search (input C3) works great ...but only searches for EXACT case sensitive results only which I do not want, I want it to search for "Yuan" or "yuan" or "Yuan newer" or "Yuan Newer" etc.  Again the search must be confined to Column E only (where the names are stored in the Database sheet).

2. Over time I will have much more than just 1 "Yuan Newer" I want the search to give me all the results for "yuan newer" from oldest to youngest and let me choose to click on the one I want to see (pulling all the data from Database and populating the correct cells in User Form).

3. Huge problem regarding "Nouveau" which resets the sheet of all data.  The button works fine but it works too great, it resets the style and the formatting not just the data in each cell of the form which means the next search results is all messed up font wise.  I have set all the form input cells to 13 and in Bold and from the left-center line, and C16:C20 are merged cells with text wraping on but all of that is wiped out each time I press "Nouveau".

4.  Search works great at pulling data from Database but does not populate at all C16:C20, it seems it does not see that one at all.

I think that's about it... I have not worked yet on the "modify" (Modifier) script being I've stopped since I hit the Search problem.

[Narc Angel]

So this is the image of the current form all messed up because of "Nouveau" wiping all style/format

https://justpaste.it/8zx92

And this is how it should be.

https://justpaste.it/4pzrb

[Narc Angel]

And yes I know the form is quite ugly but this is my first big form project so I'm tossing aside aesthetics for the moment for functionality.

[George Ghanem]

Thanks for putting the sample spreadsheet together. Please provide access so that I can edit it.

Thanks for listing the issues. I think the search and formating should be fixed I  the last code I sent you. I will look at other issues and merge in my changes once you provide access to the spreadsheet for me.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/dd7b996a-66ec-4fac-9b9f-835a2ba5b1abn%40googlegroups.com.

[Narc Angel]

I'll apply the codes right now while you take a look at the rest of the form, thank you!

[Narc Angel]

Sorry I'm confused, once I copy the sheet shouldn't I see the script in App Script?  I see the form you've made, I made a copy so I can edit, I would think to see the script you wrote I'd simply go in App Script but it's blank.

[George Ghanem]

Yes, you should be able to see the script once you make a copy of the spreadsheet.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/eaaf7f38-3c06-4cc2-af4f-b6c97ab78781n%40googlegroups.com.

[Narc Angel]

Yeah I couldn't see the scripts at all until you made it editable for me.  Now I can see the script.

[Narc Angel]

Ok so basically it works all good... although I'm confused as to how you addressed the clearform being that you scripted only search but the new search scripts corrected also clearform which isn't wiping out font/style anymore.  Now I can tackle the last phase of the form "Modifier" which is the button that send the data back to the database without adding a new row but just modifying the existing one.  

I also got to test out what happens if they are multiple same names.  Ok... the result of 2 "albert" in search means that it does not give me a choice of which one I want to edit, it only gives me the first "albert".

[Narc Angel]

Let me recap a bit the context.  I work for a non-profit organization, we drive elderly people to their appointments with doctors.  I will get a variable amount of calls per day and each call I enter the information:

Date

Time

Type of call (Data Validation List)

Telephone #

Name

Detail of the call

Conclusion of the call (Data Validation list)

Often I will get the same name asking for appointments at different time.  Which means I should see a list of those names in the input search in some way.  Say I have 20 "yuan newer"... wouldn't the form allow me to chose the correct one in a popup where it might list at least the date/name?

I mean it's always possible for me to bypass the form and go directly to the database and make the corrections there.  But it would be interesting to see a search result with more than 1 result when they are say 10 of the same name.

[George Ghanem]

Hi,

 I see what you mean. Having multiple search results shown and then you would choose from one of them. I am not sure how this can be achieved in a spreadsheet though. Do you have a view of how you want the multiple search entries shown?

 I do not mind continuing to help you since this is a non-profit. But I would suggest we take this offline rather than continuing communicating via the google group. 

Think about how you want the info displayed (for search results) and lets continue discussions offline. You already have my email address.

 Thanks

  George

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0d1fca14-cfd3-4419-a9b4-6f38b762c030n%40googlegroups.com.

[Narc Angel]

We are strictly a none-profit, I earn a salary, we are about 20 employees but our main force are volunteers which I assign a call and hope they respond to bring a person that is (usually) 70 years and older to their doctor appointments.

The multiple search results might be way too much to ask for a script and a form so I might just give up on that one, I still have the Database itself I can easily see the name I want to change the data.  I was just thinking it was a easier to do it with a script.

We work month by month, when a new month starts, we start fresh.  Each call I take goes into a database, many time the same person will call within a month and a great many time within a year (after a year I start a new database).  Maybe a solution would be a script that would pull the last matching and not the first one?

So let me explain that one, the search right now seems to pull only the first entry of a same name results, what is mostly important for me is to view the very last entry (the most recent one) of that same name result instead of giving the first one it finds which is actually the oldest entry which I never really need.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/1PcdiSZ-gzE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA4K68akDyWMLvdNTsReg6eTpoB9GUxkfNNFK_prHZZzrBHAvg%40mail.gmail.com.

[Keith Andersen]

I think you could accomplish multiple matches via a query. Once that populates, select the cell of the one you wish to load into the form. Then click a button to load that persons data. This function to accomplish this would utilize activeCell( ) in app script.

George - do you follow my thought process?

I'd be happy to collaborate but don't want to push myself into your space George.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAD3-hYM-x51KcjR3%3Dvwifx6PiLQ0YPXXsV7ZE2e%2BZVrxH7CDiA%40mail.gmail.com.

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[George Ghanem]

No problem Keith. I would be happy to share the payment ($0) with you on it.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BH3HyWU6zYEx11KGBbhgfSLF2CfGav5WJU2MMAckS_jUA%40mail.gmail.com.

[Keith Andersen]

George 🤣👍👍

Narc, If you'd like to share the spreadsheet with me, I'd be happy to take a look and add my two cents.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA4K68Z1S%3DQh5gTKzjWbnFHapnWJRD%3Dn2wmsQZW6nPjjJ%3D8VKw%40mail.gmail.com.

[Keith Andersen]

I implemented a simple dropdown menu for the search. You can use the dropdown directly, or as your database grows - simply start typing in a name and it will present any matching letters/names for you to choose from.  Might want to change button from Search to Load Record or something.

Additionally, clear() clears formatting and validation. I changed them to clearContent() which only clears values.

Narc, I see you have input validation. How do you implement it?

Hope i didn't miss something. If any questions...i'll be hanging around.

Cheers,

Keith

[George Ghanem]

Hi Keith,

 Thanks for trying. But, that is not what Narc wanted. He wants somehow to show the multiple records that match the search and be able to select between them. 

I am thinking that what may work for him is on the search to create a list of records and then have a "Next" and "Prev" buttons to be able to go through them one at a time. This means the search would need to store the results somewhere for the buttons to use to show the various records.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BEOL_JTbfxjkMg8QxQ%2Bfuifn%2BYXhkhqpH1e2gOtShKm_g%40mail.gmail.com.

[Keith Andersen]

Oh, Ok.Well, i'll give it another try.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAA4K68Yqy-1LePGjSmd3553hupbC2%3D_y2n2bvPa45X6Sz9vicA%40mail.gmail.com.

[Brent Guttmann]

Just stumbled upon this and thought I would throw an idea out there for ya to see if it helps.
 
Let's say Sheet1 is blank and Sheet2 is being used as the database, you can implement a sidebar ( using the example and code found here ) that displays the data from a selected row in Sheet1. You could (Not sure how advanced you are) modify the example to make the sidebar fields editable and add a 'Update' button at the bottom. When clicked, the row data could be passed to a function to overwrite the corresponding row in the database sheet with the new row data. You could also make a button to add new records, which would just use the appendRow function in App Script.

 
Sorry, I didn't have time to read everything and figure out how your data is set up, but lets assume your database in Sheet2 has data in columns A to J, and column B contains the names you want to match, you could add the following formula in cell A2 of Sheet1:
 
=ARRAYFORMULA(QUERY({{Sheet2!A1;ROW(A2:A)},Sheet2!A1:J},"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 WHERE LOWER(Col3) = '"&LOWER(A1)&"'",0))
 
This formula allows you to enter a search term in cell A1. It will return all matches starting in column B and display the row numbers of the matched data in column A. By integrating the sidebar mentioned earlier, you can navigate through the matched rows in Sheet1. The sidebar will show the related data for each selected row, allowing you to make edits directly. Pressing the 'Update' button could make the changes to the relevant row in the database sheet, using the row number from column A of the results to identify which row to update.
 
Not sure if you followed all of that or not, but hopefully it may steer ya down the right path!

[Brent Guttmann]

sorry, that formula should be:

=ARRAYFORMULA(QUERY({{Sheet2!A1;ROW(Sheet2!A2:A)},Sheet2!A1:J},"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 WHERE LOWER(Col3) = '"&LOWER(A1)&"'",0))

[Keith Andersen]

Narc,

Have you seen the solution on the test spreadsheet you shared with George?

Keith

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/38cdff83-de14-42a4-9c12-760e6de0fee5n%40googlegroups.com.

[Narc Angel]

This is getting confusing I'm in email and also in the group.  If you're talking about his spreadsheet where he resolved most of the issues yes, the searches works with caps or not, it populates all the correct cells and it does not erase any style when you clear it.

I've seen or come to understand that finding multiple names is near impossible or too advanced a task.  So I went back to searching the database itself for that.  My main thing is, when a person calls me, I enter the data into the form and the form injects the data in the database according to the fact it's the latest entry.  At times, my boss wants to check about 1 person in case there was an error or more informations she needs about that person, to help her I thought it would be easy to come up with a form that would print out all the results on the screen of that person's name for the current year, she would probably select the last choice or near the last choice and look at the details of the call.

But a form like that I think needs professional programmers or something much more elevated.  I'm think it's easier to just either CTRL-F or sort by name.  The thing is, I get probably 2000 calls a years so that's lots of names.

As it stands tho, the form is working perfectly... it will draw the name from the database and populate the right cells.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG5v3VWTnLGjVw6qAbuET_nkMM1U3gbUrafcFF-b1GGww%40mail.gmail.com.

[Narc Angel]

I did forget... the only problem with using the form search input is that it will only pick the first name in the database not the last name.  Say I got 5 john doe... 1 john doe 2 john doe 3 john doe 4 john doe 5 john doe, 1 being the first entry and 5 being the last, it pulls only 1 not 5, 1 being kind of useless since it's an old call and 5 is essential being it's the last call.

[Keith Andersen]

I was referring to the Blue form in the Test spreadsheet. Tab Sheet1

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAD3-hYP8y996rSqKNgCAOgJ20f%3DzQtV1AfuwNAyHKmkB49SSKQ%40mail.gmail.com.

[Narc Angel]

Ok so Keith... I've tried test sheet.  I'm not sure about Column E.  My problem with that format is I would have to create hundreds of names and those names are not sorted in order but I do understand your idea there.  It's just that, each call has a potential for a new name, I would have to edit Data Validation / List and add a new name manually each time instead of just populating Column E with entering the name.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG5v3VWTnLGjVw6qAbuET_nkMM1U3gbUrafcFF-b1GGww%40mail.gmail.com.

[Narc Angel]

I mean it does work... I like that Search will allow me to auto complete a name.

I like that it chooses the last same name and not the first same name.

I'm still not too sure about Column E but... it might work.  Validation would be huge, I could like 600... 800 names there, I'd have to choose a color and create the name each time.  I'm looking at possibilities with it.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG5v3VWTnLGjVw6qAbuET_nkMM1U3gbUrafcFF-b1GGww%40mail.gmail.com.

[Keith Andersen]

I do not understand this:

I'm still not too sure about Column E but... it might work.  Validation would be huge, I could like 600... 800 names there, I'd have to choose a color and create the name each time.  I'm looking at possibilities with it.

Can you explain this out.

:

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAD3-hYPeDzH0HV8R8%3DA3x3vef0JvegGB%2BAm7c2OCZe%3DLWN8UEA%40mail.gmail.com.

[Brent Guttmann]

I gave you a working solution for multiple results? Here is an example: https://docs.google.com/spreadsheets/d/1mipi6oIZ4MTWpkLDslEOlmpuDjlTNsPhvqJUAOnFZTA/edit?usp=sharing

[Narc Angel]

They are so many contributors that I'm getting lost in which version someone is referring to.  Keith I'll try to use the chat directly in the sheet I think you are talking about.