Dynamic Table Range in Excel Online "Create Table" node

[Anonymous]

Hi,

Every day, we save an Excel file on our Sharepoint environment and create an Excel Table in the file. 

The content of the Excel file is different every day. Different number of rows as well.

When I use the "Create Table" node from the Excel Online package, I need to specify the Table Range.

Every file contains columns A to column DN. But the rows are different. 

It tried the table Range "A:DN" but when I do that, it creates a lot of empty rows at the end of the data table. So I think I have to specify the number of rows as well.

For example today I have 412 rows. So the table range is "A$1:DN$412". 

My question is: Do I really need to specify the number of rows in the table range? and if so: How can I extract the number of rows from an Excel file?

Thanks!

Marco

[MaheswaranSK]

Hi, I have tried =OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),SUBTOTAL(103,Sheet1!A1:H1)) to find both rows and coln dynamically but it always throws me either resource not found error or invalid expression. My test file has 3 rows with header colns in first row. The same formulae is working in normal excel file correctly. can u pls help

{

  "status": 404,

  "message": "The resource could not be found.\r\nclientRequestId: d4402cc9-6102-4ea4-83d8-8567f3eeb967\r\nserviceRequestId: 1ab269af-ddea-4987-b8a5-beb6cd8ee0f3",

  "error": {

    "message": "The resource could not be found."

  }

[CP153319]

Thought I should add to this thread because I tried some of the suggestions here, and was really excited about the "OFFSET" solution, but none of them worked. I found the answer using Office Scripts combined with the Flow action "Run A Script". 

My scenario is creating an Excel file in a SharePoint library and then needing to add a table to the file. The number of columns in the file stay the same but the number of rows can change. It is your typical "flat" Excel file so there is nothing fancy going on. 

This Office Script creates a table the same size as whatever the data is in the Excel file and then names the table "Data", so I know what table name to pass to the next Flow action.

Here is the script and it is really that simple:

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();

    let usedRange = selectedSheet.getUsedRange();

    let newTable = selectedSheet.addTable(usedRange, true);

    newTable.setName("Data");

}