0

I have a table and I'm looking to delete specific columns for further processing via Office Scripts. Also had a look at the API reference but I was not able to find a method to do so.

I have ~30 columns in total but would like to remove several columns which contain blank row data, after which the plan is to use getId() or getIndex() to perform further operations on the reduced set of columns.

My concern is that each Excel file does not have a fixed number of columns nor in a specific order, so I guess using getColumnById or getColumnByName will be a challenge.

The furthest I could get is to identify the column count, but just wondering if there is a way to achieve the above.

let headerRangeValues = bodyRange.getColumnCount();
nouptime
  • 9,929
  • 5
  • 22
  • 37

1 Answers1

1

This script will delete all columns in a table that are completely empty. Let me know if this solves your scenario.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let table = selectedSheet.getTables()[0];
  let body = table.getRangeBetweenHeaderAndTotal().getValues();
  let headers= table.getHeaderRowRange().getValues();
  for (let column=table.getColumns().length-1; column>=0; column--){
    var entireColumnIsEmpty = true;
    for (let row=0; row<table.getRowCount(); row++){
         if (body[row][column] != ""){
           entireColumnIsEmpty = false;
           break;
         }
         
    }
    if (entireColumnIsEmpty){
      table.getColumnByName(""+headers[0][column]).delete();
    }
  }
}
Petra
  • 356
  • 1
  • 3
  • Thank you! This is perfect, though maybe better suited for less columns since I have 20+ columns to delete. Sure takes a while but definitely works ! – nouptime Apr 26 '21 at 17:58
  • 1
    Since the values are read ahead of time, the script should run quickly for a large number of columns as well. The thing that can slow down this script is the number of rows (large data set). – Sudhi Ramamurthy May 03 '21 at 19:10