Google Sheets - Name sheet based on value selected from range

Question

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??

Answer 1

It will make unnecessary complexity into a function if you have to shift a day from original day and week counts in common sense... which is very much not recommended. And at the same time it may cause unexpected issues when working together with other functions.

but anyway, the following script will create a list of sheet names in the format you want, and set the sheet names with those generated names in ascending order.

class getDate{} is a object class to help modify the Date() object.

createSheetNames(year) is a function which accept an input as to indicate which year of sheet names are you working with, this function will look for the first Monday of the given year to begin with, and return an array of results with a sheet name for each week.

setSheets() is a function which will iterate all sheets you have in your working spreadsheet, and rename each sheet according to the sheet names returned by the last function.

class getDate {
  constructor (input) {
    if(!!input) this.date = new Date(input);
    else this.date = new Date();
  }
  get year() { return this.date.getFullYear(); }
  get month() { return this.date.getMonth() + 1; }
  get week() { return this.date.getDay(); }
  get day() { return this.date.getDate(); }
  calDay(num) {
    if(isNaN(num)) throw new Error('requires integer.');
    return new getDate(this.date.setDate(this.day + Math.floor(num)));
  }
}

function createSheetNames(year) {
  const results = [];
  const date = new getDate(`${year}-01-01`);
  const isInt = (num) => num === Math.floor(num);
  while (date.week !== 1) date.calDay(+1);
  for (i=0;i<365+7;i++) {
    if (new getDate(date.date).calDay(-6).year > year) break; // change -6 of this line to 0 will remove the last week of a year if that week ends inside the next year.
    const calWeek = (i + 1)/7;
    if (isInt(calWeek)) {
      const sheetName = `Week ${calWeek}: ${date.calDay(-6).month}/${date.day} ~ ${date.calDay(+6).month}/${date.day}`;
      results.push(sheetName);
    }
    date.calDay(+1)
  }
  return results;
}

function setSheets() {
  const sheetNames = createSheetNames(2023); // enter the year of sheet name you need here.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  for (const [i,sheet] of sheets.entries()) {
    if (i > sheetNames.length - 1) break;
    sheet.setName(sheetNames[i]);
  }
}

After putting these codes into your spreadsheet, run the setSheets() function for one time, it will replace all your sheet names.

---

2023-01-11 update:

Fixed a couple of typos that may lead to unexpected results, and removed some variables that are not necessary in this using case which may? enchance execution speed very slightly.

Answer 2

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.

Answer 3

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);
  });
}