Google Sheets - Name sheet based on value selected from range-CodePudding

I have a range of week numbers and their corresponding dates in a column J. Week 1: 1/2 - 1/8, and so on. This is populated by:

=ArrayFormula(
LAMBDA(FIRSTOFYEAR,
 LAMBDA(DATES,
  LAMBDA(WEEKS,
   LAMBDA(DATA,
    BYROW(ARRAY_CONSTRAIN(DATA,MAX(WEEKS),4),LAMBDA(ROW,JOIN(" ",ROW)))
   )(TO_TEXT(QUERY(QUERY({WEEKS,"Week "&WEEKS&":",DATES,DATES},"SELECT Col2,MIN(Col3),'~',MAX(Col4),Col1 GROUP BY Col1,Col2",0),"OFFSET 1 FORMAT Col2'm/d',Col4'm/d'",0)))
  )(WEEKNUM(DATES))
 )(BYROW(SEQUENCE(365,1,0),LAMBDA(NUM,FIRSTOFYEAR NUM)))
)(DATE(2023,1,1))
)

I also have a script to get the number of the current sheet:

function getSheetNum() {
  const sss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (const [index,sheet] of sheets.entries()) {
    if(sheet.getName() === sss.getActiveSheet().getName()) return index;
  }
}

What I am trying to do, and not doing correctly yet, is to have a script that names the sheet based on the current week. So, the first sheet is sheet 1 and thus will be named Week 1: 1/2 - 1/8. The second sheet is sheet 2 and so it'll be named Week 2: 1/9 - 1/15, and so on. My current, non-working script is below:

function nameSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var week = getSheetNum();
  var oldName = sheet.getName();
  var newName = week.getValue();
  if (newName.toString().length>0 && newName !== oldName) {
    sheet.setName(newName);
  }
}

What am I doing wrong??

CodePudding user response：

Since you already have the desired sheet names in a spreadsheet range, try renaming sheets to those names, like this:

function nameSheetsByWeek() {
  const ss = SpreadsheetApp.getActive();
  const weekNames = ss.getRange('Sheet1!A2:A').getDisplayValues().flat();
  ss.getSheets().forEach((sheet, index) => {
    const newSheetName = weekNames[index - 1];
    if (!index || !newSheetName) {
      return;
    }
    sheet.setName(newSheetName);
  });
}

CodePudding user response：

You can try this code, If you will start renaming it from Sheet 1 forward although you should set the Activesheet() on the First sheet.:

function renameSheets() {
  const ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  const weekNames = ss.getActiveSheet().getRange("J1:J").getDisplayValues().flat();
    for (i=0; i<sheets.length; i  ) {
      if(sheets[i].getName() != weekNames[i]){
        sheets[i].setName(weekNames[i]);
      }    
  }
}

Result:

Alternatively, you can use this code if you want to have a main/fixed sheet which you will not rename:

function renameSheets2() {
  const ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  const weekNames = ss.getRange("MAIN!J1:J").getDisplayValues().flat();
    for (i=1; i<sheets.length; i  ) {
      if(sheets[i].getName() != weekNames[i-1]){
        sheets[i].setName(weekNames[i-1]);
      }    
  }
}

Result:

For example, I have named my First sheet as Main.