用于复制和重命名工作表和名称的Google脚本基于单元格引用 [英] Google script to copy and rename a sheet and name is based on a cell reference

问题描述

我是Google新手，我需要将当前活动工作表复制到新工作表，然后根据单元格值重命名此工作表。我的问题是单元格的值是一个日期和下面的代码工作，但重新命名工作表30 - 5月-2014它是返回数字等效41789.我怎么能粘贴实际日期。

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);

}

推荐答案

需要将值格式化为一个字符串，然后用它来设置名称。

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);
}

希望有帮助。

这篇关于用于复制和重命名工作表和名称的Google脚本基于单元格引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！