Excel export Json for Azure Active Directory

A VBA script that converts Excel tables to JSON format and exports the data to a file at the location of your choice, in particular for Groups and AppRoles for Azure Active Directory.

You have the full code on GitHub.

Visual Basic for Application with the project - Excel export Json for Azure Active Directory — Visual Basic for Application with the project

Installation

You can use this script by following these steps:

Open up Microsoft Excel
Go to the Developer tab (For more information on how to show the developer tab, go here)
Click on Visual Basic, in the upper left corner of the window
In the toolbar at the top of the window that appears, click on File > Import file…
Select ExcelToJSON.bas and click on Open
Click on File > Import file… for a second time
Select ExcelToJSONForm.frm and click on Open (make sure that ExcelToJSONForm.frx is located in the same folder, or this step will not work)

Usage

To use the script, you need an Excel file with at least one table in it. Once you do, follow these instructions:

Go to the Developer tab
Click on Macros
Select yourfile.XLSB!ExcelToJSON.ExcelToJSON
Click on Run
In the window that appears, select which tables that you would like to export, and then click on Submit
Finally, select the name for the JSON file that will be selected as well as the location that you would like to save the file in

Example

In an Excel file, you map the Groups for Azure Active Directory that you want to create or associate. For example, you have a table like that.

GroupName	AppReg	AppRoles
{ENV}_Contributors	{ENV}_API	Designer
{ENV}_Contributors	{ENV}_API	Editor
{ENV}_Contributors	{ENV}_API	Team_Users
{ENV}_Contributors	{ENV}_API	Viewer
{ENV}_Contributors	{ENV}_UI	Designer
{ENV}_Contributors	{ENV}_UI	Editor
{ENV}_Contributors	{ENV}_UI	Team_Users
{ENV}_Contributors	{ENV}_UI	Viewer
{ENV}_Contributors	{ENV}_UI	AdminDesigner
{ENV}_Contributors	{ENV}_UI	AdminEditor
{ENV}_Contributors	{ENV}_UI	AdminViewer
{ENV}_Contributors	{ENV}_API2_API	Team_Users
{ENV}_Contributors	{ENV}_API2_API	AdminDesigner
{ENV}_Contributors	{ENV}_API2_API	AdminEditor
{ENV}_Contributors	{ENV}_API2_API	AdminViewer
{ENV}_Dev_Leads	{ENV}_API	Admin
{ENV}_Dev_Leads	{ENV}_API	Designer
{ENV}_Dev_Leads	{ENV}_API	Editor
{ENV}_Dev_Leads	{ENV}_API	Exporter
{ENV}_Dev_Leads	{ENV}_API	Importer
{ENV}_Dev_Leads	{ENV}_API	Team_Users
{ENV}_Dev_Leads	{ENV}_API	Viewer
{ENV}_Dev_Leads	{ENV}_UI	Admin
{ENV}_Dev_Leads	{ENV}_UI	Designer
{ENV}_Dev_Leads	{ENV}_UI	Editor
{ENV}_Dev_Leads	{ENV}_UI	Exporter
{ENV}_Dev_Leads	{ENV}_UI	Importer
{ENV}_Dev_Leads	{ENV}_UI	Team_Users
{ENV}_Dev_Leads	{ENV}_UI	Viewer
{ENV}_Dev_Leads	{ENV}_UI	AdminAdmin
{ENV}_Dev_Leads	{ENV}_UI	AdminDesigner
{ENV}_Dev_Leads	{ENV}_UI	AdminEditor
{ENV}_Dev_Leads	{ENV}_UI	AdminExporter
{ENV}_Dev_Leads	{ENV}_UI	AdminImporter
{ENV}_Dev_Leads	{ENV}_UI	AdminViewer

Now, the issue is how to create a Json file for this table. There is an export in Excel that creates a Json but not in the format that is required for the Active Directory. By the way, the expected json is like the following one

