Dynamic content Excel, Location and Document Library Fields Problem

[rudioliveira]

I'm trying to allocate the path of the excel file dynamically on flow, so far I got the file identifier and the id of the library, but i'm struggling with the paths it allways says file not found.

The variable that I'm saving is a string like this "4ea21276-06de-4573-b6e3-d77118383492"

Basically the code that I get with that configuration is this one,

{
    "inputs": {
        "host": {
            "connection": {
                "name": "@parameters('$connections')['shared_excelonlinebusiness']['connectionId']"
            }
        },
        "method": "get",
        "path": "/drives/@{encodeURIComponent(variables('TESTE'))}/files/@{encodeURIComponent(body('Get_file_properties_(_GET_THE_PROPERTIES_TO_GET_THE_FILE_IDENTIFIER)')?['{Identifier}'])}/tables/@{encodeURIComponent('DADOS')}/items/@{encodeURIComponent(encodeURIComponent('PRINCIPAL'))}",
        "queries": {
            "source": "https://XXXXX.sharepoint.com/sites/ServiceCreationTest/",
            "idColumn": "DADOS"
        },
        "authentication": "@parameters('$authentication')"
    },

But the code that runs ok, adding the path manually is this one.

{
    "inputs": {
        "host": {
            "connection": {
                "name": "@parameters('$connections')['shared_excelonlinebusiness']['connectionId']"
            }
        },
        "method": "get",
        "path": "/drives/@{encodeURIComponent('b!tfzL8VMY3EKaK9KMprFTSwsHkWyulbpJsjtEN2RJal12EqJO3gZzRbbj13EYODSS')}/files/@{encodeURIComponent('012ZOE5FL272SK26N3SNCLRJKLARB7XT2E')}/tables/@{encodeURIComponent('DADOS')}/items/@{encodeURIComponent(encodeURIComponent('PRINCIPAL'))}",
        "queries": {
            "source": "https://XXXXXX.sharepoint.com/sites/ServiceCreationTest/",
            "idColumn": "DADOS"
        },
        "authentication": "@parameters('$authentication')"
    },

The inputs on the flow, test running, are this

{
    "method": "get",
    "queries": {
        "source": "https://XXXX.sharepoint.com/sites/ServiceCreationTest/",
        "idColumn": "DADOS"
    },
    "path": "/drives/4ea21276-06de-4573-b6e3-d77118383492/files/Documenti%252bcondivisi%25252fProjeto%252bQ%25252fEst%2525c3%2525b3rias%25252fLOG%252b%252526%252bCONFIGURA%2525c3%252587%2525c3%252595ES.xlsx/tables/DADOS/items/PRINCIPAL",
    "host": {
        "api": {
            "runtimeUrl": "https://flow-apim-europe-001-westeurope-01.azure-apim.net/apim/excelonlinebusiness"
        },
        "connection": {
            "name": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-f65c13fd-90d9-4d33-8ea5-cd8b3e708267"
        }
    },
    "authentication": {
        "scheme": "Key",
        "parameter": "*sanitized*",
        "type": "Raw"
    }
}

Tried different things is allways giving me file not found.

Any ideas, thanks

[fchopo]

Hello @rudioliveira 

When executing the "Get Row" operation, the document library is based on a variable called "TESTE", which I think it stores the document library name, but it should store the document library id.

Based on your screen captures, here are the differences:

 Hope it helps!

Ferran

[rudioliveira]

@fchopo it seems so thanks for the observation, can you help me getting the document library ID?

[fchopo]

Hello @rudioliveira 

You could use "Get all lists and libraries" SharePoint action and get doc library name, but I'm not sure whether it will solve your problem. Could you create the flow without using dynamic values for doc library, and use fixed ones? (using a switch action for example).

Hope it helps.

Ferran

[rudioliveira]

Hi @fchopo

The problem is that the excel in not using the id or GUID of the library is using another identifier, i can get the id, the GUID but not that identifier

as you can see the request is going with the id

"/drives/4ea21276-06de-4573-b6e3-d77118383492/files/3a80e03e-ad64-4605-8dbe-b09ae8ec7bb8/tables/DADOS/items/PRINCIPAL

if you check the original you can see that he is using another type of identification

 "/drives/b%21tfzL8VMY3EKaK9KMprFTSwsHkWyulbpJsjtEN2RJal12EqJO3gZzRbbj13EYODSS/files/012ZOE5FKCXGP5CYHRGREZSCPAIYMFM3CA/tables/%7B3797CE84-260B-45F4-8CC6-DAD5EB0E2393%7D/items/PRINCIPAL",

any idea what where to get the identifier like that?

[fchopo · Accepted solution]

Hello @rudioliveira 

Have a look at this excellent post by @Jcook on how to use dynamic document libraries and files to get some data from Excel files: https://flowaltdelete.ca/2020/06/25/power-automate-excel-dynamic-file/

You should use "Send an HTTP request to SharePoint" to get Drive IDs, and filter it according to the doc library you are working on.

Hope it helps!

Ferran