使用office脚本在工作表中使用最后一个非空单元格update.copyFrom

Question

我在更新主工作表的复制版本时遇到问题。

我在工作表中创建了一个按钮，以获得主工作表的副本，但在第7列和第5列显示了过滤后的值。

问题是，我们有时需要向列表中添加新项目，这会使范围扩大。我可以通过手动更新代码行中的功能来解决问题

// Paste to range A1 on TestSheetfrom range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.formats, false, false);
// Paste to range A1 on TestSheetfrom from range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.values, false, false);

以及更改为非空范围，例如，如果我添加一个项目，则需要更改为5001。如果我试图添加一个随机的长数字(例如6000(，这样我就可以一直在使用表范围内，那么函数会复制主表中的所有值，包括那些不在过滤函数中的值。

我查找了函数Range.getRangeEdge和Range.get-ExtendedRange，但我无法确定如何将其添加到copyFrom公式中。一些想法。

此代码用于生成根据筛选列筛选的项目列表

function main(workbook: ExcelScript.Workbook) {
let mainSheet = workbook.getActiveWorksheet();
// Check if the "Data" worksheet already exists.
if (workbook.getWorksheet("TestSheet")) {
console.log("The Data worksheet is already in the workbook. It will be deleted to add as new.");
let transferSheet = workbook.getWorksheet("TestSheet");
transferSheet.delete();
workbook.addWorksheet("TestSheet");
} else {
// Add a new worksheet.
let transferSheet = workbook.addWorksheet("TestSheet");
}
let transferSheet = workbook.getWorksheet("TestSheet");
// Toggle auto filter on selectedSheet
mainSheet.getAutoFilter().apply(mainSheet.getRange("H1"));
// Apply values filter on selectedSheet
mainSheet.getAutoFilter().apply(mainSheet.getAutoFilter().getRange(), 7, { filterOn: ExcelScript.FilterOn.values, values: ["x"] });
//mainSheet.getAutoFilter().apply(mainSheet.getAutoFilter().getRange(), 7, { filterOn: ExcelScript.FilterOn.values, values: ["x"] });
// Apply values filter on selectedSheet
mainSheet.getAutoFilter().apply(mainSheet.getAutoFilter().getRange(), 5, { filterOn: ExcelScript.FilterOn.values, values: [""] });

// Paste to range A1 on TestSheetfrom range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.formats, false, false);
// Paste to range A1 on TestSheetfrom range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.values, false, false);
// Auto fit the columns of range range E:E on TestSheet
transferSheet.getRange("E:E").getFormat().autofitColumns();
// Auto fit the columns of range range E:E on TestSheet
transferSheet.getRange("D:D").getFormat().autofitColumns();
// Auto fit the columns of range range C:C on TestSheet
transferSheet.getRange("C:C").getFormat().autofitColumns();
// Auto fit the columns of range range B:B on TestSheet
transferSheet.getRange("B:B").getFormat().autofitColumns();
// Auto fit the columns of range range A:A on TestSheet
transferSheet.getRange("A:A").getFormat().autofitColumns();
}

Answer 1

您尝试过使用列引用吗？你可以更新你的复制和粘贴行从这个：

// Paste to range A1 on TestSheetfrom range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.formats, false, false);
// Paste to range A1 on TestSheetfrom from range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A1:E5000"), ExcelScript.RangeCopyType.values, false, false);

到此：

// Paste to range A1 on TestSheetfrom range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A:E"), ExcelScript.RangeCopyType.formats, false, false);
// Paste to range A1 on TestSheetfrom from range A1:E3095 on selectedSheet
transferSheet.getRange("A1").copyFrom(mainSheet.getRange("A:E"), ExcelScript.RangeCopyType.values, false, false);

您甚至可以在getRange((和getUsedRange((方法中使用intersect运算符(空格字符(来动态地提供行数。如果你想走这条路，你会使用这样的东西：

function main(workbook: ExcelScript.Workbook) {
let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet()
let usedRangeAddy: string = sh.getUsedRange().getAddress()
let rang: ExcelScript.Range = sh.getRange(`A:E ${usedRangeAddy}`)
}

或者，如果你想直接访问Used Range的行属性，你可以写这样的代码：

function main(workbook: ExcelScript.Workbook) {
let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet()
let usedRangeRows: number = sh.getUsedRange().getRowCount()
let rang: ExcelScript.Range = sh.getRange(`A1:E${usedRangeRows}`)
}