How to use WSProxy to work with Data Extensions in server-side JavaScript

How to use WSProxy to  work with Data Extensions in server-side JavaScript

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

Table of contents

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 });
Please use a unique filter parameter to fetch the ObjectID. If a wrong folder is deleted and it contained Data Extensions, these Data Extensions will no longer be visible in the UI, as their shared CategoryID is referring to a non-existing folder (many thanks to Sascha Huwald for pointing it out).

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.

  1. Hey Ivan, i am big fan of your articles!
    Thank you very much for sharing this!

    Thanks!!

  2. Thanks Ivan. I could run my first script (from Automation Studio Activity) to create empty DE – and it worked.

  3. You are welcome Sir 🙂 I tested every one of these codes before posting.

  4. Do you have an idea how to access Shared Data Extensions from a child Business Unit using WSProxy? I don’t have access to the parent, Enterprise level – would that be done as “Impersonation via WSProxy” with the ID (MID I believe?) from the top level?

  5. Have you tried putting ENT. in front of the Data Extension name?

  6. I tried but did not succeed. Looks like I can’t even move a DE from a local folder to a shared folder – ErrorCode: 2.

  7. Moving to a shared folder using SSJS is not possible in my experience. But accessing DE records is possible using ENT. prefix. Something like: { Property: ‘Name’, SimpleOperator: ‘equals’, Value: ‘ENT.YourDataExtensionName’ }

  8. Hey Ivan , thanks for such nice explanation .
    I have a query . can we also fetch the date when data extension was modified . Because i want to move only those DE’s that have been modified in last 15 days.

  9. I think so, use api.describe(‘DataExtension’) to see what data is available in the object.

  10. Hello Ivan,

    To update records in the Data Extension, the primary key(s) needs to be present in “props” or “SaveOptions”?

  11. Hello Ivan,

    Thanks for the great article.

    When using a basic function like retrieving a record for a DE. Is there really a difference between using WSproxy or core functions?

    As I read the Elliot’s article, I understood the WSproxy is suited for more complex functions that are only found on the SOAP API.

    Forgive me if I’m wrong.

    Thanks

  12. Elias, there is not much difference in speed, but where WSProxy really shines, it’s exception handling. Also, core functions work with arrays and WSProxy works with objects. Up to you to choose what suits you best. Me personally, I prefer WSProxy, because it’s easier to work with and debug. Note that WSProxy will break your code when sending a key/value pair for a field that doesn’t exist in the DE. A core function will. In this way, I just compile all the data from the form in an object, then send this object using WSProxy to multiple DEs and whatever matches with the field names gets written and no errors are returned.

  13. Hi, Ivan, when I try to put the code of Create Data extension in my onclick event, give an error -> “ReferenceError: Script is not defined”, there is something that can I do?

  14. If you’re trying to use client-side JavaScript to trigger server-side JavaScript, it will never work

  15. Hi Ivan,

    Thanks for sharing the article. This saves me days of efforts. I have a question though. I am trying to perform upsert operation on data extension folders using the saveOptions : updateAdd on dataFolder WSProxy object.

    Somehow, it repeatedly throws StatusMessage”:”SaveAction: UpdateAdd violation”,”OrdinalID”:0,”ErrorCode”:68001 Error.

    Is it not possible to perform an upsert on data extension folders

  16. Hi, These articles are very useful. However I was trying to extract all Data extension names with some filters on attributes. For Example, Name of all data extensions where value of attribute name ‘Email address’ is ‘XYZ@abc.com’.
    Please help me in this code.

  17. Hey Ivan, great article. On your first snippet, I think you meant to use customerKey here when Writing the output as result returns null.

    Write(Stringify(result));

    Also, I’ve been unable to get Shared DEs to work from a child BU, even with the ENT prefix.

  18. “it’s not a bug, it’s a feature” 😀 Write(Stringify(result)); is just an indication of printing something that you paste in PASTE CODE HERE section

  19. Priyanka, I’m sorry but I don’t assist people with their code. I rather give you the tools to figure out on your own. If I assist everyone that asks, I’ll never have any free time 😀

Comments are closed.

server-side Javascript
Up Next:

How to retrieve more than 2500 records from a Data Extension with WSProxy in server-side JavaScript

How to retrieve more than 2500 records from a Data Extension with WSProxy in server-side JavaScript