Office Scripts - How to select specific columns using column name from a table and delete other columns?

Question

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.

Categ_No	Position	Salary	Bonus	Increment_Percentage
CAT01	Accountant	100K	25K	5%
CAT02	Controller	80K	20K	7%
CAT03	Tax Analyst	70K	15K	6%
CAT04	Director	200K	35K	8%

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!

Answer 1

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

Answer 2

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.