I want to create a spreadsheet where users can type an employee name into a cell on Sheet A, and they will receive autocomplete suggestions based on a list of employee names from Sheet B. This seems to be doable through data validation options but I want to do it through an Apps Script function. This seems to be straight forward but I'm only seeing how to do autocomplete projects on other Google platforms like WebApp.

This page has information about autocomplete but it doesn't have any examples about how that is used within Google Sheets specifically. Can this be done through 1D and 2D arrays within Google Sheets only?

CodePudding user response：

Short answer: It's not possible to add an autocomplete to Google Sheets cells by using Google Apps Script.

The link about autocomplete included in the question is about Text Input widget from the Card Service. This service can only be used on Google Workspace Add-ons, not in "normal" scripts (https://developers.google.com/apps-script/guides/sheets).

Google Apps Script has simple and installable triggers but none of them could be used to do implement an autocomplete feature as can be done in Google Workspace Add-ons and in web applications.

You might use data validation and it's possible to set the data validations options by using Google Apps Script. Also adding the autocomplete entries in cells from the same column. You might hide the rows if you don't want to display them to the users.