Google script to copy and rename a sheet and name

问题:

I'm new to google scripts and I need to copy the current active sheet to a new sheet and then rename this sheet based on a cell value. My issue is the cell value is a date and the below code works but instead on renaming the sheet 30-May-2014 it is returning the numeric equivalent 41789. How can I paste the actual date.

function CreateNewTimesheet() {

  // The code below makes a duplicate of the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

  // The code below will rename the active sheet to Month End date based on cell O3
 var myValue = SpreadsheetApp.getActiveSheet( ).getRange("O3").getValue();
 SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);

}

回答1:

You would need to format the value into a string and then use it to set the name.

var localTimeZone = "Europe/London";
var dateFormatForFileNameString = "yyyy-MM-dd'at'HH:mm:ss";

function CreateNewTimesheet() {

  // The code below makes a duplicate of the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

  // The code below will rename the active sheet to Month End date based on cell O3
 var myValue = SpreadsheetApp.getActiveSheet( ).getRange("O3").getValue();
 var dateString = getDateString_(myValue);
 SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(dateString);
}

//Function to get Date as a string
function getDateString_(dateValue) {
      return Utilities.formatDate(dateValue, localTimeZone,
                                  dateFormatForFileNameString);
}

Hope that helps.

回答2:

You need to construct a new Date(myValue), then look in the Utilities class for the date formatting function.

回答3:

Just using .getDisplayValue() in place of .getValue() in your code should do the trick.

It gets the value displayed in the cell as a string, taking into account things like date formatting, so when you then use that to rename your sheet the sheet name will be the same as what you see in your cell.