I had a scenario where a flow with an Excel connection had to be usable in different tenants. On top of this, ALM for the flow had to be based on managed solutions. That naturally means there’s no touching and editing the flow after it’s been imported. From the platform’s perspective, environment variables are a means of defining values that differ between environments and tenants. In this case, the Excel document residing in SharePoint site X in tenant 1 would have a different SharePoint site Y in tenant 2. A perfect use case for env vars and something that should be simple, right?
Env vars and Excel
First off, I have to acknowledge I’ve not used env vars with Excel before. I really try to avoid the Excel connector and its actions, and I’ve been fairly successful at this over the years I’ve worked with flow. Must be that CRM history of mine. I thought env vars for Excel would be a simple thing, but after a few tries, I looked for answers in the community. I couldn’t find anything fast and simple, so the output is this blog post. Hope it helps!
Excel – Add a row into a table
In my use case, I’m adding a row into a table in Excel. The action I’m going to use is thus Add a row into a table. Image 1 below displays the action as it would be set if the values of the different properties were constant.
In my case, though, the values will change when the flow is moved to a different tenant. This means env vars come into play. Before we look at the env vars, let’s take a closer look at the properties in the action. They’re not as they seem! A terminology definition might be good here, too! Properties in the action are Location, Document Library, File, etc. Those properties are populated with values, which can be typed in manually or populated by adding dynamic content.
Using peek code (behind the three ellipses in the action’s top right corner), we can see the JSON schema for the action. Looking at the JSON object literal parameters, we can see that it has the following keys:
- source
- drive
- file
- table
- item/Country (based on the trigger)
- item/Capital (based on the trigger)
It turns out that Location = source, Document Library = drive, etc. The key values in the JSON are different than what are used as values for properties in the action when typing them in manually or using dynamic content. Example: The Document Library property’s value says Documents, but the drive property’s value has a GUID. I’m sure you’re already connecting the dots. When we use env vars, we need the values from the parameters JSON object literal’s keys as values in the env vars.
The env vars
Next, let’s look at the env vars themselves. When moving flows from one tenant to another, there’s no need to add any values to the env vars as the values are provided on import. Even when using managed solutions, env vars are designed in a way that they are healthy as unmanaged in production environments. In others words, they get a pass when assessing healthy ALM. Image 1 illustrates an env var with no values set before exporting the related solution as managed.
Importing to destination tenant
The final step in this use case is to import the managed solution to the destination tenant. But how do we get the values for the different properties (or keys if we’re talking JSON) from the destination tenant? Well, I created a dummy flow and used peek code just like in image 1. I then copied the values to VS Code and used them in env vars on import, as seen in image 4. There probably is an easier way to do this, but at least for me, this worked just fine.
And that’s all there is to it. I’m left wondering how I’d handle a migration of 500 flows similar to this if I had to move them from one tenant to another. Or would I just run away? Maybe I’ll find out someday.