By default, tasks related to Excel tables can be integrated into low-code automation tools, such as Power Automate. However, for more advanced scenarios, you may need to rely on Office Scripts, which provides you with a wide range of alternatives to automate not only operations in Excel tables, but also in individual ranges, charts, worksheets and more.

In this post, you will learn how to read data from Excel tables using Office Scripts, which can help you to take your Excel-based automations to the next level.

Accessing all table data with Office Scripts

For this article, we will use the following Excel table, which represents a dataset containing a list of products and their current inventory levels. The only column whit a formula is in_stock, which evaluates whether the inventory column is greater than zero, returning a boolean (true/false) value based on this condition:

For accessing all values from the Excel table, we first need to retrieve the table object itself, which can be done using the getTable() method from workbook object. Within the parentheses, you need to specify the table name, which in this case is Products. We are also assigning the table object to a variable called productsTable:

Note: at the end of this article, you can find the complete script.

Unlike individual ranges, Office Scripts can easily identify a table directly from the workbook object, since tables have a structured reference.

Now that we already have access to the table object, we can access its records content by respectively using the getRangeBetweenHeaderAndTotal() and getValues() methods. The getRangeBetweenHeaderAndTotal() accesses all cells occupied by the table, excluding the headers and the total row (if any). The getValues() will retrieve the content of these cell, representing the values rather than any formulas. We are also assigning this table content to a variable called productsTableValues:

We can visualize the outputs of productsTableValues variable by using the console.log() method, as described in this article, which will print the values to the console for inspection:

Since we are returning all the table records, the output will be an array containing five nested arrays, each one with five records as well. This happens because we have a 5×5 tables (five columns and five rows), and Office Scripts returns it as one array for each row, holding one element for each of the five columns.

If we expand the outputs, we can find the values from each table row, organized within the nested arrays:

The order of the elements within the arrays follows the same order of the columns in the table, and the data types of the elements align with the types assigned to their respective columns.

Accessing specific table row with Office Scripts

Now let’s access the records from a specific row. To achieve it, we can use productsTable.getRangeBetweenHeaderAndTotal().getRow(2), where we first access the Products table object stored in productsTable variable, get all ranges using getRangeBetweenHeaderAndTotal(), and then call the getRow() method to choose an individual row by its position. In this case, since the position counting is zero-indexed, we selected the third row by passing “2” between the parenthesis. The resulting cells for this row are represented as a range object, and we will store this a variable named thirdRow:

By employing the same strategy as before, we can use console.log() to print the data, but at this time we include the getValues() method within the console.log(). To ensure clarity and avoid duplicate outputs, we commented out the previous console.log() in line 8 by adding double slashes (//). After running the scrip, the output will be an array containing a single nested array with five elements, corresponding to each column in the third row of the table:

Returning data from a table column with Office Scripts

To access the values from a specific column, the process is quite similar, but you must use the getColumn() method instead of getRow(). The getColumn() also takes an integer number as parameter, corresponding to the zero-indexed position of the column to be retrieved:

The output is an array containing five nested arrays, each one holding a single element that corresponds to the product value from each record. As we observed earlier when retrieving all values from the table, each row is returned as an individual nested array, and each element within these inner arrays corresponds to a column. In this case, we see only one element, per inner array because we are specifically accessing values from the product column alone:

For a better data visualization and manipulation, it would be ideal to eliminate the inner array layer, transforming the output into a single list containing all values for the product column from each row. The good news is that Typescript provide us with a built-in method to accomplish this task, the flat(). By using flat() on an “array of arrays”, you can remove all internal layers, resulting in a single array where all items are at the same level, as highlighted in red:

You might notice that Office Scripts raises a warning for regarding the flat() method, indicateing that it doesn’t exist in this context. You can safely ignore this warning, since this is a default configuration from Office Scripts, which does not explicitly recognize this method for arrays.

Filtering table records with Office Scripts

For some use cases, you may need to retrieve multiple rows from your table using Office Scripts. While you can easily access individual or all records, there is no built-in method within the table object to directly return multiple rows (but not all) at once.

One alternative approach could be to apply a filter to the table. The filters, however, are only a visual representation of the data and do not affect what is retrieved when using Office Scripts. In the example below, the table is filtered to show only the rows which column in_stock is equal to true (highlighted in blue), but it Office Script is still returning all five rows when reading the table records (highlighted in green):

The good news is that there is a workaround for this limitation. TypeScript has a built-in method called filter(), which can be used to extract elements from an array based on specific conditions. Since our records are organized in a two-dimensional array, we can use filter() to retrieve only the records which in_stock column is not equal to false.

For this use case, we will call the filter method on the productsTableValues variable (highlighted in red below), which is a bidimensional array. The method expects a function as parameter, where you can define the condition for filtering the records. For this use case, we are working with an arrow function, which is a concise syntax for writing functions in TypeScript. This arrow function will be executed for each element in the array and includes a parameter (highlighted in yellow below) that represents the current element being processed. Within the function statement (highlighted in green below), we can specify the filtering condition to only return rows where the in_stock column indicates that the product is available (not equal to false):

In the arrow function, we check if the fifth element (corresponding to the in_stock column) is different from false; if it is, we retain the record in the filtered list; otherwise, we exclude it. Since the indexing is zero-based, we access the fifth column using the [4] notation. The !== operator ensures that only values that are not equal to false are included in the final filtered array.

After executing the script, the output is an array containing only 3 elements, each corresponding to a record where the in_stock column is set to true:

Complete code

This is the complete code used for the script:

function main(workbook: ExcelScript.Workbook) {
    
    const productsTable = workbook.getTable('Products')
    
    const productsTableValues = productsTable.getRangeBetweenHeaderAndTotal().getValues()

    //console.log(productsTableValues)

    const thirdRow = productsTable.getRangeBetweenHeaderAndTotal().getRow(2)
    
    //console.log(thirdRow.getValues())

    const productColumn = productsTable.getRangeBetweenHeaderAndTotal().getColumn(1)

    //console.log(productColumn.getValues().flat())


    const filteredData = productsTableValues.filter(e => e[4] !== false)
    
    console.log(filteredData)

}

Conclusion

We explored how to read data from Excel tables using Office Scripts, which can be very useful when you already have an Office Script automation that requires to access data from tables. Although there is no built-in methods in the table object to return multiple rows data, we saw that it is still possible to achieve it by utilizing some TypeScript native features, such as the filter() method.