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

}

[Robert94]

Hi @ecooney007 ,
This formula =OFFSET(B3,0,0,SUBTOTAL(103,$B:$B),4) only finds rows up to 3610 , is there a limit, what can I do to bypass this limit and get all rows?

[Polly]

@ecooney007  - Thanks for this - Yes there is no limit on the number of rows.  So if you could do it for all rows that would be great.  Thank you.

[Ecooney0071]

Hi there,

There shouldn't be a limit. I just tried this in excel with 5000 rows, similar formula to yours, and it selected all 5000 rows.

Its been a while since I have looked at this, so please forgive me if I am wrong, repetitive, and covering ground already covered.

So the OFFSET function can get squirrelly if you try to run it in one of the cells inside the range you are focused on and complain about a circular reference or some such thing. This does not sound like your problem.

The problems I had when I was looking into this were blank rows or blank values within a cell inside one of the rows. OFFSET seemed to cut off the range if there were blank values. This was happening in my test data, fortunately in my live data I have not run into this issue, I think ever so I'm prob just lucky.

Take a peek at the 3600 rows, see if the row that it stops on has any kind of blank values or null value in one of the cells, or is just formatted differently from the norm.

[Robert94]

Hi, 
Thank you for you reply.
Yes the issue was that the column had empty cells, so it would only count the filled cells.
Thanks

[SowingAbyss]

This totally works! Thanks much.