1

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: enter image description here

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.

  • did you try to format C1 as Plain Text? – user0 Jul 6 at 11:17
  • Thank you for the suggestion. Formatting C1 as Plain Text works as far as the name of the tab shows up exactly as shown in the cell. However, the cell is formatted as a date which is automatically updated based on a reference sheet. By formatting the cell as plain text, the date reference no longer works. I'll attach an example sheet to make things easier. – JonatanOlsson Jul 7 at 2:03
  • Hi Jonatan, wouldn't like to have a script that makes all sheets in one go? – Jacob Jan Tuinstra Jul 7 at 6:46
  • Hi @JacobJanTuinstra, That would be absolutely fabulous, but as I said, I'm not very familiar with scripting and the solution I found is working albeit not perfectly. Do you have such a script already? – JonatanOlsson Jul 7 at 22:17
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.

1

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

  • Thank you, the script works so far as it creates the sheets I need. However, and I don't know if it's me doing something wrong or if there's an error in the script somewhere, but the weekdays on the tab names are all one day off. The 1st of January 2020 is actually a Wednesday but the script names the tab Tue 01 Jan. Link to my test-sheet below. docs.google.com/spreadsheets/d/… – JonatanOlsson Aug 21 at 0:04
  • I see what's going on now though, the script is getting the days of the month from 2019 not 2020. How can I change that? – JonatanOlsson Aug 21 at 0:13
  • @JonatanOlsson Change the variable year into the year you want: var year = 2020. That should do the trick. Didn't notice the 2020 requirement in your post.... – Jacob Jan Tuinstra Aug 22 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.