2

I am new to SSJS/WSProxy. Any idea, how you can see the retrieved records from data Extension and put them in an Array.

Question 1: Also What are the different methods to see the results in SSJS or WSProxy?

Question 2: My requirement is to retrieve records from DE and update the subscriber status.

<script runat="server">
Platform.Load("Core","1.1.1");

var subkey = '3b0-65b5-496f-8133-43ec53';
var Email = "tkakk@abc.com";

var prox = new Script.Util.WSProxy();

var props = [
{ Name: "SubscriberKey", Value: subkey },
{ Name: "Email Address", Value: email },
{ Name: "Reason", Value: "App Data unsub" }
 ];

try{
 var data = prox.updateItem("subscriber",props);
  Write(Stringify(data));
 }catch(e){
Write(Stringify(e));
}
</script>
1
  • 1
    Based on what would you like to retrieve those records - are there any criteria or do you just want to retrieve all records from a DE? And when you say you want to update the subscriber status - do you have a status column in the DE that you are retrieving data from, or do you mean the subscriber object? – zuzannamj Apr 5 '20 at 7:39
3

For retrieving data from a data extension, you don't need to use WSProxy - using AMPscript or SSJS is sufficient. WSProxy is meant for more advanced operations that require using Marketing Cloud's SOAP API.

The retrieved data comes in an array of objects in JSON format and one of the ways to "see" it, is to use the Write() function.

Here are some examples:

  1. Rows.Retrieve - Retrieves up to 2500 rows of data in a data extension. This function returns information in an array of objects in JSON format:
<script runat="server">
Platform.Load("Core","1");
try {

//initiate DE using it's External Key  
var myDE = DataExtension.Init("167E73F6-2170-4065-A288-xxxxx");

//retrieve data without filters  
var data = myDE.Rows.Retrieve();  

Write(Stringify(data));

  } catch (error) {
    Write("<br>error: " + Stringify(error));
}
</script>
  1. Rows.Retrieve with a filter. This function returns information in an array of objects in JSON format:
<script runat="server">
Platform.Load("Core","1");
try {

//initiate DE using it's External Key  
var myDE = DataExtension.Init("167E73F6-2170-4065-A288-xxxxx");

//define filter criteria  
var filter = {Property:"Company",SimpleOperator:"equals",Value:"Non LLC"};

//retrieve data with a simple filters  
var data = myDE.Rows.Retrieve(filter);  

Write(Stringify(data));

  } catch (error) {
    Write("<br>error: " + Stringify(error));
}
</script>

You can also use a more complex filter if needed:

var filter = {
                LeftOperand:{
                    Property:"Age",
                    SimpleOperator:"greaterThan",
                    Value:20
                },
                LogicalOperator:"AND",
                RightOperand:{
                    Property:"FirstName",
                    SimpleOperator:"equals",
                    Value:"Angel"
        }};
  1. Rows.Lookup - Returns columns that match the specified values. This function returns information in an array of objects in JSON format:
<script runat="server">
Platform.Load("Core","1");
try {

//initiate DE using it's External Key  
var myDE = DataExtension.Init("167E73F6-2170-4065-A288-xxxxx");

//retrieve data usig lookup
var data = myDE.Rows.Lookup(["FirstName"], ["Angel"]);  

Write(Stringify(data));

  } catch (error) {
    Write("<br>error: " + Stringify(error));
}
</script>
  1. For retrieving data using WSProxy, see this example: Retrieve Data Extension rows using WSProxy

To answer the second part of your question, let's say you want to find a subscriber with a given SubscriberKey and update their status in the data extension:

<script runat="server">
Platform.Load("Core","1");
try {

//initiate DE using it's External Key  
var myDE = DataExtension.Init("167E73F6-2170-4065-A288-xxxxx");

//update status for subscriber with a given key  
myDE.Rows.Update({SubscriberKey:"123456"}, ["Status"], ["Unsubscribed"]);

  } catch (error) {
    Write("<br>error: " + Stringify(error));
}
</script> 

If you're updating more records in one go, you will need to add a loop.

