sheet name from cell (formatting issue)

Question

I have been tasked with creating reservation sheets for a restaurant. The sheets are for internal use only so the only people with access to these sheets are the staff in the reception, reservations and marketing.

That said, it's a time consuming task to edit these sheets and make sure the data is correct. The idea is to have one workbook for each month of the year and then one sheet each per day of the month.

The request is to have the sheets be named in the following manner:

The red marking for Sundays is entirely optional unless there is an easy way to implement this.

I am not an expert at scripting but I have spent several hours trying to find a solution to this. The closest I've come so far is the script below:

function onEdit(e) {
if(e.range.getA1Notation() !== 'C1') return;
    var s = e.source.getActiveSheet()
            s.setName(s.getRange('C1')
                .getValue())
}

The function works in as much as it changes the name of the sheet, however, the format of the name of the sheet shows up in the following way:

Fri May 01 2020 00:00:00 GMT+0100 (BST)

I'm sure there is a way to specify the format of the name displayed, I just can't figure it out myself.

Desired format:

Fri 01 May

All help appreciated.

Answer 1

Issue resolved.

Instead of formatting the cell as a date, I formatted it as plain text as suggested above and then in the cell I used the following formula:

=TEXT(Date!A24,"ddd dd mmmm")

Thank you.

Answer 2

The following code will create sheets based on the number of days in a given month.

Code

// globals
var JAN = "January";
var FEB = "February";
var MAR = "March";
var APR = "April";
var MAY = "May";
var JUN = "June";
var JUL = "July";
var AUG = "August";
var SEP = "September";
var OCT = "October";
var NOV = "November";
var DEC = "December";
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Create sheets")
    .addItem(JAN, "createSheetsJan")
    .addItem(FEB, "createSheetsFeb")
    .addItem(MAR, "createSheetsMar")
    .addItem(APR, "createSheetsApr")
    .addItem(MAY, "createSheetsMay")
    .addItem(JUN, "createSheetsJun")
    .addItem(JUL, "createSheetsJul")
    .addItem(AUG, "createSheetsAug")
    .addItem(SEP, "createSheetsSep")
    .addItem(OCT, "createSheetsOct")
    .addItem(NOV, "createSheetsNov")
    .addItem(DEC, "createSheetsDec")
    .addSeparator()
    .addItem("Delete sheets", "deleteSheets")
    .addToUi();    
}

function createSheetsJan() {
  createMonth(JAN);
}

function createSheetsFeb() {
  createMonth(FEB);
}

function createSheetsMar() {
  createMonth(MAR);
}

function createSheetsApr() {
  createMonth(APR);
}

function createSheetsMay() {
  createMonth(MAY);
}

function createSheetsJun() {
  createMonth(JUN);
}

function createSheetsJul() {
  createMonth(JUL);
}

function createSheetsAug() {
  createMonth(AUG);
}

function createSheetsSep() {
  createMonth(SEP);
}

function createSheetsOct() {
  createMonth(OCT);
}

function createSheetsNov() {
  createMonth(NOV);
}

function createSheetsDec() {
  createMonth(DEC);
}

function deleteSheets() {
  var shs = ss.getSheets();
  shs.forEach( function (s, i) {
    i > 0 ? ss.deleteSheet(shs[i]) : null;
  });
}    

function createMonth(month) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tmp = ss.getSheetByName('Sheet1');
  var tmz = ss.getSpreadsheetTimeZone();
  var cnt = 1;
  var year = new Date().getYear();

  //https://stackoverflow.com/a/13566675/1536038
  month = new Date(Date.parse(month + " 1," + year)).getMonth();
  var date = new Date(year, month, 1);
  while (date.getMonth() === month) {
    ss.insertSheet(Utilities.formatDate(date, tmz, "EEE dd MMM"), cnt, {template: tmp});
    date.setDate(date.getDate() + 1);
    cnt++;
  }
}

Explained

• First the code create global variables that can be used throughout the script.
• Then it will create a new menu option, with all months added (plus an extra option to delete all sheets).
• Once a selection is made from the menu, the script will plough through the month, whilst creating the sheets in the desired naming format, see the createMonth function
• you can assign a template sheet if you want to

Add this script to your script editor (Tools > Script editor...) and press save. Now press the bug button to authenticate the script in your environment. Window appears, telling you the script is unsave, but scroll down and click the link to the left and approve the script nonetheless. Now the onOpen function will be executed and a new menu item will appear.

Hope this helps and good luck creating those sheets !!