Failing to use Environment Variable in an Excel action

[GaëlleF]

Hello there !

I'm quite new using Solutions and Environment variables and I am a bit stuck in a simple process :

What is my problem :

When trying to get the rows of an excel file, using Environment Variables, I get an error and cannot select the right excel file nor the concerned table. I get this error :

which reads "The dynamic invocation request failed with error : { "error": {"code" : "invalidRequest","message":"The provided drive id appears to be malformed, or does not represent a valid drive", "innerError":{/*followed by a bunch of different ID and tokens.*/}}}

And I am trying to find what can work, but i am quite stuck on it.

What I created :

I created a solution called NextFactory_Solution.

In it, I set 3 Environment variables :

1. FormID : a string, which will contain the ID of a MS Forms. Default value = the ID of a form I already set for dev purposes
2. SiteURL : a database, type Site, with default value a SharePoint site from my environment, let's call it TeamSite
3. LibraryID : a database, with the type List, dependant on the SiteURL variable. Default value : the standard Document Library on the SharePoint site.
   Here is the settings of LibraryID :
   
4. FilePath : a string environment variable, containing the path+filename of the excel file. In this case it is set to default value "/Client/00.Factory/MyExcelFile.xlsx"

I then added an existing flow to the solution, by clicking "Add existing > automation > cloud flow > Not in dataverse".

To sum up, this flow is triggered when a new response is submitted to a form and it goes fetch the rows of a table set in a excel file stored under the Document library of my SharePoint TeamSite.

When the flow was added to the solution, it was easy to put the FormID environment variable in the trigger, I got no error message.

I then added the SiteURL environment variable to the Excel action, and it worked well, because Power Automate found the right document libraries by itself into the Document Library field.
The issue is when i add the variable LibraryID, Power Automate do not show me all the folder to browse under the File field.

The error I talked to you about is appearing.

Now, I created the FilePath variable, but the error remains, it is just triggered by the Table field :

This error is slightly different but from the same rootcause I think. It says :
Could not retrieve values. The dynamic invocation request failed with error: {
"status": 400,
"message": "Invalid request\r\nclientRequestId: 504da1d4-b657-4ce3-94b4-5451f5b3bffe\r\nserviceRequestId: 0cb5dc87-f3fd-4a39-9399-caf5d07fdb40",
"error": {
"message": "Invalid request"
},
"source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net"
}.

Could you please help me to understand what i did wrong when initializing my Environment Variables ?

Thanks a lot !

Have a wonderful day

[MGprogrammer]

Hi,

I have the same problem. I already have the ID of the excel file, but using environment variables for the Site and Document library seems not to work.

[eric_van_waas]

I have the same issue.
If I do it without environment variable than it works fine

If I do it with environment variable it does not work.
If I check the raw input, and I see the drive (library) has a different GUID

Without environment variable

{
    "host": {
    "connectionReferenceName": "shared_excelonlinebusiness",
    "operationId": "GetItems"
    },
    "parameters": {
         "source": "https://XXXXXX.sharepoint.com/sites/RPA",
         "drive": "b!wK0B_z9fbkWxy04kzcFARcJCFeJiamNEnYxKu0pIr6jGra9duAwFSo0i_lP6sMJr",
         "file": "/SMSx_11_11_2022_10_40_19a.xlsx",
         "table": "Table1"
}
  
With environment variable
{
   "host": {
       "connectionReferenceName": "shared_excelonlinebusiness",
       "operationId": "GetItems"
   },
   "parameters": {
         "source": "https://xxxxxxxx.sharepoint.com/sites/RPA",
         "drive": "5dafadc6-0cb8-4a05-8d22-fe53fab0c26b",
        "file": "/SMSx_11_11_2022_10_40_19a.xlsx",
       "table": "Table1"
  }
}

[JoseConde88]

Hola,

Para obtener el valor de la variable de entorno para el archivo, debes seleccionar todos los valores requeridos:

 Luego le das en inspeccionar la acción:

Y el valor que está de color amarillo sería la variable de entorno de tipo cadena para el archivo de excel.   Para obtener el valor de la tabla lo hacemos con la acción de enumerar registros:

 

Inspeccionamos la acción:

 

Y el valor de color en amarillo sería la variable de entorno de tipo cadena para la tabla del archivo de excel.

 Ya con todos los valores necesarios en las variables de entorno, lo reemplazamos y funciona el flujo sin problemas.

 

[booster211]

After strugglin, pulling my hair... i found the post and discussion here.

I have exactly the same isse - it never works with environment variable neither compose or so

A BUG?

[JoseConde88]

Follow the step by step above, that's how I solved it.

[booster211]

@JoseConde88 thank you 👍

I am sure, the Excel Business Connector has some 'bugs' it can not handle environment variables.

We have to use the solution that you are recommending, or is used a Power Automate Action Series that reads the Drive ID

Solution and Discussion to this topic: Solved: Re: Get drive for Doc Library selection - Power Platform Community (microsoft.com)

Have a nice day 😎

[TomaszPoszytek]

Hi, you are all nearly there, to solve the issue. First of all, this action requires not the library reference, but the drive id which can be obtained either dynamically from graph API: Get Drive - Microsoft Graph v1.0 | Microsoft Learn or actually read from the action's "Peek code".

So first, select the library you want to get Excel data from, 

and after that, switch to "Peek view":

copy the "drive" value:

and use as a value of a "Text" env. variable, not "Data source":

Then use this variable as a custom value in action, to get data from the library:

That's it!

Good luck 🙂

[renanfm]

Genial. Pero tengo una pregunta, ¿cómo puedo obtener estos valores utilizando contenido dinámico?

[JoseConde88]

Hola, lo que explicaba en los hilos anteriores es para poder utilizar esas acciones con variables de entorno.  Me podrías dar más detalle de lo que quieres preguntarme.

[renanfm]

En primer lugar, me gustaría agradecer tu respuesta, incluso después de tanto tiempo desde tu última interacción en este post.

Bien, mi objetivo es crear tablas en Power Automate, de forma dinámica e inteligente. O sea, NO quiero utilizar configuraciones manuales, como seleccionar el sitio, definir la carpeta, elegir el archivo y seleccionar el rango de mis datos.

Bueno, ya tengo casi todo listo, las variables de mis sitios, y como identificar mis archivos.

Pero me encontré con un error al buscar, mediante variables, la ubicación de la biblioteca de documentos (Drive ID) y creo que tendré el mismo problema con el File (File ID).

Fue entonces cuando, buscando en internet, terminé aquí. Me ha parecido muy interesante esta solución porque no la conocía.

Hice una prueba usando el método "manual", seleccionando los criterios, "mostrando el código" y efectivamente los IDs están ahí.

Ahora, ¿cómo lo hago, sin tener que "echar un vistazo al código" antes de mi flujo? Por favor, explícame cómo obtengo el Drive ID del código JSON a través de un Outputs.

Gracias!

[JoseConde88]

Para obtener el Drive de manera dinámica podrías agregar un Compose después de la acción de Excel y con la siguiente expresión:

actions('Obter_planilhas_2')?['inputs']?['parameters']?['drive']

[renanfm]

Gracias