This article explains how to use WSProxy to work with Data Extensions in server-side Javascript in Salesforce Marketing Cloud.

Table of contents

Basics

• Create a Data Extension
• Move a Data Extension
• Delete a Data Extension
• Retrieve all Data Extension names

Records

• Create records in a Data Extension
• Update records in a Data Extension
• Delete a record from a Data Extension
• Clear all records from a Data Extension
• Retrieve records from a Data Extension

Folders

• Create a Data Extension folder
• Delete a Data Extension folder
• Retrieve a Data Extension folder
• Retrieve a Data Extension folder path
• Retrieve all Data Extension folders

Fields

• Create fields in a Data Extension
• Update field attributes in a Data Extension
• Retrieve all field attributes from a Data Extension

Why use WSProxy?

Compared to the server-side JavaScript Core functions, WSProxy is faster and provides a better support for the error handling.

It also uses JSON objects, instead of arrays when it comes to retrieving records from Data Extensions.

For more information, please have a look at this amazing article by Eliot Harper.

Before we start

Please consider the code below as the starting point, where the CustomerKey of a Data Extension named “Customers” is retrieved.

<script runat="server">
    Platform.Load("core", "1.1.1");
	
    var api = new Script.Util.WSProxy();
	
	try {
	
		var dataExtensionName = "Customers";

		var req = api.retrieve("DataExtension", ["CustomerKey"], {
			Property: "Name",
			SimpleOperator: "equals",
			Value: dataExtensionName
		});

		var customerKey = req.Results[0].CustomerKey;

		/////////////////////////////////////////////////
        ///// PASTE CODE HERE
        /////////////////////////////////////////////////

		Write(Stringify(result));
		
	} catch(error) {
		Write(Stringify(error));
	}	
</script>

All of the following code snippets can be used within this code.

Basics

---

Create a Data Extension

At this point, the Data Extension named “Customers” doesn’t exist yet. Let’s have a look at how to create one.

var fields = [
	{
		"Name": "FirstName",
		"FieldType": "Text",
		"MaxLength": 50
	},
	{
		"Name": "LastName",
		"FieldType": "Text",
		"MaxLength": 80
	}, 
	{
		"Name": "EmailAddress",
		"FieldType": "EmailAddress",
		"IsPrimaryKey": true,
		"IsRequired" : true
	}
];

var config = {
	"CustomerKey": String(Platform.Function.GUID()).toUpperCase(),
	"Name": "Customers",
	"CategoryID": 0,
	"Fields": fields
};

var result = api.createItem("DataExtension", config); 

Move a Data Extension

Moving a Data Extension from one folder to another is the matter of changing it’s CategoryID attribute.

var catId = 0;

var config = {
	"CustomerKey": customerKey,
	"CategoryID": catId
};

var result = api.updateItem("DataExtension", config);

Delete a Data Extension

In order to delete anything in Marketing Cloud, CustomerKey is just not enough. Providing the ObjectID and the name of the object we want to delete is how it becomes possible.

var req = api.retrieve("DataExtension", ["ObjectID"], {
	Property: "DataExtension.CustomerKey",
	SimpleOperator: "equals",
	Value: customerKey
});

var objectId = req.Results[0].ObjectID;

var result = api.deleteItem("DataExtension", { "ObjectID": objectId });

Retrieve all Data Extension names

Retrieving all the available Data Extension names requires 2 things: filtering on a common denominator (non-empty CustomerKey) and ignoring all the names starting with an underscore character.

var req = api.retrieve("DataExtension", ["Name"], {
	Property: "CustomerKey",
	SimpleOperator: "isNotNull",
	Value: " "
});

var arr = [];

var results = req.Results;

for (var k in results) {
	var nm = results[k].Name;
	if (nm.indexOf("_") != 0) arr.push(nm);
}

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

Folder

---

Create a Data Extension folder

In order to create a Data Extension folder, we need to provide a Parent Folder ID, which we first need to retrieve by the Name.

Please note that Description attribute is required as well.

var req = api.retrieve("DataFolder", ["ID"], {
    Property: "Name",
    SimpleOperator: "equals",
    Value: "Data Extensions"
});

var parentFolderId = req.Results[0].ID;

var config = {
    "Name": "MyNewFolder",
    "Description": "API Created Folder",
    "ParentFolder": {
        ID : parentFolderId,
        IDSpecified: true
    },
	"IsActive": true,
    "IsEditable": true,
    "AllowChildren": true,
    "ContentType": "dataextension"
};

var result = api.createItem("DataFolder", config);  

Delete a Data Extension folder

In order to delete a Folder, we need to use the ObjectID.

var result = api.deleteItem("DataFolder", { "ObjectID": objectId });

Retrieve a Data Extension folder

Remember, CategoryID of the Data Extension object is the ID of the DataFolder object.

var req = api.retrieve("DataExtension", ["CategoryID"], {
	Property: "DataExtension.CustomerKey",
	SimpleOperator: "equals",
	Value: customerKey
});

var catId = req.Results[0].CategoryID;

var req = api.retrieve("DataFolder", ["Name"], {
	Property: "ID",
	SimpleOperator: "equals",
	Value: catId
});

var result = req.Results[0].Name;

Retrieve a Data Extension folder path

Each folder has a ParentFolder.ID attribute. Therefore, by looping through every parent and retrieving the name of the folder, we can build a path to the Data Extension of our choosing.

var req = api.retrieve("DataExtension", ["Name","CategoryID"], {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: customerKey
});

