in ADF Get excel worksheets from http dataset

In Azure Datafactory i'm trying to read an excel from sharepoint. I need to know the number of sheet to read from the rightmost sheet (highest index), apparently you can't use the "Get Metadata" activity since it doesn't support http datasets

I've tried using Get Metadata but you're not allowed to. I really have no idea of how to get the sheet indexes

Currently, the Get meta data activity doesn't support HTTP dataset. As a workaround, you can use the below approach using a lookup activity.

First, give a maximum number in the HTTP dataset which should be out of your sheet index range.

Give this dataset to a lookup activity in the pipeline. Upon executing this, it will give index out of range error in the lookup activity. Also, in the error, it will show the range of sheet indexes of the given excel file.

You can use this error message to extract the right most index of your excel file.

Upon the failure of the lookup activity, take an integer set variable activity and use the below expression to extract the right most index.

@int(split(split(activity('Lookup1').Error.Message,'..')[1],')''')[0])

This expression first splits the error message on the string ".." and takes the second element from the result array. Then, again it splits on the string ")'" (extra ' in the expression is an escape character) and takes the first element from the result array and converts it into integer which will be the desired Right most sheet index of the excel file.

Ignore the warning message here.

Result:

You can use this variable in your further activities after the success of the above set variable activity as per your requirement. To get the list of sheet indexes like [0,1,2], use the below expression.

@range(0,add(variables('right_index'),1))