Thursday, August 16, 2012

How to set default sheet in a google spreadsheet

If you are wondering how to set any sheet as your default sheet when you open a Google Spreadsheet in Google Drive, that is whenever you open your spreadsheet should load and display that default sheet, then here is the solution:

If you want to do it without using a script then just drag that sheet to the left most side sheet, for example see the screenshot below:





Here, Sheet2 is on the extreme left most side, so this will be loaded and displayed as the default sheet when you open this spreadsheet.


And now let us achieve the same using scripts. So if you want to open a specific sheet without changing the order of the sheets in the panel the following script will help you to do so:

function onOpen()
{  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = spreadsheet.getSheetByName("Sheet3");
  //here "Sheet3" will be active sheet.
  spreadsheet.setActiveSheet(sheetname);
};


Just add the function below into the script and change "Sheet3" to whatever sheet you want to open and then open your spreadsheet you will notice that by doing so you will make "yoursheetname" open by default.

If you are not familiar with writing scripts and don't know where to start from then have a look at the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 


And if you are sending the link to some to open then spreadsheet, then you can also directly send the URL of the sheet with the ending " #gid=2" , here 2 is Id of "Sheet2", So the link will open the spreadsheet directly with the Sheet2 as active sheet regardless of the order of the sheets by which it has been saved.

I hope this will make you aware of different ways to set the default sheet to be opened at the time of opening of the spreadsheet.

7 comments:

  1. Unfortunately, your "without a script" solution does not work. I keep my work hours in a spreadsheet and the latest year is always the leftmost sheet--but it's not the default. It always opens to 2012 (which is neither the leftmost or rightmost sheet). Kind of odd really. Adding a script as you suggested does work, though--thanks.

    ReplyDelete
  2. I just bookmark particular sheets and update them if another sheet later takes precedence.

    ReplyDelete
  3. This solution doesn't work. Mine loaded the left-most sheet until I got to mix 6th sheet and it stopped. I hope the developers update it to load the last sheet used in the future.

    ReplyDelete
  4. Like olaf noted, just go to the sheet you want and book mark the URL. It is to that sheet.

    ReplyDelete
  5. bookmarking the URL for a particular sheet only works if you use book marks... I open the docs from my drive and am clueless on scripts... should be an easier interface like in the sheet options pop up... 'make default'... how hard is it to program this?

    ReplyDelete
  6. You can create a desktop shortcut to any URL by dragging the icon within your browser's address bar to your desktop, then renaming it.

    ReplyDelete
  7. Since GoogleSheets is based on Excel, that is a function that should have been kept. In Excel, the document opens to the sheet that was open whne the document was last saved. They should fix GoogleSheets to do the same.

    ReplyDelete