Combine Specific columns from several tables using excel office script to one table

Question

A looking for a way to get specific columns by name from several tables. My data comes in several sheets with different number of columns upto 38 columns so i cannot use getColumnById. I only need 7 columns from this.

First am converting all sheet ranges to tables, then am getting all tables. What I want is to get specific columns by names and merge all into one table on a new sheet.

I followed example from Docs but am stuck at getting column name for each Table.

I know my header Values, shown in example below.

function main(workbook: ExcelScript.Workbook) {
    let sheets = workbook.getWorksheets();
    for (let sheet of sheets) {
        sheet.getTables()[0].convertToRange();
        sheet.addTable(sheet.getRange('A1').getUsedRange().getAddress(),true)
    }
    workbook.getWorksheet('Combined')?.delete();
    const newSheet = workbook.addWorksheet('Combined');
    const tables = workbook.getTables();
    const headerValues = [['Column1', 'Column6', 'Column8', 'Column9','Column11', 'Column16', 'Column18', 'Column19']];
    const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length - 1, headerValues[0].length - 1);
    targetRange.setValues(headerValues);
    const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
    for (let table of tables) {
        let dataValues = table.getColumnByName( // this where am stuck //).getRangeBetweenHeaderAndTotal().getTexts();
        let rowCount = table.getRowCount();

        // If the table is not empty, add its rows to the combined table.
        if (rowCount > 0) {
            combinedTable.addRows(-1, dataValues);
        }
    }

}

Thanks for your help. George

Answer 1

A few things:

1. In most circumstances for this scenario, I'd recommend iterating through a specific set of table objects. Unfortunately, that's difficult to do here. Every time you unlink and recreate a new table, Excel may give your table a new name. That makes it difficult to work with the table. You can get around this in your code by capturing the table name before you unlink it, unlinking the table, recreating the table, and setting the table name to the original one captured. If you go that route then you could reliably work with the table names
2. Because table names in this scenario can be a bit tricky, I'm going to use the sheet names so that I can work with the sheets that contain the underlying tables. This will allow us to use and get data from the tables regardless of what they're named in the sheets.

Please see my code below:

    function main(workbook: ExcelScript.Workbook) {
    
    //JSON object called SheetAndColumnNames. On the left hand side is the sheet name. 
     //On the right hand side is an array with the column names for the table in the sheet.
    
      //NOTE: replace the sheet and column names with your own values
    
      let columnNames : string[] = ["ColA","ColB","ColC"]
      const sheetAndColumnNames = {
        "Sheet1": columnNames,
        "Sheet2": columnNames
      }
    
    //JSON object called columnNamesAndCellValues. On the left hand side is the column name. 
     //On the right hand side is an array that will hold the values for the column in the table.

    //NOTE: replace these column names with your own values
      const columnNamesAndCellValues = {
        "ColA": [],
        "ColB": [],
        "ColC": []
      }
    
    
    //Iterate through the sheetAndColumnNames object
      for (let sheetName in sheetAndColumnNames) {
    
    //Use sheet name from JSON object to get sheet
          let sheet: ExcelScript.Worksheet = workbook.getWorksheet(sheetName)
    
    //get table from the previously assigned sheet
          let table: ExcelScript.Table = sheet.getTables()[0]
    
    //get array of column names to be iterated on the sheet
          let tableColumnNames: string[] = sheetAndColumnNames[sheetName]
    
    //Iterate the array of table column names
          tableColumnNames.forEach(columnName=> {

    //get the dataBodyRange of the tableColumn
            let tableColumn : ExcelScript.Range = table.getColumn(columnName).getRangeBetweenHeaderAndTotal()

    //iterate through all of the values in the table column and add them to the columnNamesAndCellValues array for that column name
            tableColumn.getValues().forEach(value=>{
              columnNamesAndCellValues[columnName].push(value)
            })
          })
      }
    
      //Delete previous worksheet named Combined
      workbook.getWorksheet("Combined")?.delete()
    
      //Add new worksheet named Combined and assign to combinedSheet variable
      let combinedSheet : ExcelScript.Worksheet = workbook.addWorksheet("Combined")
    
      //Activate the combined sheet
      combinedSheet.activate()
    
    //get the header range for the table
      let headerRange : ExcelScript.Range = combinedSheet.getRangeByIndexes(0,0,1,columnNames.length)
    
    //set the header range to the column headers
      headerRange.setValues([columnNames])
    
      //iterate through the arrays returned by the columnNamesAndCellValues object to write to the Combined sheet
      columnNames.forEach((column,index)=>{
        combinedSheet.getRangeByIndexes(1, index, columnNamesAndCellValues[column].length, 1).setValues(columnNamesAndCellValues[column])
        })
    
      //Get the address for the current region of the data written from the tableColumnData array to the sheet
      let combinedTableAddress : string = combinedSheet.getRange("A1").getSurroundingRegion().getAddress()
    
      //Add the table to the sheet using the address and setting the hasHeaders boolean value to true
      combinedSheet.addTable(combinedTableAddress,true)
    }