Excel自動化操作-office script，自動複製sheetA結果到另一個sheetB

po-A發表於2024-06-25
Excel

本文原創禁止轉載，如需轉載請聯絡博主。

基礎：

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);

不謝。

相關文章