Excel自动化操作-office script，自动复制sheetA结果到另一个sheetB

本文原创禁止转载，如需转载请联系博主。

基础：

ref：Cut, copy, and paste ranges using the Excel JavaScript API - Office Add-ins | Microsoft Learn

ref：paste过程记得加个wait()

【Office Scripts】How to wait for the number of specified seconds | Kokoroe (konomi.app)

ref：一个好用的例子：How to copy a filtered table in office script and paste it in a different sheet? - Microsoft Q&A

How to copy a filtered table in office script and paste it in a different sheet?

function main(workbook: ExcelScript.Workbook) {
 let firstSheet = workbook.getWorksheet("Sheet1");
 let table = firstSheet.getTables()[0]
 let visibleTableRange = table.getRange().getVisibleView();
 let visibleTableRangeValues = visibleTableRange.getValues();
 let sheetToPaste = workbook.getWorksheet("Paste")
 let pastedValues = sheetToPaste.getRangeByIndexes(0, 0, 
      visibleTableRange.getRowCount(), visibleTableRange.getColumnCount());
 pastedValues.setValues(visibleTableRangeValues);
 let newTable = sheetToPaste.addTable(pastedValues.getAddress(), true)
}

others:Office Script & Power Automate - copy data from one fixed range to - Microsoft Community

我自己写的例子：实现功能：对cm_table 某一列进行filter，把filter后的结果集复制粘贴到另一个sheet - "New SKUs"。

因为实现了复制filtered visible rows，所以会复杂一点。

 1 // Get the range of the table column named "Is SKU New?".
 2   let cm_table = current_month.getTables()[0];
 3   let cm_ColumnIsSKUNew = cm_table.getColumnByName("Is SKU New?");
 4   // if the index is 81, then the actual column index is the 82th, because index start with 0.
 5   console.log(" INDEX:" + cm_ColumnIsSKUNew.getIndex());
 6   // Apply checked items filter on table column Is SKU New? where value = "y"
 7   cm_table.getColumnByName("Is SKU New?").getFilter().applyValuesFilter(["Y"]);
 8 
 9   // Get filtered visible table, prepared for pasting.
10   let visibleTableRange = cm_table.getRange().getVisibleView();
11   let visibleTableRangeValues = visibleTableRange.getValues();
12   let sheetToPaste = workbook.getWorksheet("New SKUs")
13   let pastedValues = sheetToPaste.getRangeByIndexes(0, 0, 
14       visibleTableRange.getRowCount(), visibleTableRange.getColumnCount());
15  pastedValues.setValues(visibleTableRangeValues);
16   let newTable = sheetToPaste.addTable(pastedValues.getAddress(), true)
17   wait(1000);//1000 ms=1 second
18   console.log("Paste to sheet completed!");

简单版本：

//paste the whole sheet into New SKUs Sheet, this part is not used as it didn't filtered rows.
// let current_month = workbook.getWorksheet("Current Month");

//let new_SKUs = workbook.getWorksheet("New SKUs");

// Paste to range A1 on new_SKUs from table current_month
// new_SKUs.getRange().copyFrom(current_month.getRange(), ExcelScript.RangeCopyType.all, false, false);

不谢。