Dear Google App Script Developers,
I'm looking for a feature which will allow me to lookup data in one from another Spreadsheet. In general the Validation-Feature of Google Spreadsheet works fine, but it only can lookup Data within the same Spreadsheets (from one to another Sheet). I would need something similar, but link the list of Data in a Range or Table from another spreadsheet.
The other spreadsheet could be identified thru the Spreadsheet ID. An app is not required, a script would be ok, in which I then could define range of data to be looked up as well as spreadsheet ID.
A few important points:
- IMPORTRANGE doesn't work, as it requires user authentication/approval in each new Spreadsheet. Since I'm working template based, this wouldn't be suitable. So even in a new Spreadsheet, the Script should not ask the user again to improve the link/data lookup.
- The data lookup should either be possible in a single cell, or in a cell range (e.g. full column in a table).
- If the user clicks in the cell where a data lookup is applied, the user should be able to search in the lookup by a specific value or string
- The data lookup should not only be apply-able as a drill-down/selection box (like it works for the validation feature), but also as formula. For example I'd like to apply a formula in a table column, which defaults me the price from a Product which was selected in a column next to it. Both Product ID and Price are columns in a separate Spreadsheet.
I'm looking forward to your suggestions for approach and budget.