List Rows present in a table - how do I programatically specify the table?

[Gareth-]

Hi Microsoft,

It does not seem possible to use a variable or an expression to specify the name of the table in the 'List rows present in a table' step in PowerAutomate.  See picture - red dot - 

this can only be hard-coded at flow creation.

I believe that this is a serious limitation that significantly reduces the value of (a) Office Script and (b) Power automate integration into office script.

The general use case that is broken is like this (one example of many):

(1) New Data is generated in an excel file -->    (2) an office script program is triggered by Flow to transform the data --> (3) the data is consolidated into another file, which is used as a power BI data source --> (4) the Power BI report is refreshed automatically.

The data in step (1) is periodic i.e. hourly, weekly, monthly etc. [it does not matter] 

The problem comes in step (3) as you cannot read the data from the Excel table from the file created in step (1) as the file has been created after the flow was programmed. You can use other flow steps to get the file properties (ie filename, the names of the tables in the file), but you cannot programmatically put the table name into the 'List rows present in a table step'

Am i missing something? (that would be great - please let me know!) otherwise this is like having a jigsaw with a piece missing.

any guidance appreciated.

[danwilso]

Hey Gareth!

Great callout, thanks for your feedback—you are correct, "List rows present in a table" isn't really usable for more complex scenarios like this; it's really meant to read from a specific table in a specific workbook which doesn't change.

It would be hard to enable this action for dynamic table names; since the action loads the table's headers for use later in the flow with the dynamic content pane, it tries to read the headers from the selected table—which doesn't really work if you also want to work with tables that don't exist yet.

Since you're already using Office Scripts, I'd accomplish this by creating an Office Script to mimic the functionality of "List rows present in a table," but one which accepts a tableName parameter.

Here's my attempt below—should work for you with some minor adjustments:

/**
 * Lists rows present in a specific table named tableName
 * 
 * This script helps to overcome some shortcomings with Power Automate's Excel Online (Business) connector "List rows present in a table" action which does not allow for dynamically defined table names.
 */
function main(workbook: ExcelScript.Workbook, tableName: string = "Table1") {
    // First, get the correct table according to the parameter received from Power Automate - or some default if testing the script in Excel for the Web
    const table = workbook.getTable(tableName);

    // Get the range of only the body of the table, not the header
    const tableRange = table.getRangeBetweenHeaderAndTotal();

    // Next, get the cell values from the table range - tableRange actually contains Excel objects, and we only want the values.
    const tableValues = tableRange.getValues();

    /**
     * Convert the array of table row / cell values to be an array of cell value objects where each cell value has its corresponding header, e.g.:
     * 
     * { header: value }
     * 
     * This will allow us to select specific values from rows when using the script in Power Automate, just as the "List rows present in a specific table" action does.
     * 
     * You don't need this part if you're not using the "Dynamic content" pane in the flow designer.
     */

    // We must specify the table headers or the rows won't show up nicely in Power Automate. We must also specify the headers below in the TypeScript type (... as { ... }).
    const tableHeaders = ["A", "B", "C"];

    const result = tableValues.map((rowValues) => {
      return rowValues.reduce((row, cellValue, i) => {
        const header = tableHeaders[i];
        row[header] = cellValue;

        return row;
      }, {} as {
        A: string;
        B: string;
        C: string;
      });
    });

    console.log(result);

    return result;
}

You'll have to adjust the tableHeaders variable to match your column headers (same for the "...row as { A:... }" near the end). This should accomplish more or less what you're looking for, assuming your tables all have the same headers.

[Gareth-]

Hi danwilso (Dan?),

Many thanks for this.  I will give it a go.  thanks for taking the time to reply.

regards

Gareth 

[alexaso]

Hi, 

is there any other workaround?

In my case we need to create automaticly an Excel file with the same file and table name. But Power Automate take the old tableid instead of name.

[Chriddle]

The "Get tables" action returns an array of table IDs and table names.

You can use the name to determine the table ID, which you can insert into the "Get rows" action.

[alexaso]

Hi Chriddle,

thanks for that. What did you mean exatly with determine the table ID? With the dynamic operation im getting an error. The new table doesnt exist yet. 

[Chriddle]

The error in your flow happens, because you inserted the table name. You have to use the id.

With "determine the table ID", I thought of following:

With a given table name (here "testTable") I loop through all tables and check for the name. Then use its id to get the rows.

[Chriddle]

Of course, if you need the data of all tables in your flow, you don't need this condition.

[Chriddle]

This creates an array of objects with each name and the rows of all tables in the Excel file:

[alexaso]

Now it works with the ID. I just need to set the actual Table Name (hard-coded at flow creation) and setup afterwards the dynamic ID. 

I have just one Table in my file, so i dont need any conditions there.

Next days i'll test furthermore. Thank you for your support.

Kind regards,

Alex