cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic Table Range in Excel Online "Create Table" node

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

21 REPLIES 21

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
Helper V
Helper V

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,019)