{
  "Groups": [
    {
      "GroupName": "{ENV}_Contributors",
      "AppRegs": [
        {
          "AppRegName": "{ENV}_API",
          "AppRoles": [
            "Designer",
            "Editor",
            "Team_Users",
            "Viewer"
          ]
        },
        {
          "AppRegName": "{ENV}_UI",
          "AppRoles": [
            "Designer",
            "Editor",
            "Team_Users",
            "Viewer",
            "AdminDesigner",
            "AdminEditor",
            "AdminViewer"
          ]
        },
        {
          "AppRegName": "{ENV}_API2_API",
          "AppRoles": [
            "Team_Users",
            "AdminDesigner",
            "AdminEditor",
            "AdminViewer"
          ]
        }
      ]
    },
    {
      "GroupName": "{ENV}_Dev_Leads",
      "AppRegs": [
        {
          "AppRegName": "{ENV}_API",
          "AppRoles": [
            "Admin",
            "Designer",
            "Editor",
            "Exporter",
            "Importer",
            "Team_Users",
            "Viewer",
            "TRSCore"
          ]
        },
        {
          "AppRegName": "{ENV}_UI",
          "AppRoles": [
            "Admin",
            "Designer",
            "Editor",
            "Exporter",
            "Importer",
            "Team_Users",
            "Viewer",
            "AdminAdmin",
            "AdminDesigner",
            "AdminEditor",
            "AdminExporter",
            "AdminImporter",
            "AdminViewer",
            "TRSCore"
          ]
        },
        {
          "AppRegName": "{ENV}_API2_API",
          "AppRoles": [
            "Team_Users",
            "AdminAdmin",
            "AdminDesigner",
            "AdminEditor",
            "AdminExporter",
            "AdminImporter",
            "AdminViewer",
            "TRSCore"
          ]
        }
      ]
    },
    {
      "GroupName": "{ENV}_DevOps",
      "AppRegs": [
        {
          "AppRegName": "{ENV}_API",
          "AppRoles": [
            "Admin",
            "Designer",
            "Editor",
            "Exporter",
            "Importer",
            "Team_Users",
            "Viewer"
          ]
        },
        {
          "AppRegName": "{ENV}_UI",
          "AppRoles": [
            "Admin",
            "Designer",
            "Editor",
            "Exporter",
            "Importer",
            "Team_Users",
            "Viewer",
            "AdminAdmin",
            "AdminDesigner",
            "AdminEditor",
            "AdminExporter",
            "AdminImporter",
            "AdminViewer"
          ]
        },
        {
          "AppRegName": "{ENV}_API2_API",
          "AppRoles": [
            "Team_Users",
            "AdminAdmin",
            "AdminDesigner",
            "AdminEditor",
            "AdminExporter",
            "AdminImporter",
            "AdminViewer"
          ]
        }
      ]
    },
    {
      "GroupName": "{ENV}_Internal_Client_Support",
      "AppRegs": [
        {
          "AppRegName": "{ENV}_API",
          "AppRoles": [
            "Designer",
            "Editor",
            "Team_Users",
            "Viewer",
            "TRSCore"
          ]
        },
        {
          "AppRegName": "{ENV}_UI",
          "AppRoles": [
            "Designer",
            "Editor",
            "Team_Users",
            "Viewer",
            "AdminDesigner",
            "AdminEditor",
            "AdminViewer",
            "TRSCore"
          ]
        },
        {
          "AppRegName": "{ENV}_API2_API",
          "AppRoles": [
            "Team_Users",
            "AdminDesigner",
            "AdminEditor",
            "AdminViewer",
            "TRSCore"
          ]
        }
      ]
    },
    {
      "GroupName": "{ENV}_Users",
      "AppRegs": [
        {
          "AppRegName": "{ENV}_API",
          "AppRoles": [
            "Editor",
            "Team_Users",
            "Viewer"
          ]
        },
        {
          "AppRegName": "{ENV}_UI",
          "AppRoles": [
            "Editor",
            "Team_Users",
            "Viewer",
            "AdminEditor",
            "AdminViewer"
          ]
        },
        {
          "AppRegName": "{ENV}_API2_API",
          "AppRoles": [
            "Team_Users",
            "AdminEditor",
            "AdminViewer"
          ]
        }
      ]
    }
  ]
}

Because this structure is a little complex, I have to create something my own export. With this code, when I run the Macro, I get a window with the list of the tables in the spreadsheet.

Example of a generated window with the list of tables - Excel export Json for Azure Active Directory — Example of a generated window with the list of tables

Then, I can select one or more tables I want to export. Remember this script generates only one json file. After that, I have to choose the location and the name of the file I want to create.

The form

The first part of Excel export Json for Azure Active Directory is to create a simple form to select the list of tables in the spreadsheet. So, the user can select one or more from the list in order to export into a json file.

The form is in the GitHub repository. There are 2 files: ExcelToJSONForm.frm and ExcelToJSONForm.frx. When in the Macro you import the first file, automatically, both files are imported in the spreadsheet.

When you run the macro, the form is shown and it displays the list of tables in the all Excel file. Select the table you want to export and then click Submit. This executes this code

Private Sub SubmitBtn_Click()
    Dim numCheckedBoxes As Integer

    For Each userFormControl In ExcelToJSONForm.Controls
        If TypeName(userFormControl) = "CheckBox" Then
            If userFormControl = True Then
                numCheckedBoxes = numCheckedBoxes + 1
            End If
        End If
    Next userFormControl
    
    If numCheckedBoxes = 0 Then
        MsgBox "Please select one or more tables before proceeding"
    Else
        
        j = 0
        
        ReDim Preserve usrSlctdTblsNameArray(0 To numCheckedBoxes)
        For Each userFormControl In ExcelToJSONForm.Controls
            If TypeName(userFormControl) = "CheckBox" Then
                If userFormControl = True Then
                    j = j + 1
                    usrSlctdTblsNameArray(j) = userFormControl.Caption
                End If
            End If
        Next userFormControl
        Me.Hide
    End If
End Sub

In the line 18, the variable usrSlctdTblsNameArray is re-redimensioned based on the number of checkboxes that are checked. This variable is defined in the ExcelToJSON.bas file.

The code

Now, everything is happening in the Sub ExcelToJSON(). This function opens the form and adds the checkboxes, one for each table in the spreadsheet.

The logic

What is the logic behind the generation of the file? As you see in the json file, there are 3 levels:

GroupName is the name of the group. This group has one or more App Registration
AppReg is the name of the application to register in the Active Directory. This has in attachment a list of AppRoles
AppRoles is the list of roles related to a specific application. This is a list of string

So, the code reads line by line the selected tables and organizes the json accordingly.

Wrap up

In conclusion, this is how I created an Excel export Json for Azure Active Directory. Let me know if the code is clear enough. Please give me your feedback or open a post in the forum.