我正在尝试创建一个脚本,该脚本每天从主工作表创建一个新工作表,将其重命名为当前日期,编辑A1以显示当前日期,隐藏主工作表并将工作表移动到底部选项卡上的第一个位置。非常困,请帮忙!
function master() {
const pattern = 'master';
var source = SpreadsheetApp.getActiveSpreadsheet();
var date = getDates();
var master = source.getSheetByName(pattern);
var current = source.getSheetByName(date);
source.insertSheet(date);
master.copyTo(current);
master.hideSheet();
current.getRange("A1").setValue(getDates()).moveActiveSheet(1);
}
function getDates() {
var today = new Date();
var today_month = addZero(today.getMonth()+1)
var today_day = addZero(today.getDate())
var today_year = addZero(today.getYear()+1900)
var today_date = today_day.toString() + "/" + today_month.toString() + "/" + today_year.toString()
return [today_date]
}
function addZero(i) {
if (i < 10) {
i = "0" + i;
}
return i;
}
我看到您试图创建自己的函数来将日期转换为字符串。由于您想要标准的欧洲格式,因此使用Date.toLocaleDateString((方法更容易
另一个改进是Sheet.copyTo((返回新的工作表,因此您可以链接更多的方法,如activate((或setName((,并最终将结果分配给一个变量。
这应该做你想做的:
function master() {
const pattern = 'master';
const locale = "en-GB"; // date format dd/mm/yyyy
var today = new Date()
var title = today.toLocaleDateString(locale);
// find the master sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName(pattern);
// make a copy, set as active, and rename
var current = master.copyTo(ss).activate().setName(title)
// rearrange tabs and hide master
ss.moveActiveSheet(1);
master.hideSheet();
// put the date in A1 (as a date format, not text)
current.getRange("A1").setValue(today)
}