How to read data from a Data Extension with server-side JavaScript

How to read data from a Data Extension with server-side JavaScript

If you’ve been coding for Marketing Cloud long enough, you’ve realized that sometimes AMPscript is just not enough.

There is no way to properly debug it, add Data Extension fields if they don’t exist or look up every row without using any criteria.

In this article, I will show you how to create a Data Extension, fill it up with data and retrieve this data with server-side Javascript.

Create a new Data Extension

Consider a new Data Extension named ListOfOptions with the following structure and data:

IdOptionActive
1Quick brown foxTrue
2Big white bearTrue
3Awesome flying hawkTrue

Let’s create it by using the following server-side Javascript:

<script runat="server">
    Platform.Load("core","1.1");
    var DE = "ListOfOptions"

    /* Create a new Data Extension */
    try {
        var obj = {
            "CustomerKey" : DE,
            "Name" : DE,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "Option", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : false }
            ]
        };
        DataExtension.Add(obj);
        Write("(+) Data Extension was created successfully.");
    } catch (err) {
        Write("(!) Data Extension was not created. " + err);
    }     

    /* Deleting old data */     
    try {
		var deleteDE = DataExtension.Init(DE);
		deleteDE.Rows.Remove(["Active"],["True"]);
        Write("(+) Old records were deleted.");
	} catch(err) {
        Write("(!) No old records were deleted: " + err);
	}

    /* Insert new data */
    try {
        Platform.Function.InsertData(DE,["Id","Option","Active"],["1","Quick brown fox","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["2","Big white bear","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["3","Awesome flying hawk","True"]);
        Write("(+) New data was inserted successfully.");
    } catch(err) {
        Write("(!) New data was not inserted. " + err);
    }  
</script>

Note that it’s good practice to add some AMPscript and client-side Javascript for debugging purposes.

<script runat="server">
    Platform.Load("core","1.1");
	Variable.SetValue("name","Mister Blue");
</script>
<script runat="client">
	console.log(`%%=v(@name)=%%`);
</script> 

Read data from a Data Extension

Have you ever considered to use Data Extensions as a simple data storage for a long list of items? Data Extensions are databases, there is no reason not to use them as such.

In this example, we store a list of options that we use to populate a select field in a form.

<form>
    <label for="Options">List of options</label>
    <select name="Options">
        <option></option>
        <script runat="server">
            Platform.Load("core","1.1");
			var DE = "ListOfOptions"
            try {

                var ListOfOptionsDE = DataExtension.Init(DE);
                var Rows = ListOfOptionsDE.Rows.Retrieve();

                if (Rows.length > 0) {
                    for(var i in Rows) {
                        Write("<option>" + Rows[i]["Option"] + "</option>");
                    }
                }

                Variable.SetValue("rowMessage","(+) New data was retrieved successfully.");

            } catch (err) {
                Variable.SetValue("rowMessage","(-) No data was retrieved." + err);
            }
        </script>
    </select>
    <button>Submit</button>
</form>

<script runat="client">
	console.log(`%%=v(@rowMessage)=%%`);
</script>

In the end, all of our code will perform the following actions:

  • Create a Data Extension
  • Delete existing data if the Data Extension already exists
  • Insert data
  • Read data from the Data Extension

Full code

<!-- STEP 1 -->
<script runat="server">
    Platform.Load("core","1.1");
    var DE = "ListOfOptions"

    /* Create a new Data Extension */
    try {
        var obj = {
            "CustomerKey" : DE,
            "Name" : DE,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "Option", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : false }
            ]
        };
        DataExtension.Add(obj);
        Variable.SetValue("createMessage","(+) Data Extension was created successfully.");
    } catch (err) {
        Variable.SetValue("createMessage","(!) Data Extension was not created. " + err);
    }     

    /* Deleting old data */     
    try {
		var deleteDE = DataExtension.Init(DE);
		deleteDE.Rows.Remove(["Active"],["True"]);
        Variable.SetValue("deleteMessage","(+) Old records were deleted.");
	} catch(err) {
        Variable.SetValue("deleteMessage","(!) No old records were deleted: " + err);
	}

    /* Insert new data */
    try {
        Platform.Function.InsertData(DE,["Id","Option","Active"],["1","Quick brown fox","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["2","Big white bear","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["3","Awesome flying hawk","True"]);
        Variable.SetValue("insertMessage","(+) New data was inserted successfully.");
    } catch(err) {
        Variable.SetValue("insertMessage","(!) New data was not inserted. " + err);
    }  
</script>

<script runat="client">
	console.log(`%%=v(@createMessage)=%%`);
	console.log(`%%=v(@deleteMessage)=%%`);
	console.log(`%%=v(@insertMessage)=%%`);
</script> 

<!-- STEP 2 -->
<form>
    <label for="Options">List of options</label>
    <select name="Options">
        <option></option>
        <script runat="server">
            Platform.Load("core","1.1");
			var DE = "ListOfOptions"
            try {

                var ListOfOptionsDE = DataExtension.Init(DE);
                var Rows = ListOfOptionsDE.Rows.Retrieve();

                if (Rows.length > 0) {
                    for(var i in Rows) {
                        Write("<option>" + Rows[i]["Option"] + "</option>");
                    }
                }

                Variable.SetValue("rowMessage","(+) New data was retrieved successfully.");

            } catch (err) {
                Variable.SetValue("rowMessage","(-) No data was retrieved." + err);
            }
        </script>
    </select>
    <button>Submit</button>
</form>

<script runat="client">
	console.log(`%%=v(@rowMessage)=%%`);
</script>

Have I missed anything?

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

  1. For Some reason I’m facing issue with Rows.Retrieve funtion, I was using that function in SSJS to read but I see an error saying Object expected: Retrieve. Please help me if you have solution for this

  2. Hi Ivan
    I am very new to SSJS and programming. I am using the part of the code to just create a data extension but getting the error “Data Extension was not created. Error adding data extension.” Could you help please?
    Using below code

    Platform.Load(“core”,”1.1″);
    var DE = “ListOfOptions”

    /* Create a new Data Extension */
    try {
    var obj = {
    “CustomerKey” : SSJS_DE,
    “Name” : SSJS_DE,
    “Fields” : [
    { “Name” : “Id”, “FieldType” : “Number”, “IsPrimaryKey” : true, “IsRequired” : true },
    { “Name” : “Option”, “FieldType” : “Text”, “MaxLength” : 50 },
    { “Name” : “Active”, “FieldType” : “Boolean”, “DefaultValue” : false }
    ]
    };
    DataExtension.Add(obj);
    Write(“(+) Data Extension was created successfully.”);
    } catch (err) {
    Write(“(!) Data Extension was not created. ” + err);
    }

  3. I am too facing the same issue –
    For Some reason I’m facing issue with Rows.Retrieve funtion, I was using that function in SSJS to read but I see an error saying Object expected: Retrieve. Please help me if you have solution for this

    I am using below code –
    var DE = “POC_Abandoned_Cart_Final”
    var Abandoned_Cart = DataExtension.Init(DE);
    var data = Abandoned_Cart.Rows.Retrieve();

  4. Akash,

    Try using the CustomerKey of the dataextension instead of its name and it should work

Comments are closed.

Salesforce Marketing Cloud
Up Next:

How to create links and redirects in Marketing Cloud

How to create links and redirects in Marketing Cloud