CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-apps
Leonardo
Leonardo

Reputation: 2504

Custom Autocomplete in Google spreadsheet app

My question is the following, is there any tutorial about how to make that when you begin typing on a google spreadsheet cell, the fields belonging to a custom list that contains (anywhere on the text) the typed characters appears in a drop down list.

I do not know a lot of Javascript, so if there's any book or reference where I can found something similar it would be really appreciated.

Upvotes: 1

Views: 3312

Answers (1)

just.a.guy
just.a.guy

Reputation: 300

I am new, and I don't know of any method for allowing the selection of valid values to be position based upon what the user starts typing (into a cell).

I know this cannot be done within a script. All scripts run after the user has made all of their changes (to a cell).

You may already know about a drop-down validation list using defined ranges. This process puts a small down arrow adjacent to the right-edge of the box. It allows the user to select from a pre-defined list. It does not prevent the end user from entering invalid values. It just marks their value as invalid if it does not agree with one of the entries in the list. And by pressing the down-arrow, the list of valid values is displayed. The user can select an entry in the list by clicking on it.

I learned about this validation from the documentation. If that is all you need then you can stop here.

To me the interesting thing about this validation method is:

  1. you can define the list and give it a name. The invalid error message will include the name. You can use the name to indicate the type of validation error.

  2. The list can be contained on a different sheet other than were it is referenced. The sheet that contains the list can be hidden.

  3. If you add a row to the list area that is not the bottom row, (insert below) the spreadsheet engine will automatically adjust the size of the defined range. The new values entered in the list appear in the updated drop-down list (in the order that they appear in the range definition).

What I have not tested is expanding the list from a script. I don't know if the spreadsheet engine will perform the same range modification if the change comes from a script. I would hope that it would.

Also I don't know if you make a changes to a hidden sheet from a script, then will the hidden sheet remain hidden. I know when you do a "copy sheet" function from a script, the source sheet is temporarily made current, and is therefore no longer hidden.

Hope this helps.

Upvotes: 1

Related Questions