Thursday, June 20, 2013

Google Spreadsheet Script to go to Next Sheet

Google Spreadsheet Script to go to Next Sheet


Question:

(by Aaron Tawil)




So I am trying to create a click-through by assigning a script to a 'next' button that allows the user to click on the button and it will move them from the current sheet to the next sheet.

I get this error when I click the button: Cannot find method (class)(class)setActiveSheet(number)

this is my code:




function nextMonth(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var id = sheet.getSheetId();
  if(id == 11){
    id = 0;
  }
  else{
    id += 1;
  }
  sheet.setActiveSheet(id);
}


Solution:

Now, have a look at the following script:





function switchToNextSheet()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var id = ss.getSheetId();
  var sheets =  ss.getSheets();
  
  var ids =[];
  var currentid = 0;
  for( var i=0;i<sheets.length;i++)
  {
    ids[i] = sheets[i].getSheetId();
    if( id == ids[i] )
      currentid = i;
  }
  
  var nextid = 0;
  
  if( currentid == sheets.length-1 )
    nextid = ids[0];
  else
    nextid = currentid+1;
  
  ss.setActiveSheet(sheets[nextid]);
}


When you run the above function, you will move to the next sheet of your current active sheet.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:


If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


2 comments:

  1. Hey, if you are still checking this page, would you have some code to skip a sheet that is hidden by a hidesheet script? This is for a decision tree type sheet.

    ReplyDelete
  2. Hey, if you are still checking this page, would you have some code to skip a sheet that is hidden by a hidesheet script? This is for a decision tree type sheet.

    ReplyDelete