CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
AdrianL
AdrianL

Reputation: 31

Google Apps Spreadsheet open specific sheet based on current date (Month)

I am currently creating a spreadsheet in Google Apps that will be used to log client attendance. The spreadsheet contains 12 sheets. Each sheet represents a month of the year so sheet one represents January, sheet two represents February etc.

I am trying to create a Google Apps Script using the On_Open function to automatically open the relevant sheet based on the current date.

For example if the current date where 18/02/2013 then the Spreadsheet would automatically open with Sheet 2 (February) in focus.

I think I am correct in using SpreadsheetApp.setActiveSheet but I don't know how I would capture the date specifically the month with Google App Script.

I am assuming the script would flow a bit like the following?

If Date = 18/02/2013 then SpreadsheetApp.setActiveSheet 2

Any help would be gratefully received.

Upvotes: 3

Views: 4564

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

To get a month number, 0 - 11:

var month = (new Date()).getMonth();

Assuming you have { Sheet1 .. Sheet12 } representing January - December, then this will work:

/**
 * Selects a monthly sheet
 */
function onOpen() {
  var month = (new Date()).getMonth();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[month];
  ss.setActiveSheet(sheet);
};

Alternatively, you could name the sheets by month, and find the appropriate monthly sheet by name. This would allow you to have other non-Monthly sheets in the Spreadsheet:

/**
 * Selects a monthly sheet
 */
function onOpen() {
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  ss.setActiveSheet(sheet);
};

Upvotes: 4

Related Questions