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，所以会复杂一点。

// Get the range of the table column named "Is SKU New?".
  let cm_table = current_month.getTables()[0];
  let cm_ColumnIsSKUNew = cm_table.getColumnByName("Is SKU New?");
  // if the index is 81, then the actual column index is the 82th, because index start with 0.
  console.log(" INDEX:" + cm_ColumnIsSKUNew.getIndex());
  // Apply checked items filter on table column Is SKU New? where value = "y"
  cm_table.getColumnByName("Is SKU New?").getFilter().applyValuesFilter(["Y"]);

  // Get filtered visible table, prepared for pasting.
  let visibleTableRange = cm_table.getRange().getVisibleView();
  let visibleTableRangeValues = visibleTableRange.getValues();
  let sheetToPaste = workbook.getWorksheet("New SKUs")
  let pastedValues = sheetToPaste.getRangeByIndexes(0, 0, 
      visibleTableRange.getRowCount(), visibleTableRange.getColumnCount());
 pastedValues.setValues(visibleTableRangeValues);
  let newTable = sheetToPaste.addTable(pastedValues.getAddress(), true)
  wait(1000);//1000 ms=1 second
  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);

 

不谢。