How to use date variable for getSheetByName?

user_19518970 0
Viewed 33

I'm trying to create a simple button on a GGL sheet dashboard to jump to another sheet with the current date in their names (Go to today) and in the format of "mmmm d", such as "July 24".

I almost have no knowledge about this so of course the script do not work, and I think because the variable is not compatible with getSheetByName? Here's the current script:

function totodaysheet() {
var spreadsheet = SpreadsheetApp.getActive();
var date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "mmmm d");
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(date), true);
};

Would you be able to advise on this?

google-apps-script google-sheets
1 Answers

Modification points:

  • If you want to use the date format like July 24, I think that mmmm d should be MMMM d.
  • If the sheet name is not found, an error occurs.

When these points are reflected in your script, it becomes as follows.

Modified script:

function totodaysheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  var date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMMM d");
  var sheet = spreadsheet.getSheetByName(date);
  if (!sheet) return
  spreadsheet.setActiveSheet(sheet, true);
}

Note:

  • For example, if today is July 1, 2022, MMMM d returns July 1. If you want to retrieve July 01, please use MMMM dd.

References:

user_71086530
Related