Office script to get data from table, filtered data and pasted into a new tab and new table

  Kiến thức lập trình

I have been trying to extract filtered data from my “PMs” table in my excel, I need to filter column D for statuses that are equal to “WSCH” or “WAPPR” or “INPRG” and filter column H with values equal or less to 7, once the filter has been applied, create a new tab and paste the filtered data into a new table called PM On Risk, I have tried it for several days but I can’t get it.

Thank you very much for your help.


I try this:

function main(workbook: ExcelScript.Workbook) {
  // Obtener la tabla "PMs"
  let table: ExcelScript.Table = workbook.getTable("PMs");

  // Filtrar los datos por la columna "Status" igual a "WSCH", "WAPPR" o "INPRG" y la columna "Days till the target end date" menor o igual a 7
  let filteredRange: ExcelScript.Range = table.getRange().getFilteredRange([{
    column: table.getColumnByName("Status").getIndex(),
    operator: "In",
    values: ["WSCH", "WAPPR", "INPRG"]
  }, {
    column: table.getColumnByName("Days till the target end date").getIndex(),
    operator: "LessThanOrEqual",
    values: ["7"]

  // Crear una nueva hoja de cálculo llamada "PMRisk"
  let newSheet: ExcelScript.Worksheet = workbook.addWorksheet("PMRisk");

  // Crear una nueva tabla llamada "PM_Notification" en la nueva hoja de cálculo
  let newTable: ExcelScript.Table = newSheet.addTable("PM_Notification", filteredRange, true);