Scripting and Macros

This script creates a table from the current worksheet's used range, then sorts it based on the first column.

function main(workbook: ExcelScript.Workbook) { 
  let selectedSheet = workbook.getActiveWorksheet(); 
  let usedRange = selectedSheet.getUsedRange(); 
  let newTable = selectedSheet.addTable(usedRange, true); 
  newTable.getSort().apply([{ key: 0, ascending: true }]); 
} 

Combine data from multiple Excel tables into a single table

function main(workbook: ExcelScript.Workbook) { 
  // Delete the "Combined" worksheet, if it's present.  
  workbook.getWorksheet('Combined')?.delete();  

  // Create a new worksheet named "Combined" for the combined table. 
  const newSheet = workbook.addWorksheet('Combined');  
  
  // Get the header values for the first table in the workbook. 
  // This also saves the table list before we add the new, combined table. 
  const tables = workbook.getTables();    
  const headerValues = tables[0].getHeaderRowRange().getTexts(); 
  console.log(headerValues); 

  // Copy the headers on a new worksheet to an equal-sized range. 
  const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length-1, headerValues[0].length-1);  
  targetRange.setValues(headerValues); 

  // Add the data from each table in the workbook to the new table. 
  const combinedTable = newSheet.addTable(targetRange.getAddress(), true); 
  for (let table of tables) {      
    let dataValues = table.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); 
    } 
  } 
} 

Combine data from multiple Excel tables in select worksheets into a single table

function main(workbook: ExcelScript.Workbook) { 
  // Set the worksheet names to get tables from. 
  const sheetNames = ['Sheet1', 'Sheet2', 'Sheet3'];  
    
  // Delete the "Combined" worksheet, if it's present.  
  workbook.getWorksheet('Combined')?.delete();  

  // Create a new worksheet named "Combined" for the combined table. 
  const newSheet = workbook.addWorksheet('Combined');  

  // Create a new table with the same headers as the other tables. 
  const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()[0].getHeaderRowRange().getTexts(); 
  const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length-1, headerValues[0].length-1);  
  targetRange.setValues(headerValues); 
  const combinedTable = newSheet.addTable(targetRange.getAddress(), true); 

  // Go through each listed worksheet and get their tables. 
  sheetNames.forEach((sheet) => { 
    const tables = workbook.getWorksheet(sheet).getTables();     
    for (let table of tables) { 
      // Get the rows from the tables. 
      let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts(); 
      let rowCount = table.getRowCount(); 

      // If there's data in the table, add it to the combined table.  
      if (rowCount > 0) { 
          combinedTable.addRows(-1, dataValues); 
      } 
    } 
  }); 
}