0
136
ViewsFrom a named range, select one column but not the header row
I can make this work for a whole column, but if I have a NamedRange, I'll call food as follows.
| Vegetables | Fruit | Meat |
|---|---|---|
| Carrot | Apple | Pork |
| Potato | Orange | Chicken |
| Turnip | Pear | Beef |
Then I want to grab the column below the first row and then add that to a drop-down.
I know how to populate the drop-down, and I can grab a whole column using Map, but how do I get just the items below the first row.
I've created the following working script, but when you Logger.log (oneSet)
I get the whole column, including the heading. Instead, I want the relevant column of data without the heading.
function selectCol(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var foodList = ss.getRangeByName("Food").getValues();
var oneSet = foodList.map(oneCol);
function oneCol(one){
return one[2]
};
Logger.log(oneSet);
// [0] returns [Vegetables, Carrot, Potato, Turnip]
// [1] returns [Fruits, Apple, Orange, Pear]
// [2] returns [Meats, Pork, Chicken, Beef]
// So what I want is:
// [0] to return [Carrot, Potato, Turnip]
// [1] to return [Apple, Orange, Pear]
// [2] to return [Pork, Chicken, Beef]
}
·
Juan Pablo Isaza
1 answers
Answer question0
This function builds all three and places them right below each list.
function makeADropDown() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const vs = sh.getDataRange().getDisplayValues();
[...Array.from(Array(3).keys())].forEach(idx => {
let r =SpreadsheetApp.newDataValidation().requireValueInRange(sh.getRange(2,idx + 1,sh.getLastRow()-1,1)).build();
sh.getRange(sh.getLastRow() + 1,idx + 1).setDataValidation(r);
})
}
This line just creates and array like [0,1,2] to run through the loop 3 times.
[...Array.from(Array(3).keys())]
·
Juan Pablo Isaza
Report
Answer question