TypeScript Code

link - learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-tables

Creating

Excel.run(function (context) { 
   const currentWsh = context.workbook.worksheets.getActiveWorksheet() 
//   const context.workbook.tables.add('Sheet1!A1:E7', true);  
   const myTable = currentWsh.tables.add("A1:D1", 
                                        true /*has Headers*/ ); 
   myTable.getHeaderRowRange().Values = 
      [ [ "col1, "col2", "col3", "col4"] ]; 
   myTable.rows.add 
      (null /*add at the end*/, 
      [ [ "one", "11", "one-1", "1-1"], 
      [ [ "two", "22", "two-2", "2-2"], 
      [ [ "three", "33", "three-3", "3-3"], 
   ]); 
   myTable.columns.getItemAt(3).getRange().numberFormat = [['@']];  
   myTable.getRange().format.autofit.Columns(); 
   myTable.getRange().format.autofit.Rows(); 

                return context.sync(); 
}).catch(function (error) { 
   console.log(error); 
});

Reading

Excel.run(function (context) { 
   var tableRows = context.workbook.tables.getItemAt(0).rows.load("values"); 
   return context.sync().then(function () { 
      for (var i = 0; i < tableRows.items.length; i++) { 
         console.log(tableRows.items[i].values); 
      } 
   console.log("done");                      
}); 
}).catch(function (error) { 
   console.log(error); 
});

Read a table from the document and display its content.

Office.context.document.getSelectedDataAsync("table", 
                                             function (asyncResult) { 
  if (asyncResult.status == "failed") { 
    showMessage("Action failed with error: " + asyncResult.error.message); 
  } 
  else { 
    showMessage("Headers: " + asyncResult.value.headers + " Rows: " + 
    asyncResult.value.rows); 
  } 
});

The following example uses the getSelectedDataAsync method to read the currently selected table.
The CoercionType.Table parameter specifies the expected data type.
The callback function returns an object containing the data currently selected by the user, which can be accessed by the asyncResult.value property.

Read All

Excel.run(function (context) { 
   var tables = context.workbook.tables.load("name"); 
   return context.sync().then(function() { 
      for (var i = 0; i < tables.items.length; i++) 
      { 
         console.log(tables.items[i].name); 
      } 
   console.log("done"); 
}); 
}).catch(function (error) { 
   console.log(error); 
});

Add Row

Excel.run(function (context) { 
    context.workbook.tables.getItem('Table1').rows.add(3, [[1,2,3,4,5]]);  
    return context.sync(); 
}).catch(function (error) { 
   console.log(error); 
});

Format Row

Excel.run(function (context) { 
    var range = context.workbook.tables.getItem('Table1').rows.getItemAt(1).getRange();  
    range.format.fill.color = "#00AA00"; 
    return context.sync(); 
}).catch(function (error) { 
   console.log(error); 
});

Delete Row

Excel.run(function (context) { 
    context.workbook.tables.getItem('Table1').rows.getItemAt(3).delete();  
    return context.sync(); 
}).catch(function (error) { 
   console.log(error); 
});

Writing

Create a TableData object.

var myTable = new Office.TableData(); 
myTable.headers = ["First Name","Last Name","Grade"]; 
myTable.rows = [["Lisa","Simpson","A"], ["Bart","Simpson","C"], 
               ["Homer","Simpson","B"]]; 

Office.context.document.setSelectedDataAsync(myTable, 
                                             function (asyncResult) { 
if (asyncResult.status == "failed") { 
showMessage("Action failed with error: " + asyncResult.error.message); 
} 
});

Be aware that a table can only be written into the spreadsheet if no other data in the surrounding cells will be overwritten.

Updating Row

Create a new table

var myTable = new Office.TableData(); 
myTable.headers = ["City", "State"]; 
myTable.rows = [["Phoenix","AZ"],["Raleigh","NC"], ["Nashville","TN"], 
               ["Boston","MA"]]; 

Office.context.document.setSelectedDataAsync(myTable, 
                                             function (asyncResult) { 
if (asyncResult.status == "failed") { 
showMessage("Action failed with error: " + asyncResult.error.message); 
} 
});

Filtered - Visible Rows

'create a table binding
Office.context.document.bindings.addFromNamedItemAsync 
   ("Table1", "table", {id : "myTableBinding1" 
      }, function(asyncResult){ 
         //handle any errors 
      }); 

Office.select("Bindings#MyTableBinding1").getDataAsync({ 
   coercionType : "table", 
   filterType : "onlyVisible" 
}, function (asyncResult) { 
   var values = (asyncResult.value.rows); 
});

This is the new way to do it

var table = tables.getItemAt(0); 
var visibleView = table.getRange().getVisibleView(); 
context.load(visibleView); 
context.sync() then (function () { 
   var values = visibleView.values; 
});

Sorting

table.showTotals = true; 
table.style = "TableStyleLight10" 

var table = context.tables.getItem("") 
var sortFields = [ { } ] 
table.sort.apply(sortFields, true) 
context.load(table)

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