Display/Retrieve all columns from a data extension

Question

I just want to create a replica of my data extension on a web page where my team can see the status of each subscriber. I've tried achieving this from this documentation but it's just pulling only one column.

Answer 1

Agreed with the comment from Johannes Schapdick above: Apply this with extreme caution. This is NOT a performant solution, it will lag or time out with bigger amounts of data. Also, a public page that exposes your PII like emails might not be a good idea either. Logins are there for a reason.

Still, sometimes this approach can help and serve as a starting point.

The following concepts are applied: Build a Retrieve using a filter that applies to all records. This only works if you can filter for a field that all records have in common:

So, build a field like field "include" with a defaultValue of 1 in the DE. This common "hack" enables you to retrieve "all records", with the following limitation:

You have to loop the retrieves as one iteration only gives you 2500 records, which you can circumvent using ContinueRequest / moredataavailable.

I am sure this works with leaner code (WSProxy) as well, but this "old school" code has served me well:

<script type="javascript" runat="server">
Platform.Load("Core", "1")

// get records from Data Extensions
var dataExtensionName = "myDE";
var fieldToReturn = "Email";
// a)  you can add more variable + fields here

var rr = Platform.Function.CreateObject("RetrieveRequest");
Platform.Function.SetObjectProperty(rr, "ObjectType", "DataExtensionObject[" + dataExtensionName + "]");
Platform.Function.AddObjectArrayItem(rr, "Properties", fieldToReturn);
// copy the line above, changing the variable to the one defined at a)

/* filter condition */
var sfp1 = Platform.Function.CreateObject("SimpleFilterPart");
Platform.Function.SetObjectProperty(sfp1, "Property", "include");
Platform.Function.SetObjectProperty(sfp1, "SimpleOperator", "equals");
Platform.Function.AddObjectArrayItem(sfp1, "Value", "1");

/* assign filter to retrieverequest*/

Platform.Function.SetObjectProperty(rr, "Filter", sfp1);

var statusAndRequestId = [0, 0];
var status = statusAndRequestId[0];
var requestID = statusAndRequestId[1];

var numberOfRowsInDE = 0;
var allEmails = [];
// c) build a second array, or change to an object for more fields 

/* perform retrieves until no more data is left (= do not stop at 2500 records )*/

do {
    var results = [0,0];
    var rows = Platform.Function.InvokeRetrieve(rr, results);
    var runstatus = results[0];
    var requestId = results[1];

    if (rows != null) {

        for (var i in rows) {
         var rEmail = rows[i].Properties[0].Value;
         /*d) more fields can go here by building another variable and 
           increasing Properties[0] to 1, 2 etc. 
         allEmails.push(rEmail);
         // e) 
     }
 }
 rr.ContinueRequest = requestId;
 numberOfRowsInDE = numberOfRowsInDE + rows;

} while (runstatus == "MoreDataAvailable")

 /* end of retrieve. now you should have an array "allEmails", and the variable 
 "numberOfRowsInDE" holds the number of rows retrieved. */

 /* write each email */
     for (var i = 0; i < numberOfRowsInDE; i++) {
         Write("Email: " + allEmails[i]);

}

</script>

Hope this helps.