Rename a tab with todays date "MM/DD/YYYY" and then another one with "MM/DD/YYYY-1&qu-CodePudding

i got a script that create a new tab and changes the name to today's date. But if a run the script again, shows up an error bc the tabs name already exist. Anyway I can rename the first with today date and the rest with a "- 1" or "- 2". This is what I have at the moment that just create a new tab and changes the name to today's,

function changeDate() { //Add sheet and name with todays date
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:AO102').activate();
  spreadsheet.insertSheet(1);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = ss.getSpreadsheetTimeZone();
  var sheets = ss.getSheets();
  var date = Utilities.formatDate(new Date(), tz, 'MM-dd-yyyy');
  sheets[1].setName(date);

CodePudding user response：

Description

Here is an example script of how to rename sheets with a suffix number that avoids renaming a sheet with a name that already exists.

Code.gs

function insertSheet() {
  try {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let tz = ss.getSpreadsheetTimeZone();
    let name = Utilities.formatDate(new Date(), tz, 'MM-dd-yyyy');
    let sheets = ss.getSheets();
    // get sheets that include today's date at the start of the name
    sheets = sheets.filter( sheet => sheet.getName().indexOf(name) === 0 );
    if( sheets.length > 0 ) {
      let min = 0;
      // get the lowest number suffix (i.e. -3, -2, -1)
      sheets.forEach( sheet => {
          let i = parseInt(sheet.getName().slice(name.length));
          min = i < min ? i : min;
        }
      );
      // now rename starting from lowest number (i.e. -3)
      while( min < 1 ) {
        let rename = min === 0 ? name : name min;
        let sheet = ss.getSheetByName(rename);
        if( sheet ) {
          sheet.setName(name (min-1));
        }
        min  ;
      }
    }
    let sheet = ss.insertSheet(1);
    sheet.setName(name);
  }
  catch(err) {
    console.log(err);
  }
}

References

CodePudding user response：

You can use a recursive try...catch loop to setName().

Replace

sheets[1].setName(date);

with

const tcloop = ((i) => (func) => {
  try {
    func(i);
  } catch (e) {
      i;
    tcloop(func);
  }
})(0);
tcloop((i)=>sheets[1].setName(`${date}${i > 0 ? -i : ""}`))