使用Porperties Service [1],您可以解决您的问题,我编辑了链接到示例电子表格的代码,如下所示:
function PlusMP() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('5:5').activate();
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(),1);
spreadsheet.getActiveRange().offset(0,1,spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange('6:6').copyTo(spreadsheet.getActiveRange(),SpreadsheetApp.CopyPasteType.PASTE_NORMAL,false);
spreadsheet.getRange('L5').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true,skipFilteredRows: true});
spreadsheet.getRange('H5').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true,skipFilteredRows: true});
//Set offset property
var scriptProperties = PropertiesService.getScriptProperties();
var offset = Number(scriptProperties.getProperty('offset'));
if ( offset == null) {
scriptProperties.setProperty('offset',1);
}
else {
scriptProperties.setProperty('offset',offset + 1);
}
};
function PlusCOMP() {
//Get offset property
var scriptProperties = PropertiesService.getScriptProperties();
var offset = Number(scriptProperties.getProperty('offset'));
if(offset == null) {
offset = 0;
}
var row = 10 + offset;
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange(row + ':' + row).activate();
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(),spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange(row + ':' + row).copyTo(spreadsheet.getActiveRange(),false);
spreadsheet.getRange('L' + row).activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true,skipFilteredRows: true});
spreadsheet.getRange('H' + row).activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true,skipFilteredRows: true});
};
function resetOffset() {
PropertiesService.getScriptProperties().setProperty('offset',0);
}
运行PlusMP函数时,它将设置或更新offset
属性,而运行PlusCOMP函数时,它将使用相同的offset
属性相应地插入行。
[1] https://developers.google.com/apps-script/reference/properties/properties
本文链接:https://www.f2er.com/3143829.html