var list = [req.Results[0].Name];
var id = req.Results[0].CategoryID;

while(id > 0) {

    var req = api.retrieve("DataFolder", ["Name","ParentFolder.ID"], { 
        Property: "ID", 
        SimpleOperator: "equals", 
        Value: id 
    });

    list.push(req.Results[0].Name);
    id = req.Results[0].ParentFolder.ID;
}

list = list.reverse();

var result = list.join(" / ");

Retrieve all Data Extension folders

DataFolder objects have different Content Types. Therefore, we just need to filter on the Data Extension type: “dataextension“.

var req = api.retrieve("DataFolder", ["Name"], {
	Property: "ContentType",
	SimpleOperator: "equals",
	Value: "dataextension"
});

var list = [];

var results = req.Results;

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

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

Records

---

Create records in a Data Extension

In order to create a record, we need to split the data into the Name-Value pairs.

var props = [
	{
		"Name": "FirstName",
		"Value": "John"
	},
	{
		"Name": "LastName",
		"Value": "Smith"
	},
	{
		"Name": "EmailAddress",
		"Value": "john.smith@mail.com"
	}
];

var result = api.createItem('DataExtensionObject', {
	CustomerKey: customerKey,
	Properties: props
});

Update records in a Data Extension

Remember that an update action can only be performed on a Data Extension with a Primary Key field.

var props = [
	{
		"Name": "LastName",
		"Value": "Malkovitch"
	},
	{
		"Name": "EmailAddress",
		"Value": "john.smith@mail.com"
	}
];

var options = {
	SaveOptions: [
		{
			PropertyName: 'EmailAddress',
			SaveAction: 'UpdateAdd'
		}
	]
};

var result = api.updateItem('DataExtensionObject', {
	CustomerKey: customerKey,
	Properties: props
}, options);

Delete a record from a Data Extension

Although the Properties attribute is used to create and/or update a record in a Data Extension, it’s the Keys attribute that we need to use to delete a record.

var result = api.deleteItem("DataExtensionObject", { 
	CustomerKey: customerKey,
	Keys: [
		{
			Name: "EmailAddress",
			Value: "john.smith@mail.com"
		}
	] 
});

Clear all records from a Data Extension

In order to clear all the data, we need to perform a ClearData action on the Data Extension defined by the CustomerKey.

var properties = {
	CustomerKey: customerKey
};
var action = "ClearData"
var options = {}
var res = api.performItem("DataExtension", properties, action, options);

Retrieve records from a Data Extension

Please note that the syntax for the retrieval is different from everything that came before. We also need to provide the names of the fields we wish to retrieve.

var result = api.retrieve(
	"DataExtensionObject[" + customerKey + "]", 
	["FirstName", "LastName","EmailAddress"]
);

Fields

---

Create fields in a Data Extension

Here is an example for the creation of each field type.

var fields = [
	{
		"Name": "Text",
		"FieldType": "Text",
		"MaxLength": 50,
		"DefaultValue": "Hellow world"
	},
	{
		"Name": "Number",
		"FieldType": "Number"
	},
	{
		"Name": "Date",
		"FieldType": "Date"
	},
	{
		"Name": "Boolean",
		"FieldType": "Boolean",
		"DefaultValue": false
	},
	{
		"Name": "Email",
		"FieldType": "EmailAddress"
	},
	{
		"Name": "Phone",
		"FieldType": "Phone"
	},
	{
		"Name": "Decimal",
		"FieldType": "Decimal",
		"MaxLength": 18,
		"Scale": 2
	},
	{
		"Name": "Locale",
		"FieldType": "Locale"
	}
];

var result = api.updateItem('DataExtension', {
	"CustomerKey": customerKey,
	"Fields": fields
});

Update field attributes in a Data Extension

Please note that updating an existing field attribute is not always possible, as there are many restrictions based on the initial field configuration.

var field = {
	"Name": "LastName",
	"DefaultValue": "Doe",
	"ObjectID": null
}

var filter = {
	LeftOperand: {
		Property: "DataExtension.CustomerKey",
		SimpleOperator: "equals",
		Value: customerKey
	},
	LogicalOperator: "AND",
	RightOperand: {
		Property: "Name",
		SimpleOperator: "equals",
		Value: field.Name
	}
}

var req = api.retrieve("DataExtensionField", ["ObjectID"], filter);

var objectId = req.Results[0].ObjectID;

field.ObjectID = objectId;

var result = api.updateItem("DataExtension", {
	CustomerKey: customerKey,
	Fields: [field]
});

Retrieve all field attributes from a Data Extension

In this example we only retrieve the attributes that we can use later on.

var cols = [
	"Name", 
	"MaxLength", 
	"IsRequired", 
	"FieldType", 
	"DefaultValue", 
	"Ordinal"
];

var result = api.retrieve("DataExtensionField", cols, {
	Property: "DataExtension.CustomerKey",
	SimpleOperator: "equals",
	Value: customerKey
});

Considerations

As for the Core functions, the WSProxy retrieve functions are subject to the limitation of 2500 records retrieved by request.

Nothing to worry about though.

If you want to retrieve than 2500 records, please have a look at this article.

Conclusion

These are all the best code snippets I know when it comes to manipulating Data Extensions with WSProxy.

It took me many hours to assemble and test everything you see in this article.

Feel free to share, subscribe to my Newsletter below and/or pay me a nice hot coffee.

Have I missed anything?

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