1

I have a script that changes the name of a sheet based on a cell in that sheet. Which works. But I am trying to change the sheet name to a particular date format - 'August 1, 2022'. But the sheet name appears as 'Tue Aug 01 2028 08:00:00 GMT+0100 (British Summer Time)' I have tried to add the code '.setNumberFormat('dd.MM.yyyy');' to my code but this doesn't work.

Any ideas? My current code is

function changeSheetName( ){
var As = SpreadsheetApp
.getActiveSpreadsheet( )
.getActiveSheet( )
As.setName(As.getRange("A1")
.getValue( ) )
.setNumberFormat('dd.MM.yyyy');
}

function onEdit( ){
changeSheetName( )
}   
1

1 Answer 1

1

If cell A1 already contains a date in the format you want in the sheet name, simply use Range.getDisplayValue() instead of Range.getValue() and set the sheet name to that text string.

If the date format in cell A1 is something else, use Utilities.formatDate(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  changeSheetName_(e);
}


/**
* Changes the active sheet name to the date in cell A1 of the sheet.
*
* @param {Object} e The onEdit() event object.
*/
function changeSheetName_(e) {
  const sheet = e.source.getActiveSheet();
  const date = sheet.getRange('A1').getValue();
  if (Object.prototype.toString.call(date) !== '[object Date]') {
    return;
  }
  const timezone = e.source.getSpreadsheetTimeZone();
  const sheetName = Utilities.formatDate(date, timezone, 'MMMM d, yyyy');
  sheet.setName(sheetName);
}

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.