Google-sheets – sheet name from cell (formatting issue)

google sheetsgoogle-apps-script

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.

Best Answer

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.

From the question

Is it possible for creating the subset (filtered by Month) in a way that if we were to make changes in that subset view, those changes would cascade back to the Master. Would QUERY work? Or do I have to resort to a script. Or it is not possible.

Short answer

Google Sheets doesn't have a built-in feature that syncs two spreadsheets so you have to use something that extends it.

Explanation

Built-in functions like QUERY are not able to make make changes to another range. They could only return a value or an array of values. The same apply to custom functions.

Below is included a very simple Google Apps Script code example that exports the value of an edited cell to a cell of another spreadsheet. That code could be added to two spreadsheets to export the edited cells values to each other in order to have a "two way sync" between those spreadsheets.

It's purpose is to show that it's possible to sync two spreadsheets by using Google Apps Script. In order to make it work to sync a master sheet with filtered sheet to logic to find which rows are the match in the other file should be added. Usually this is done using an unique ID.

Broad instructions

Copy the code to script projects bounded to the each spreadsheet and update the targetID and targetSheetName variable definition code lines (lines 1 and 2).
Add a On Edit installable triggers to each script project.

Code

var targetID = 'spreadsheet-id';
var targetSheetName = 'Sheet1';

function onEdit(e) {
  // Get the event object properties
  var range = e.range;
  var value = e.value;
  //Get the cell position 
  var row = range.getRowIndex();
  var column = range.getColumnIndex();
  exportValue(row,column,value)
}

function exportValue(row,column,value) {
  var ss = SpreadsheetApp.openById(targetID);
  var s = ss.getSheetByName(targetSheetName); 
  var target = s.getRange(row, column);
  target.setValue(value);
}

Limit the changes to be synced

In order to limit the changes to be synced add rules to the code. The JavaScript comparators ==, ===, !=, !==, >, <, >=, <=, and the logical operators &&, ||, ! with if, switch among other commands could be used to create simple of complex rules.

Best Answer

Related Solutions

Google-sheets – Return name of range as string in Google Sheets

Google-sheets – Copying data to *and from* another sheet based on the month of a date

From the question

Short answer

Explanation

Broad instructions

Code

Limit the changes to be synced

References

Related Topic

Google-sheets – Copying data to and from another sheet based on the month of a date