手动从菜单运行Google Apps脚本，而不是在打开时运行

Question

我最近更新了我的脚本以从自定义菜单而不是工作表中的按钮调用它，但我发现脚本在打开时立即运行"copyNew"函数，但我希望它仅在我从自定义菜单中手动单击它时才运行，任何人都可以指导我调整此脚本以实现此目的， 请？

"复制新">

功能将"结果"工作表中的所有内容复制到"新"工作表上 "清除单元格"功能从"结果"工作表中清除某些列

这是脚本：

function onOpen() {
var ui = SpreadsheetApp.getUi()
ui.createMenu("Payroll")
.addItem("Create New Month", "CopyNew")
.addItem("Reset Results", "ClearCells")
.addToUi();
}
function CopyNew() {  
var ss = SpreadsheetApp.getActive();
var sheetName = ss.getSheetByName("Results");
var createSheet = ss.getSheetByName("NEW");
if(!newSheet){
var createSheet = ss.insertSheet("NEW");
var sourceRange = sheetName.getRange("A1:Q100");
var targetRange = newSheet.getRange("A1:Q100");
targetRange.setValues(sourceRange.getValues());
sourceRange.copyTo(targetRange, {formatOnly:true});
}
}
CopyNew();//remove this
function ClearCells() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('H7:H100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('L7:L100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('N7:N100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('Q7:Q100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

谢谢！

Answer 1

就像这样：

function onOpen() {
var ui = SpreadsheetApp.getUi()
ui.createMenu("Payroll")
.addItem("Create New Month", "CopyNew")
.addItem("Reset Results", "ClearCells")
.addToUi();
}
function CopyNew() {  
var ss = SpreadsheetApp.getActive();
var sheetName = ss.getSheetByName("Results");
var createSheet = ss.getSheetByName("NEW");
if(!newSheet){
var createSheet = ss.insertSheet("NEW");
var sourceRange = sheetName.getRange("A1:Q100");
var targetRange = newSheet.getRange("A1:Q100");
targetRange.setValues(sourceRange.getValues());
sourceRange.copyTo(targetRange, {formatOnly:true});
}
}
function ClearCells() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('H7:H100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('L7:L100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('N7:N100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('Q7:Q100').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
}