Create Bulk Variables in Azure DevOps Variable Groups with CLI

In this article, we are going learn how to leverage Azure DevOps CLI to automate the process of adding multiple variables to a Variable Group with very low manual work.

Scenario

One of the most common requirements for a DevOps Engineer is to add variables to a Variable Group which is to be taken from an ExcelSheet with hundreds of variables. Below is what the Excel looks like.

The task is to add all of the above Key-Value pairs into a Variable Group which would look like below.

Note. It’s very easy to copy/paste as there are just 20 Key-Value pairs in this scenario. However, think about a scenario where you need to repeat this for many Variable Groups for multiple projects. It’s a tedious job and there is a scope for human error.

Solution

Instead of adding them directly from the Azure DevOps Portal, we will leverage the below Tools to automate the process of automatically adding the Key-Value pairs without doing any manual Data-Entry job.

• Microsoft Excel
• Azure DevOps CLI + Azure CLI
  

Pre-requisites

1. Install Azure CLI: If not done already, Install the latest version of Azure CLI from here
2. Install the Azure DevOps CLI extension using the below command.

   az extension add --name azure-devops

3. Microsoft Excel

Let’s dive in and understand how to implement the automated solution.

Step 1. Login to Azure DevOps from the Command Line

Before we start issuing any commands, it’s mandatory to authenticate using Azure DevOps credentials.

Authenticate using Azure Account

If you are using the same account for both Azure and Azure DevOps then you can use the below command to authenticate.

Az login

Authenticate using Azure DevOps PAT

You can also authenticate using a Personal Access Token (PAT) to authenticate. More details about it here

Once the Login is successful, you can set the default Organization name and Project name in which you want to run the commands. Let’s see that next.

Step 2. Set default organization and project

Set the default Organization and Project name using the below commands so that you don’t need to worry about these details later when adding the Variables.

Set default organization

Run the below command to set the default Organization.

az DevOps configure -d organization=https://dev.azure.com/<OrganizationName>

Set Default Project

Run the below command to set the default Project.

az devops configure -d project=<ProjectName>

Step 3. Create a variable group

Create a Variable Group from the Azure DevOps portal by navigating to Pipelines à Library and clicking on the + Variable Group button as shown below.

Clicking on the + Variable Group opens up a new popup where you can create the new Variable Group as shown below.

Note. It’s mandatory to create at least one variable while creating the Variable Group. As shown above, a Testkey1 test variable is also created.

Once all the information is provided, click on the Save button to Create the Variable Group.

Step 4. Retrieve the variable group ID

As soon as you click on the Save button, the Variable Group will be created, and the page will be refreshed, which contains the Variable Group ID as shown below.

In my case, the Variable Group ID is 2, as shown in the above screenshot. This ID will be used in Step 4 and Step 5 to dynamically create the Variables using the Azure DevOps CLI commands.

Step 5. Create your first test variable using Azure DevOps CLI Command

Let’s now run the Azure DevOps CLI command to create another Test Variable. Run the below command to create the Test Variable Testkey2

az pipelines variable-group variable create --group-id 2 --name "Testkey2" --value "Testvalue2"

You can review the results by refreshing the page in the Azure DevOps. The Variable Group should show the new Variable as shown in the below screen capture.

Step 6. Generate Azure DevOps CLI commands dynamically using Excel Formula

In the previous step, we created one single test command to create a variable in the Variable Group. Now, it’s time to generate individual commands using my favorite tool, Excel.

Navigate to the Excel sheet, add another column, and paste the below formula.

=CONCAT("az pipelines variable-group variable create --group-id 2 --name """,C2,""" --value """,D2,"""")

And, apply the formula to all the rows. Once you apply the formula to all the rows, it should look something like below.

Step 7. Execute the Azure DevOps CLI commands

In the previous step, we generated all the commands in Excel. Now, it’s time to run them. Copy the entire rows (exclude the header) of commands and paste all of them commands at once in the command prompt.

The command prompt will automatically execute one command at a time, as shown below.

The above screenshots show only the last few commands for brevity.

Step 8. Review the output and delete test Variables.

Now, it’s time to review the results. Navigate to the Variable Group and refresh the page to view all the new 20 variables as shown below.

Don’t forget to delete the TestVariables.

Summary

In this article, we have discussed a simple Tip that helps a DevOps engineer automate the process of creating the variables in an existing Variable Group. The same technique could be used to create a bulk of variables for any number of Variables or projects, which saves effort.

Thanks for reading my article. If you have any better way to solve the problem, please feel free to post in comments.