This article explains how to look for and retrieve the list of all shared Data Extensions using server-side JavaScript in Salesforce Marketing Cloud.

What are shared Data Extensions?

Data Extension is a Marketing Cloud term for a relational database with additional features. In short, it’s a table that holds your data, indefinitely or for a limited period of time.

There are 3 main types of Data Extensions: regular, shared and synchronised.

Both regular and synchronised Data Extensions can be easily accessed with SQL, AMPscript and server-side JavaScript, providing us with the possibility to list and count the pieces of data we store.

But when it comes to the shared Data Extensions, things get complicated.

Why getting shared Data Extensions is complicated?

By definition, a shared Data Extension is a piece of data available across all Business Units (also called BUs, which are basically work spaces in Marketing Cloud).

In the Content Builder, shared Data Extensions are located in a separate folder, beside a general folder named Data Extensions and a folder named Synchronised Data Extensions.

So why is it complicated to retrieve the shared Data Extensions when we know exactly where they are located and have a direct access in the Marketing Cloud UI?

The answer is simple: it’s an illusion! The shared Data Extensions are not actually in your Business Unit, but in your parent Business Unit (also called Entreprise BU or root BU).

How to retrieve a single shared Data Extension?

When it comes to retrieving a single shared Data Extension, nothing get in our way, as long as we specify that the Data Extension is at the Entreprise level (prefix ENT.).

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    try {

        var result = DataExtension.Retrieve({
            Property: "Name",
            SimpleOperator: "equals",
            Value: "ENT.MySharedDataExtension"
        });

        Write(Stringify(result));

    } catch (error) {

        Write(Stringify(error));

    }

</script>

So far so good, but in this case we knew the exact name of the Data Extension we wanted to retrieve. What about when we don’t? Let’s find out!

How to retrieve all Data Extensions?

First, let’s recall how to get the list all the regular Data Extensions.

<script runat="server">

    Platform.Load("core", "1");
	
    var api = new Script.Util.WSProxy();
	
	try {
	
        var req = api.retrieve("DataExtension", ["Name"], {
            Property: "CustomerKey",
            SimpleOperator: "isNotNull",
            Value: " "
        });

        var name_list = [];

        var results = req.Results;

        for (var k in results) {
            name_list.push(results[k].Name);
        }

        var result = name_list.join(", ");

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	
    
</script>

This script should retrieve a nice lean list of all of our Data Extensions from a Business Unit.

But how do we access the Entreprise level Data Extensions?

In this case, we need to provide an extra parameter to the WSProxy retrieve function called queryAllAccounts. When set to true this parameter allows us to query all Business Units at once!

And since we are only interested in the Data Extensions from the parent Business Unit, we can also filter on the Client.ID property, which should be equal to the ID of the parent BU (e.g. 123456789).

<script runat="server">

    Platform.Load("core", "1");
	
    var api = new Script.Util.WSProxy();
	
	try {
	
        var req = api.retrieve("DataExtension", ["Name"], {
            LeftOperand: {
                Property: 'CustomerKey',
                SimpleOperator: 'isNotNull',
                Value: ' '
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: 'Client.ID',
                SimpleOperator: 'equals',
                Value: '123456789'
            }
        }, true);

        var name_list = [];

        var results = req.Results;

        for (var k in results) {
            name_list.push(results[k].Name);
        }

        var result = name_list.join(", ");

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	
    
</script>

How to retrieve all shared Data Extensions?

Now that we know how to retrieve the data from the parent Business Unit, the code works! But the bad news is that the it doesn’t make a distinction between a regular Data Extension and a shared Data Extension. Therefore our list is a mix of both…

In order to solve this problem, we need to find a difference between the two types of Data Extensions. But how? They are similar in every way! The only distinction is the location: all shared Data Extensions can be found in the Shared Items folder.

And what about the subfolders? As a matter of fact, all the subfolders of the Shared Items folder have property and a value that separates them from the rest: ContentType = “shared_dataextension”.

Here is what we need to do:

• Retrieve all the Data Extensions from the parent Business Unit.
• Retrieve all the IDs from all DataFolder objects (folders) from the parent Business Unit where the ContentType property is equal to “shared_dataextension“.
• Filter out the Data Extensions where the CategoryID equals to one of the DataFolder IDs.

Now, let’s code!

Full code

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    var results = {};

    var config = {
        parentBU: '123456789'
    }
	
	try {

        results.dataextensions = retrieveDataExtensions();

        results.folder_ids = retrieveDataFolderIds();

        results.filtered = filterSharedDataExtensions(results.dataextensions, results.folder_ids);

        results.count = {
            dataextensions: results.dataextensions.length,
            folder_ids: results.folder_ids.length,
            shared_dataextensions: results.filtered.length
        };

        Write(Stringify(results));
		
	} catch(error) {

        Write(Stringify(error));
        
    }

    function filterSharedDataExtensions(dataextensions, folder_ids) {

        var result = [];

        for(var k in dataextensions) {

            var dataextension = dataextensions[k];

            for(var i = 0; i < folder_ids.length; i++) {

                var folder_id = folder_ids[i];

                if(dataextension.CategoryID == folder_id) {

                    result.push(dataextension);

                }

            }

        }

        return result;

    }

    function retrieveDataExtensions() {

        var filter = {
            LeftOperand: {
                Property: 'CustomerKey',
                SimpleOperator: 'isNotNull',
                Value: ' '
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: 'Client.ID',
                SimpleOperator: 'equals',
                Value: config.parentBU
            }
        };

        var request = api.retrieve("DataExtension", ["Name", "CategoryID"], filter, true);

        var result = [];

        for(var k in request.Results) {

            result.push({
                Name: request.Results[k].Name,
                CategoryID: request.Results[k].CategoryID
            });

        }

        return result;

    }

    function retrieveDataFolderIds() {

        var filter = {
            LeftOperand: {
                Property: 'ContentType',
                SimpleOperator: 'equals',
                Value: 'shared_dataextension'
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: 'Client.ID',
                SimpleOperator: 'equals',
                Value: config.parentBU
            }
        };

        var request = api.retrieve("DataFolder", ["ID", "Name", "ContentType"], filter, true);

        var result = [];

        for(var k in request.Results) {

            result.push(request.Results[k].ID);

        }

        return result;

    }
    
</script>

Notes

1. Array.includes prototype doesn’t exist in server-side JavaScript, therefore a loop within a loop is required to perform the filter operation.
2. This technique is only made possible thanks to the awesome WSProxy function and would never work with any Core function available.

Conclusion

That’s all folks! Please share this article with all those in need and don’t forget to replace the parent BU id in the code above!

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.