<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">
Go to top icon

PowerShell – Assigning Tags to Azure Resources using Excel Sheet

Sourabh Majali Aug 03, 2023

Azure Azure Cloud Powershell Excel Sheet


  • PowerShell with full access
  • List of Resource Names, Tag Names & Values
  • Excel Module installed in PowerShell. If not, use the “Install-Module ImportExcel” command

Why Tagging is important?

  1. Organization: Tags help categorize and organize resources for better management.
  2. Cost Management: They enable cost tracking and optimization by associating resources with specific projects or teams.
  3. Resource Lifecycle: Tags aid in identifying resources for easier lifecycle management.
  4. Security and Compliance: They assist in enforcing security policies and compliance requirements.
  5. Automation: Tags can be used for automation and resource group management tasks.
  6. Monitoring and Reporting: They facilitate resource monitoring and reporting based on different tags.
  7. Governance and Accountability: Tags help establish ownership and accountability for resources.

Step-by-step guide for the provided PowerShell script:

  1. Connect to Azure Account: The script starts by connecting to your Azure account using the Connect-AzAccount cmdlet.
  2. Select Azure Subscription: Next, it selects a specific Azure subscription using the Select-AzSubscription cmdlet. Replace "xxxxxx" with the actual name or ID of the subscription you want to use.
  3. Prepare an Excel Sheet as follows,

    Azure Resources using Excel Sheet

  4. Add the following commands to fetch the Excel file (.xlsx) data and set the initial parameters,

    - $file = "C:\Users\Sourabh.Majali\Downloads\Testt.xlsx"
    - $sheetName = "Sheet1"
    - $objExcel = New-Object -ComObject Excel.Application
    - $workbook = $objExcel.Workbooks.Open($file)
    - $sheet = $workbook.Worksheets.Item($sheetName)
    - $objExcel.Visible=$false
    - $rowMax = ($sheet.UsedRange.Rows).count
    - $rowcustname ,$colcustname = 1,1
    - $rowsubid ,$colsubid = 1,2
    - $rowtenantid ,$coltenantid = 1,3


  5. Loop all the resources one by one from your excel sheet to get the Resources & Tags, and add the following set of commands,

    - for($i=1;$i -le $rowMax; $i++ )
    - {
    - $resourceName = $sheet.Cells.Item($rowcustname+$i,$colcustname).text
    - $resource=Get-AzResource -Name $resourceName
    - $tags = $resource.tags
    - $App = $sheet.Cells.Item($rowcustname+$i,$colcustname+1).text
    - $env = $sheet.Cells.Item($rowcustname+$i,$colcustname+2).text
    - $func = $sheet.Cells.Item($rowcustname+$i,$colcustname+3).text
    - if($tags -ne $null)
    - {
    - $tags.add("Application", $App)
    - $tags.add("Environment", $env)
    - $tags.add("Function", $func)
    - Write-Output "-------------------------------/////---------------------------------------------"
    - Write-Output "Assigning tags to "$resourceName
    - Write-Output $resource
    - Write-Output $App
    - Write-Output $env
    - Write-Output $func
    - Write-Output "-------------------------------/////---------------------------------------------"
    - #Set-azResource -ResourceId $resource.id -Tag $tags -Force  
    - }
    - Else
    - {
    - #Set-azResource -ResourceId $resource.id -Tag @{Name="Application";Value=$App,Name="Environment";Value=$env,Name="Function";Value=$func }
    - }
    - }

How does this work

  • The above commands fetch the data from your excel sheet like Resource Names and Values for the App, Env, and Function, as provided by you
  • The looping mechanism goes through all the rows
  • The Get-AzResource command is used to get the details of the resource and the existing Tags associated with it
  • $tags.add adds the read tags (Application, Environment, Function) to the tags collection of the resource using the Add() method only if the Tags are not Null
  • Assign Tags to Resource: The last line of the loop (commented out in the provided script) is the actual command to assign the updated tags to the resource using the Set-AzResource cmdlet. It is important to note that the last line is currently commented out (#Set-azResource ...) to prevent the actual tag assignments. Once you are confident with the script's behavior and have verified that the desired tags are correctly read from the Excel sheet, you can uncomment this line to apply the tags to the Azure resources
  • Remember to replace the Excel file path ($file) and the column numbers ($colcustname) based on your actual Excel sheet layout. Also, ensure that the column numbers for $colcustname+1, $colcustname+2, and $colcustname+3 are accurate for reading the tags, e.g., (App, Environment, Function), in each row


Output for PowerShell script

Tagging plays a crucial role in Azure resources in providing a powerful organizational framework. Tags facilitate the organization and monitoring of resources, resulting in streamlined resource management, effective cost optimization, improved security, and simplified automation. They also facilitate resource monitoring and reporting, ensuring better governance and accountability. Hence, implementing a well-structured tagging strategy is essential for maximizing the benefits of Azure services and maintaining a well-organized and cost-effective cloud environment.

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.