Retrieve pagination loop in SSJS

Question

I wrote teh following script to get data from a data extension and then process it. I than noticed that it only gets 2500 records. I read somewhere that I need to add a pagination loop, but I couldn't find any examples how this should look.As I am still learning JavaScript, could I please ask for your assistance in adding this pagination loop.

Thank you

<script runat="server"> 

var DE = DataExtension.Init("894D9624-9313-420D-3400-74655397D00E"); 
var data = DE.Rows.Retrieve();  

//Write(Stringify(data));   

if(data.length >= 1) { 
      for (i = 0; i < data.length; i++) { 

        ...do stuff...

    } 
} 
else { 
Write("no data to retrieve"); 
}  

</script>

Answer 1

To my knowledge the built-in Core library functions cannot be paginated, but the API call (which is essentially what the function is anyway) can. And even better, you can transform it into WSProxy making it even easier AND faster.

Below is an example of pagination utilizing WSProxy: (I updated it from the sample on the page to instead aim at a Data Extension.)

var prox = new Script.Util.WSProxy(),
    objectType = "DataExtensionObject[" + deCustKey + "]",
    cols = ["FirstName","LastName",etc...],
    moreData = true,
    reqID = null,
    numItems = 0;

while(moreData) {
    moreData = false;
    var data = reqID == null ?
           prox.retrieve(objectType, cols) :
           prox.getNextBatch(objectType, reqID);

    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
            for(var i=0; i< data.Results.length; i++) {
                Platform.Response.Write(data.Results[i].Name);
                numItems++;
            }
        }
    }
}
Platform.Response.Write("<br />" + numItems + " total " + objectType);

One thing different from the Core library function is that you will need to list all of the columns you want retrieved from the call inside the cols array or they will be returned as empty/null.

Another option, if you can utilize AMPscript instead of SSJS, is to use a LookupOrderedRows function using DataExtensionRowCount() as the 'max records returned' value.

E.g. SET @Rows = LookupOrderedRows(@YourDE, DataExtensionRowCount(@YourDE),"SortableAttribute asc", "FlagField", "Value")

Now this would require some sort of flag or lookup value to work, which could be a dealbreaker. I usually solve this by having an extra field in my DEs named 'LU' which has a default value of 1. This way if I ever need to pull every record, I can still use the LookupOrderedRows function, filtering on ..."LU", 1... and it will return everything.