I have a spreadsheet displaying information on two monitors. The file has anywhere from 5 to 30 tabs. I have been trying to get the following script with a time-driven trigger to move from one sheet tab to the next every minute, however I have two problems:
1) - I need the loop to skip four tabs (they have fixed names) and I currently can't figure out a workable solution, and
2) - the time driven trigger of 1 minute is not doing anything, not working.
Here are two scripts I've been testing and tweeking to see if I find the one that works:
Script 1:
function MoveNext() {
var spreadsheet = SpreadsheetApp.getActive();
var nextSheetIndex = spreadsheet.getActiveSheet().getIndex() + 1;
if (nextSheetIndex > spreadsheet.getSheets().length) { nextSheetIndex = 1; }
spreadsheet.setActiveSheet(spreadsheet.getSheets()[nextSheetIndex - 1],true);
And Script 2: this one aims at skipping the four tabs I don't want to loop:
var ss = SpreadsheetApp.getActive();
var sheets = ss.getSheets();
for (i = 0; i < sheets.length; i++) {
switch (sheets[i].getSheetName()) {
case "T1":
case "T0":
case "Summary Panel":
case "Flight Info":
case "Template":
break;
default:
var nextSheetIndex = ss.getActiveSheet().getIndex() + 1;
if (nextSheetIndex > ss.getSheets().length) {
nextSheetIndex = 1;
}
ss.setActiveSheet(ss.getSheets()[nextSheetIndex - 1], true);
You'll have to add your timer code yourself, but here is an example of how to move to the next "approved" sheet.
function MoveNext() {
var sheetsToSkip = ["Sheet3", "Sheet4", "Sheet7"];
var thisBook = SpreadsheetApp.getActive();
var sheetCount = thisBook.getNumSheets();
var thisSheet = SpreadsheetApp.getActiveSheet();
var thisSheetName = thisSheet.getName();
var thisSheetIndex = thisSheet.getIndex() - 1; //subtract one to get the array index
var allSheets = thisBook.getSheets();
var i = thisSheetIndex;
var notDone = true;
while (notDone) {
if (i == (sheetCount-1)) {
i = 0;
} else {
i++;
}
Logger.log('next sheet index is ' + i);
var nextName = allSheets[i].getName();
if (sheetsToSkip.indexOf(nextName) == -1) {
var nextSheet = allSheets[i];
Logger.log('next active sheet should be ' + nextSheet.getName());
thisBook.setActiveSheet(nextSheet);
notDone = false;
}
}
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments