Working with SSJS in Script Activities

Before we get into Script Activities, it’s essential to understand Server-Side Javascript (aka SSJS). If you are new to SSJS for Marketing Cloud, then I’d recommend going through the basics on Greg Grifford’s blog site:

• SSJS 1: Intro
• SSJS 1.5: JS Background
• SSJS 2: Overview

In Marketing Cloud SSJS, we use functions primarily from Platform and the Core Libraries. Do watch out for more articles about these topics on Greg’s site and HowToSFMC.com coming up in the near future!

You can also refer to the official Salesforce SSJS documentation for Marketing Cloud for all the functions available under Platform and Core libraries.

Now that you have an idea about SSJS, let’s dive into Script Activities in Automation Studio.

What is a Script Activity?

Script Activity is part of the Automation Studio activity set – as the name suggests, you can execute SSJS inside a Script Activity, making it one of the most powerful activities in the Automation Studio suite. While the other activities focus on specific tasks like file related workflows or segmentation, the use cases for Script Activities are much more – you can execute a wide range of marketing cloud tasks programmatically.

Some of you may be wondering how we can use Script activities in Automation Studio. Well let’s check this out using a simple scenario below.

Event Coupons Validation:

Let’s say we have 2 Data Extensions as follows:

• EventDetailsDE – contains details about specific events organized by a retail company (each Event identified by a unique EventID field)

• RetailCouponsDE – contains Coupon details specific to Events (references EventId from EventDetailsDE)

Now the company has a requirement to have the EventDetailsDE updated every night with the Coupon information for each Event ID:

• EventCouponsAvailable – boolean flag to indicate if there’s any coupons available for each EventID
• NumOfCoupons – numeric value indicating number of unclaimed coupons available for the particular EventID

This can be easily achieved with a fairly simple script in SSJS as shown in the following section.

A simple solution using SSJS

Let’s take a look at a simple SSJS code that we can use to solve the above requirement:

<script runat="server" language="javascript">

   Platform.Load("core", "1");
  
   var eventsDE = DataExtension.Init("EventDetailsExternalKey"); //Initiate EventDetailsDE
   var eventRows = eventsDE.Rows.Retrieve();

   for (var i=0; i < eventRows.length; i++)
    {
       var hasCoupons = "False";
       var numOfCoupons = 0;
       var couponRows = Platform.Function.LookupRows("RetailCouponsDE",["EventID","CouponClaimed"],[eventRows[i].EventID,"False"]);
       if (couponRows && couponRows.length > 0)
        {
          	hasCoupons = "True";
          	numOfCoupons = couponRows.length;
        }
   		var updRows = Platform.Function.UpdateData("EventDetailsDE",["EventID"],[eventRows[i].EventID],["EventCouponsAvailable","NumOfCoupons"],[hasCoupons,numOfCoupons]);
    }
</script>

Breaking down the code:

1. The SSJS code must be enclosed within a <script> tag – and the runat=”server” property must be specified
2. If you plan to use any of the Core functions in the script, then you must load the Core library – (for Platform functions, no separate initialization is required). In our scenario, since we plan to use the Rows.Retrieve function from the Core library, we need to load this in the script:
   • Platform.Load(“core”, “1”); – Do note that some Core functions cannot be used in the context of an email message / send preview
3. The next step would be to identify each Event that we have in the EventDetailsDE (using the unique EventID field) – once we get the list of all the unique EventIDs, we can then iterate through each one to check for the corresponding Coupons in the RetailCouponsDE. In order to get all the Event records by EventID from the EventDetailsDE, we need to use 2 statements – one to initialize the DE and the second is to use the Rows.Retrieve Core function as shown below:
   • var eventsDE = DataExtension.Init(“EventDetailsExternalKey”); – the External Key value of the EventDetailsDE needs to be provided as the argument for the Init function
   • var eventRows = eventsDE.Rows.Retrieve(); – using the previously initialized eventsDE object, we can now retrieve all rows into the eventRows object array
4. We can use a For loop to iterate through the rows in eventRows – if there are no rows returned, then eventRows.length will return 0 and the flow shall not enter the For loop:
   • for (var i=0; i < eventRows.length; i++) { … <code here> … }
5. We will initialize two variables to use for updating the RetailCouponsDE after checking for each EventID:
   • var hasCoupons = “False”;
   • var numOfCoupons = 0;
6. Now that we have each EventID being iterated using the For loop (denoted by eventRows[i].EventID), we can use the LookUpRows Platform function to check if there are any rows in the RetailCouponsDE for the corresponding EventID -> eventRows[i].EventID where the CouponClaimed field is “False“ (means the Coupon is still available)
   • var couponRows = Platform.Function.LookupRows(“RetailCouponsDE”,[“EventID”,”CouponClaimed”],[eventRows[i].EventID,”False”]); – do note that this function only returns a maximum of 2000 rows. For our scenario let’s assume that the coupon rows will always be less than 2000.
7. If there are rows returned into the couponRows array, then we can set the 2 variables initialized earlier:
   • var hasCoupons = “True”; – setting boolean flag to True
   • var numOfCoupons = couponRows.length; – couponRows.length indicates the number of rows returned with the CouponClaimed field = “False” – i.e. number of available coupons
8. The final step would be to update the EventID row in the EventDetailsDE from within the For loop – the hasCoupons and numOfCoupons variables would have the appropriate values based on the previous steps. The UpdateData Platform function can be used for updating the EventDetailsDE by specifying the matching column (in this case EventID -> eventRows[i].EventID) and the columns to be updated (“EventCouponsAvailable” and “NumOfCoupons“)
   • var updRows = Platform.Function.UpdateData(“EventDetailsDE”,[“EventID”],[eventRows[i].EventID],[“EventCouponsAvailable”,”NumOfCoupons”],[hasCoupons,numOfCoupons]);

The above code would be sufficient to get the job done using the Script Activity – but then what happens if an exception occurs during the script execution? How would you know what records got processed and where the automation stopped? If we were to execute the script from a cloudpage, we could set debug statements to output to the screen using the Write function – but that is not possible when using Script Activities.

Exception handling and debugging in Script Activities

The try .. catch .. finally blocks

Those of you who are familiar with C#, Java or Javascript would already know about using these blocks for exception handling. If you haven’t used try .. catch blocks, do not worry – it’s quite easy. These blocks enclose a set of code that you wish to monitor for exceptions, and if one does occur, then do some actions (such as display / output the error). The basic syntax is shown below:

try
{
   <code statements where exception may occur>;
}
catch(err)
{
   <perform some action based on error captured in "err">;
}
finally
{
   <final statements to be executed - even if exception occurs or not>;
}

Please note that the “finally” block is optional – in many cases, programmers use just the try .. catch blocks. One main thing to note when using try .. catch .. finally blocks is that the scope of variables is localized – i.e. if you declare a variable in the “try” block, and then try referencing it in the “catch” or “finally” blocks, then it will show as undefined. If you do wish to reference a common variable across all the 3 blocks, then you can declare it outside (preferably just before) these blocks.

If you wish to read more about try .. catch blocks, you may refer to Jason’s blog on Using the try statement in SSJS

Logging to a Data Extension

Now that we understand the try .. catch .. finally blocks, how can we apply this for our scenario? Quite easy – we wrap most of the code we have seen earlier in a “try” block followed by a “catch” block where we will handle the exception. But in a Script Activity, we do not have the option to Write and display the error on the UI. In such cases, the best option is to log messages into a custom DE specifically created for logging.

Let’s create a standard DE called AutomationLogDE with the below fields:

I’d recommend to keep the Retention Policy On and set to a fixed number of days or weeks after which you won’t be needing the logs. For e.g. if you expect to check logs once a week, then it’d be safe to clear records older than 10 days or move them to files through Data Extract activity if you wish to archive them for future reference. We have kept the bare minimum fields to track log details.

• Timestamp – date and time of when the message was logged
• Source – custom text field which can denote the name of the automation script activity from where the logging occurs – this is quite useful if you are using the same AutomationLogDE for capturing logs across multiple Script Activities that are running in your org.
• LogDescription – message details to be logged to this field
• LogType – can be used to specify the type of message – like “Info” or “Error”

By now, you may have noticed that the AutomationLogDE is not suitable just for logging exceptions, but also any informational messages or debugging logs that you may wish to output from your code. This is quite helpful if you wish to track certain details like number of records processed, loops or iteration passes in the code, records that were processed before a specific exception occurred, etc.

