I want to select specific columns by Column Name from a table in Excel Online Workbook and delete other unwanted columns. I want to do this using Office Scripts.

As above table shows I want only Categ_No ,Position and Bonus columns to be selected by column name using Office Scripts and delete other two columns(Salary and Increment_Percentage).

I'm new to the Office Scripts and any help would be super appreciate!

CodePudding user response：

This is basic, needs more work, but demonstrates what you're asking for ...

function main(workbook: ExcelScript.Workbook) {
  let myTable = workbook.getTable("MyTable");
  let columnsToKeep: string[] = ["Categ_No", "Position", "Bonus"];

  myTable.getColumns().forEach(column => {
    let columnName = column.getName();

    if (!columnsToKeep.includes(columnName)) {
      column.delete();
    }
  });
}

CodePudding user response：

You can try the code below:

function main(workbook: ExcelScript.Workbook) {
  let tbl = workbook.getTable("Table1")
  let headersArr = tbl.getHeaderRowRange().getValues()[0] as string[]
  let finalColumns: string[] = ["Categ_No", "Position", "Bonus"];
  headersArr = headersArr.filter(e => !finalColumns.includes(e))
  headersArr.forEach(e => {
    tbl.getColumn(e).delete()
  })
}

It should produce the same result as the other answer. How it does that is just a bit different. This code gets the values from the header row ranges. It then filters the headers array based on the columns you don't want to keep. And then it iterates through it (which at this point will be columns you don't want) and delete the columns with those column names.