SSJS & AMPscript in Script Activity updating SF object and logging errors into DE

Question

I'm trying to update SF object (Lead) with 3 values in Script Activity in Automation Studio and also logging errors into DE but my code constantly fails. I added Catch/Try blocks but it didn't help and of course there is no changes on SF end.

Moreover - I got info from Support that I cannot update converted Lead record. Whether the Try/Catch block help me process the code or only detects and log the error?

Code:

<script runat="server">
Platform.Load("core","1");

var sfUpdateString;
var targetDE = DataExtension.Init('Contact_Deletion_Lead_UAT').Rows.Retrieve();
 
   try{ 
   
  for (var i = 0; i < targetDE.length; i++)
  {
  
    var Field1 = targetDE[i].ID;
    var Field2 = targetDE[i].EmailBouncedReason;
    var Field3 = targetDE[i].EmailBouncedDate;
    
var sfObj = "Lead";

sfUpdateString = '';
sfUpdateString = '%'+'%[SET @uso = UpdateSingleSalesforceObject("'+ sfObj +'","'+Field1+'"';
sfUpdateString += ',"EmailBouncedReason","'+Field2+'","RFMC__c","True","EmailBouncedDate", FormatDate("' + Field3 + '", "iso"))]%'+'%';
Platform.Function.TreatAsContent(sfUpdateString);
Write(Platform.Variable.GetValue("@uso"));
  }

catch(e)
{
     var logDE = DataExtension.Init("DeletionErrorLog");
     var errorMsg = Stringify(e.message);
     var errorDsp = Stringify(e.description);
     logDE.Rows.Add({Id:Field1,Message:errorMsg, Description:errorDsp});
}

}

Answer 1

Your code has some small typos - especially the placement of the try-catch brackets could be the reason for your code failing, which is unconnected to your systems' setups.

See comments here:

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

var sfUpdateString;
var targetDE = DataExtension.Init('Contact_Deletion_Lead_UAT').Rows.Retrieve();
 
try { 
   
  for (var i = 0; i < targetDE.length; i++) {
  
    var Field1 = targetDE[i].ID;
    var Field2 = targetDE[i].EmailBouncedReason;
    var Field3 = targetDE[i].EmailBouncedDate;
    
    var sfObj = "Lead";

    sfUpdateString = '';
    sfUpdateString = '%'+'%[SET @uso = UpdateSingleSalesforceObject("'+ sfObj +'","'+Field1+'"';
    sfUpdateString += ',"EmailBouncedReason","'+Field2+'","RFMC__c","True","EmailBouncedDate", FormatDate("' + Field3 + '", "iso"))]%'+'%';
    Platform.Function.TreatAsContent(sfUpdateString);
    Write(Variable.GetValue("@uso")); // removed "platform" - Variable.GetValue is not a Platform function.
    // https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/ssjs_utilitiesVariableGetValue.html
  }
} /* moved bracket to here... */
catch(e) {
  var logDE = DataExtension.Init("DeletionErrorLog");
  var errorMsg = Stringify(e.message);
  var errorDsp = Stringify(e.description);
  logDE.Rows.Add({Id:Field1,Message:errorMsg, Description:errorDsp});
}

/* ...from here */
/* added closing tag */
</script>

Small comment - I do not see an error, but a "remark":

I am not a fan of the style of writing the AMPScript as a "sliced" SET statement with treatAsContent, but that is likely personal taste. I find it hard to read, but it does look correct.

What I would do is entering the plain AMPScript into a Code Snippet ContentBlock and calling it via "Platform.Function.ContentBlockBykey("myAMPScript"). That makes the AMPscript itself more straightforward, but of course you will have it outside of the code proper. Which I admit also isn't super practical, but still I'd prefer plain to "sliced" AMPScript for debugging.

Answer 2

I got help from SF support Changes:

1. Changing from DE name to DE External Key
2. Replacing syntax for variables from var Field1 = targetDE[i].ID; to var Field1 = targetDE[i]["Id"];
3. Putting try under the loop
4. Adding and defining variable for error logging errorId = targetDE[i]["Id"];
5. I think the game changer for replacing ID with Id (since SSJS is case-sensitive) because Id is the value I used in my DEs
6. Replaceing var errorDsp = Stringify(e.description); with var errorDsp = Stringify(e)

The final (working!) code:

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

var sfUpdateString;
var targetDE = DataExtension.Init('685AFEE2-C1DD-4D3B-ADA4-E1CAFB998057').Rows.Retrieve();
   
  for (var i = 0; i < targetDE.length; i++) {
  var errorId;
  
  try { 
  
    var Field1 = targetDE[i]["Id"];
    var Field2 = targetDE[i]["EmailBouncedReason"];
    var Field3 = targetDE[i]["EmailBouncedDate"];
    errorId = targetDE[i]["Id"];
    
    var sfObj = "Lead";

    sfUpdateString = '';
    sfUpdateString = '%'+'%[SET @uso = UpdateSingleSalesforceObject("'+ sfObj +'","'+Field1+'"';
    sfUpdateString += ',"EmailBouncedReason","'+Field2+'","Removed_From_Marketing_Cloud__c","True","EmailBouncedDate", FormatDate("' + Field3 + '", "iso"))]%'+'%';
    Platform.Function.TreatAsContent(sfUpdateString);
    Write(Variable.GetValue("@uso")); 
  }
  catch(e) {
  var logDE = DataExtension.Init("B60EF294-9CB5-481C-890A-1C98FE01B5AA");
  var errorMsg = Stringify(e.message);
  var errorDsp = Stringify(e);
  logDE.Rows.Add({Id:errorId,Message:errorMsg, Description:errorDsp});
    }
  } 


</script>