Applying Exception Handling and Debug Logging for our scenario

For our scenario, we will be declaring a few variables outside the try .. catch .. finally blocks so that we can use them across.

 var logmode = 1; 	//set to 1 to log results into AutomationLogDE
 var logsource = "Event Coupon Update - Script Activity";
 var logtype = "Info";	//set to "Error" when logging error
 var logdesc;

1. logmode – you can set this to 0 or 1 – and then log data to the AutomationLogDE when the value is set to 1. Declaring this at the top makes it easy for you to toggle debug / exception logs – for e.g. if there are a lot of debug log statements in your code, you may turn it off for performance.
2. logsource – variable to capture the text to be inserted into the Source field in the AutomationLogDE – can refer to the Script Activity name for identification purposes
3. logtype – can default to “Info” when logging debug messages. You can override this value to “Error” when logging from the “catch” block
4. logdesc – will use this variable to store each message that we wish to log to the AutomationLogDE

Let’s now write a function logDE to insert a record into the AutomationLogDE whenever we wish to log a message. We can pass the logdesc variable to the function. The advantage of declaring this as a function is that you can now call logDE from multiple locations in your code and pass the message to be logged using the logdesc parameter. Also if you need to make changes to the update functionality, say for e.g. you wanted to add one more field to the DE, then having it in one place is easier to maintain rather than across multiple locations in your code.

 function logDE(logdesc)
 {
    var updLog = Platform.Function.InsertData("AutomationLogDE",["Timestamp","Source","LogDescription","LogType"],[Platform.Function.Now(1),logsource,logdesc,logtype]);
 }

We will use the InsertData Platform function to insert a log record into the AutomationLogDE – and specify the values for the 4 fields in the DE:

• Timestamp – we always default to the datetime returned by the Now() function
• Source – will use the value from the global variable logsource initialized earlier
• LogDescription – use the value from the parameter logdesc passed to the function
• LogType – will use the value from the global variable logtype – will default to “Info” unless overridden before the function call

Once we have defined the function for logging to the AutomationLogDE, we can then identify areas where we need to call this function and what values to set if the default values need to be overridden. In most debug message logs, we can leave the 3 fields as default and only need to set LogDescription field using the logdesc variable. One area where we may override the logtype variable from “Info” to “Error” is when you call the function from within the “catch” block to signify that an exception has occurred.

We will also check the value of logmode before we call the logDE function – this ensures that the logging happens only based on the desired value which can be toggled (either 0 or 1 – to disable or enable logging respectively). Below is the updated code for our scenario with the logging statements included:

<script runat="server">
 Platform.Load("core", "1");
 var logmode = 1; 	//set to 1 to log results into AutomationLogDE
 var logsource = "Event Coupon Update - Script Activity";
 var logtype = "Info";	//set to "Error" when logging error
 var logdesc;

 function logDE(logdesc)
 {
    var updLog = Platform.Function.InsertData("AutomationLogDE",["Timestamp","Source","LogDescription","LogType"],[Platform.Function.Now(1),logsource,logdesc,logtype]);
 }

 try
 {
    if (logmode)
    {
	logdesc = "Starting Automation Script Activity - Event Coupon Update";
      	logDE(logdesc);
    }
    var eventsDE = DataExtension.Init("EventDetailsExternalKey"); //Initiate EventDetailsDE
    var eventRows = eventsDE.Rows.Retrieve();

    if (logmode)
    {
   	logdesc = "Event Rows Retrieved = " + eventRows.length;
      	logDE(logdesc);
    }
   
    for (var i=0; i < eventRows.length; i++)
    {
        if (logmode)
   		{
		logdesc = "Processing Event ID: " + eventRows[i].EventID;
      		logDE(logdesc);
   		}

        var hasCoupons = "False";
       	var numOfCoupons = 0;
       	if (logmode)
       	{
        	logdesc = "Checking for available Coupons for Event ID: " + eventRows[i].EventID + " in RetailCouponsDE";
        	logDE(logdesc);
       	}
       	var couponRows = Platform.Function.LookupRows("RetailCouponsDE",["EventID","CouponClaimed"],[eventRows[i].EventID,"False"]);
       	if (couponRows && couponRows.length > 0)
        {
           	hasCoupons = "True";
           	numOfCoupons = couponRows.length;
        }
        if (logmode)
        {
          	logdesc = "Found " + numOfCoupons + " coupons for Event ID: " + eventRows[i].EventID;
        	logDE(logdesc);
          	logdesc = "Updating EventDetailsDE with Coupon availability for Event ID: " + eventRows[i].EventID;
        	logDE(logdesc);
        }
    	var updRows = Platform.Function.UpdateData("EventDetailsDE",["EventID"],[eventRows[i].EventID],["EventCouponsAvailable","NumOfCoupons"],[hasCoupons,numOfCoupons]);
        if (logmode)
        {
          	logdesc = "Updated " + updRows + " row(s) in EventDetailsDE for Event ID: " + eventRows[i].EventID;
        	logDE(logdesc);
        }
    }
 }
 catch(e)
 {
    if (logmode)
    {
      	logtype = "Error";
    	logdesc = "Exception occurred: " + Stringify(e);
     	logDE(logdesc);
    }
 }
 finally
 {
  	if (logmode)
   	{
     	      logdesc = "Exiting Automation Script Activity - Event Coupon Update";
     	      logDE(logdesc);
   	}
 }
