Help! I am new to this and have nearly no idea what I am doing.
I am designing a step-by-step spreadsheet for teaching students where each step appears in the worksheets of the spreadsheet.
I have inserted two drawings, a left arrow and a right arrow. I want to assign an app script to the left arrow to allow the user to switch to the previous worksheet/step and the right arrow to allow the user to switch to the next worksheet/step.
I have added 50 worksheets to the spreadsheet that are named incrementally starting from number 1 and ending at 50, i.e. the name of the worksheet represented by the tab that is furthest left is "1" and "50" for the worksheet that is furthest right.
Am trying to accomplish the following:
Write a function that will find the currently active sheet then add 1 to it to move to the adjacent work sheet, ie if on step 5 worksheet name is "5" script adds 1 to the number 5 from current worksheet name to get "6" which is then used to activates sheet named "6". I would then assign this to the drawing of the right arrow.
Write a function that will do the same as 1. above, but subtract 1 from the currently active sheet, ie. in above example it would activate worksheet "4" to the left of currently active worksheet called "5". This would be assigned to the drawing of the left arrow.
3a. When executing 1 or 2 above, the only unhidden sheet would be the current one, ie. "5" in above examples. The function first unhides adjacent sheet, ie. either worksheet "4" or "6" in above examples and would also hide the currently active sheet, ie. worksheet "5" from above so that there is only ever one worksheet active at anytime (to prevent users from navigating by clicking worksheet tabs at bottom of UI).
3b. Ideally, I would have it hide any active sheets except for the one that was just navigated to, ie. "4" or "6" above and not only would it hide "5" but would hide any active sheet (this is just to ensure that it there can never be multiple tab open, only the current one).
For what it is worth the following is what I have come up with, but it is of course a total fail.
function SHEETNAME() {
let activeSheet = SpreadsheetApp.getActiveSheet();
return activeSheet.getName();
}
function NextSheet(SHEETNAME) {
var sheet = SpreadsheetApp.getActive().getSheetByName(SHEETNAME + 1);
sheet.activate();
}
function PreviousSheet(SHEETNAME) {
var sheet = SpreadsheetApp.getActive().getSheetByName(SHEETNAME - 1);
sheet.activate();
}
And for hiding all sheets except active one (3b. above), I found this but don't know how to integrate with other code.
function main(){
hideAllSheetsExcept(SHEETNAME);
}
function hideAllSheetsExcept(sheetName) {
var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();
for(var i =0;i<sheets.length;i++){
Logger.log(i);
if(sheets[i].getName()!=sheetName){
sheets[i].hideSheet();
}
}
}
THANKS ANY HELP U CAN OFFER :-)