Google script – use every time the newest sheet

I want to create a script which should use everytime the newest sheet.

The sheet gets updates from another group and I want to create an email alert, if I have something to do. Everytime this spreadsheet get an update from another team, they create a new sheet with name of the date (TT.MM) and make the old one invisble. The problem is that this isn’t a continous time, this is sporadically. It is simply not a recurring period in which the new sheet is created, at times it is created every day, at times once a week. Therefore I can’t call it by the name of the sheet.

If i use the function

  var sheet = spreadsheet.getActiveSheet();

it doesn’t work, because the newest sheet isn’t the active one.

The newest sheet is added with a simple click on the plus (add sheet), the old one is simply hidden.

This is my beginnig of the code, the code works, but I need instead of the spreadsheet.getSheetByName another function, which use the newest sheet or the sheet on the first place of the legend

Now I had got the .getSheetbyName Function but this name is changing every time and I don’t know whats the next name.

function EmailAlert() {
  //Variable für das Google Sheet
  var spreadsheet = SpreadsheetApp.openById("18lvOFMBOIlXRrJcGbNZDabtH5tVNDvgMXgOaSrAIhpw");
  var sheet = spreadsheet.getSheetByName('15.03.'); 

Thanks Launga

Update 17 March: these are the sheets

Answer

Actually you can’t actually tell what the last hidden sheet is by it’s position in the getSheets() list because other users can move them around. So your going to have to use their sheet name. What does TT.MM stand for?

This functions finds all sheets of the form xx.yy where xx assumes atleast 1 digit and can be any digit or could be 2 digits. Same is true for yy. The function returns the string for sheet name with the largest number the yy is to the left of the decimal point. So a comparison of sheet names like 01.03,02.03,03.03,31.02 will return sheetname = “03.03”.

This doesn’t require you to have leading zeroes but it allows it.

function getHighestNumberedPage() {
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets().map((sh) => {
    let name = sh.getName();
    let rev = name.split(".").reverse().join(".");
    let v = rev.match(/d{1,2}.d{1,2}/g);
    if (v && v.length == 1) {
      return sh.getName();
    } else {
      return '';
    }
  }).filter((n) => { return n }).sort((a, b) => {
    return parseInt(a.split(".").reverse().join(".")) - parseInt(b.split(".").reverse().join("."));
  });
  Logger.log(shts[shts.length - 1]);
  return shts[shts.length - 1];
}