2
  • You are updating a Data Extension and the OP wants to update the Subscriber Model. Likely should use Subscriber.Upsert (developer.salesforce.com/docs/…) or similar function instead – Gortonington Apr 6 '20 at 13:00
  • 1
    Yep, I wasn't sure which one they meant, so I asked about that in the comment under OP's question :-) – zuzannamj Apr 6 '20 at 14:54
2

I have written a script to do this very thing. It basically retrieves a Data Extension via WSProxy that contains all the fields inside of it and converts them into Attributes to be updated inside of All Subscribers via WSProxy. You would just need to have a field in your DE named 'Status' and have the value be what you want to change the Status to.

The reason I use this instead of the SSJS core functions or the 'Platform'/AMPscript functions is because WSProxy can do batch retrieve AND updates. Basically meaning that the entire bulk that you retrieve from your first WSProxy call can then be inserted inside a single call for updating inside of Subscribers.

This alone is a great increase of efficiency. Another good reason to use WSProxy instead is that this allows you to handle an audience that is greater than 2000 if necessary via the 'HasMoreRows' property inside the return. It will break it down into chunks via pagination and do retrieve/update call per chunk. Meaning if we paginate at 2000, and have 1000 records, this would do 5 retrieves and 5 update calls. Which is much simpler and more efficient than trying to do 5 core Rows.Retrieves (and figure out how to filter to get the next 'batch' [likely need to do SQL queries to assign 'batch' numbers inside the DE for the lookup]) and 10000 Subscriber.Update functions.

See below for my recommended script:

<script runat="server">

var mid = '8675309'; //MID of target BU -- OPTIONAL Default is current BU of script/page
var deCustKey = 'my_de1'; //your DE's CustomerKey / External Key
var prox = new Script.Util.WSProxy(); //creates proxy

var subArr = []; //To hold batch of subscriber objects
var moreData = true; //To validate if more data in Retrieve
var reqID = null; //Used with Batch Retrieve to more to next batch

while(moreData) {

    var moreData = false;

    var deReturn = retrieveDERows(mid,deCustKey,reqID);

    var moreData = deReturn.HasMoreRows;
    var reqID = deReturn.RequestID;

    for (var a = 0; a < deReturn.Results.length; a++) {

        var attributes = [];
        var updateObject = {};

        var results = deReturn.Results[a];

        for (var i = 0; i < results.Properties.length; i++) {

            var name =  results.Properties[i].Name;
            var value = results.Properties[i].Value;

            if (name == "FirstName" || name == "LastName") {
                name = name.replace(/tName/g,"t Name")
            }

            if (name == "SubscriberKey") {
                var subkKey = value;
            }
            else if (name == "EmailAddress") {
                var email = value;
            }
            else if (name == "Status") {
                var status = value;
            }
            else {
                var object = Platform.Function.ParseJSON('{Name:"' + name + '",Value:"' + value + '"}')
                attributes.push(object);
            }

        }

        updateObject.SubscriberKey = subKey;
        updateObject.EmailAddress = email;
        if(status) {
            updateObject.Status = status;
        }
        updateObject.Attributes = attributes;

        subArr.push(updateObject);
    }

    var subUpdate = upsertIntoSubscribers(mid,subArr);

}

function retrieveDERows(mid,deCustKey,reqID) {

    if(mid) {
        prox.setClientId({ "ID": mid }); //Impersonates the BU
    }
    var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved

    if (reqID == null) {
        var desc = prox.retrieve("DataExtensionObject[" + deCustKey + "]", cols); //executes the proxy call
    } else {
        desc = prox.getNextBatch("DataExtensionObject[" + deCustKey + "]", reqID);
    }

    return desc;
}

function upsertIntoSubscribers(mid,subArr) {

    if(mid) {
        prox.setClientId({ "ID": mid }); //Impersonates the BU
    }
    var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

    var res = prox.updateBatch('Subscriber', subArr, options);

    return res;
}

</script>

One note is that the above does not handle List Subscriptions for the subscriber. It can be modified to do this if necessary, but there are a few different ways to achieve this and judging from your question, this did not seem to be a factor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.