</script>

You’ll notice that the first log message identifies the Start of the Automation Script Activity – and since we have the “finally” block, it’d be a good practice to have an Exit Activity message as well so that we know when the automation has completed – especially when you have log messages from multiple automation script activities in the same DE. The advantage of having the “finally” block is that even if you have an exception, the code in this block gets executed.

Below is a screenshot of sample log records generated in AutomationLogDE when we ran the above code using Script Activity.

Now let’s see what happens when an exception occurs. For this, we will add a forced exception to be thrown using the “throw” statement. We will add this after the log message for the Event rows retrieval and before the For loop iteration starts. So ideally we should see all the log messages till this exception, followed by the message from the “catch” block and then the “finally” block.

    if (logmode)
    {
   	logdesc = "Event Rows Retrieved = " + eventRows.length;
      	logDE(logdesc);
    }
    throw "Creating a fake error";
   
    for (var i=0; i < eventRows.length; i++)

Below is the log messages generated in AutomationLogDE when we run the code with the forced exception. From the log messages, you’ll be able to deduce the statements that got successfully executed and approximately which code section caused the exception. You will notice that the logtype = “Error” after the flow enters the “catch” block – even for the Exit message – this is to distinguish between a Successful Exit vs an Errored Exit from the Script Activity.

How do I get notified when exceptions occur?

If you wish to receive an email notification for such exception errors that are not caught by Automation Studio, you can create an automation workflow with the following steps and schedule it to run daily:

1. Create a Query Activity to pull the records that have LogType = “Error” for a specific time duration and join with a DE containing your email (and your team if needed) – update this query results to a Sendable DE
2. Create a Send Email Activity to send email to you (or your team) using the Sendable DE from Step 1 (include the log message fields in the email as well)

1. logmode > 1 – to separate base logging from detailed logging

Additional tips

• In the above scenario, we used a simple toggle feature with the logmode variable (0 and 1) to enable / disable logging. We can take this one step further by having multiple values for logmode –
  • 0 = No logging
  • 1 = Error logs only
  • 2 = Error logs and basic logs only
  • 3 = All logs
• If you wish to use this type of advanced log modes, then all you need to do is use the appropriate checks in the “if” function when calling the logDE function. This gives you a lot of flexibility to switch between log modes – especially when you may need to output only exception logs and don’t need other detailed messages.
  • In the “catch” block, for all exception message logs, we can use if (logmode > 0) { logDE(logdesc) };
  • Within the “try” and “finally” blocks, for basic messages that you wish to log – you can use if (logmode > 1) { logDE(logdesc) };
  • For any detailed messages that you wish to log, you can use if (logmode > 2) { logDE(logdesc) };

• I would highly recommend trying out the SSJS code in a Cloudpage (LandingPage) first – you can use the Preview option to test and output variable values to the screen. Once you are confident that the code snippet works, you can then move it to the Script Activity.

• For those of you have a strong programming appetite, you can deep dive into Greg’s blog on how to Access Script Activities via APIs and explore advanced scenarios with Script Activities

Hope you now have an idea on how to use SSJS in Script Activities and try out a sample scenario in your own org. Stay tuned for more articles on SSJS and Script Activities!