I am stuck. I have a spreadsheet with a dropdown list containing the names of the sheets. Is it possible within a function to set the active spreadsheet to match the dropdown selection
function getNextCell() {
// Active sheet should reflect dropdown selection
// to find first empty cell of that sheet by dropdown
var sheetTo = SpreadsheetApp.getActiveSheet();
var myValues = sheetTo.getRange('A:A').getValues();
var i;
for (i = 0; i < myValues.length; i++) {
if (myValues[i][0] === '')
return i + 1;
}
return i + 1;
}
Sample sheet updated to show current work: https://docs.google.com/spreadsheets/d/1befqsGQvbPfn0XTGrygLOGcrUIMrICUagJVH0S-2rDw/edit?usp=sharing
I should mention I tried an array but that didn't work for me. I may have been utilizing it wrong but am not sure.
function onEdit(e) {
const sh = e.range.getSheet();
if(sh.getName() == "Dropdown" && e.range.columnStart == 8 && e.value) {
e.source.getSheetByName(e.value).activate();
}
}
You can also create the list of all sheet names for the datavalidation in column H with this:
function makeColumnDropDown(col = 8) {
const ss = SpreadsheetApp.getActive();
const list = ss.getSheets().map(sh => sh.getName());
const r = SpreadsheetApp.newDataValidation().requireValueInList(list);
const sh = ss.getSheetByName("Dropdown");
sh.getRange(1,col,100).setDataValidation(r);
}
If you wish to leave some out you may also add a .filter method to the third line and exclude a list of file names. Just ask I show you.
function getColumnHeight(col, sh, ss) {
var ss = ss || SpreadsheetApp.getActive();
var sh = sh || ss.getActiveSheet();
var col = col || sh.getActiveCell().getColumn();
var rcA = [];
if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
let s = 0;
for (let i = 0; i < rcA.length; i++) {
if (rcA[i].toString().length == 0) {
s++;
} else {
break;
}
}
return rcA.length - s;
}