One of the most needed features of tables is creating dependent drop-down lists, like these:
List 1 is the main for list 2. List 2 is the main for lists 3 and 4. I’ve already supposed one possible solution. And here’s why I suggest you this one, too.
- Limit on 500 items for the list is not the case now: cascade lists are based on a range, not an array.
- The speed of the script is improved.
- Settings of lists are easier: the code of settings is created by the formula.
The main challenge was: speed. Here’re 2 versions of the same script in comparison:
V0: reading data from the sheet. V1: reading data from script memory
The result: 4 times faster!
Solution: 2 files:
- Main: create the reference and set the script (copy sample file and script)
- Work file: import the reference, use drop-down lists (copy sample file and script).
Install Cascade lists
Create a library:
Main File Menu Tools > Script Editor (in new tab)... Menu File > Manage Versions > Save new version
Save library key:
Main File Menu Tools > Script Editor (in new tab)... Menu File > Project properties > Copy Project key
You’ll get a key like this:
Install the library:
Work File Menu Tools > Script Editor (in new tab)... Menu Resources > Libraries > Paste Project key
Set Cascade lists
The script of the work file has settings in it. All these settings may be created on the sheet “Script” of the main file. Sample settings for 1 dependency are as follows:
// ; obj_dv.sheets = ['Expences']; obj_dv.dataRangeA1 = "'Imp_Database'!C4:K14"; obj_dv.rowStart = 3; obj_dv.colStart = 2; obj_dv.colEnd = 6; obj_dv.sheetRefName = 'Imp_Database'; obj_dv.refColStart = 3; obj_dv.refRowStart = 4; obj_dv.refCols = 9; obj_dv.refRows = 11; var res2 = runValidation2d_(e, obj_dv); Logger.log(res2);
You set only yellow cells:
When you enter the yellow cells, copy the generated code and paste it to the script of the Work file.
That’s the minimum info needed to install the script. Please post your questions on the comments. I’ll be back to add more details to this post.