I want to update Salesforce Automotive Cloud Object and Marketing Cloud Data Extension using SSJS in Automation Studio

Question

I have a data extension named "SMSUnsubLog" which includes SMS unsub data of customers. I want to get data from the data extension and update salesforce object(ContactPointTypeConsent) and another MC data extension(Contact Master)

I created a code snippet like below;

%%[
var @rowCount
set @rowCount = DataExtensionRowCount("SMSUnsubLog")
var @SMSUnsubLogRows, @rsRow, @i, @IndividualId, @MessengerConsentData, @MessengerConsentRow, @MessengerConsentId, @OptOutDate, @Result, @UpsertDE

SET @SMSUnsubLogRows = LookupRows("SMSUnsubLog")
IF RowCount(@SMSUnsubLogRows) > 0 THEN

 FOR @i = 1 to RowCount(@SMSUnsubLogRows) DO
    SET @rsRow = Row(@SMSUnsubLogRows, @i)
    SET @IndividualId = Field(@rsRow, "IndividualId")
    SET @MessengerConsentData = RetrieveSalesforceObjects("ContactPointTypeConsent", "Id", "PartyId", "=", @IndividualId, 'Name', '=', 'Messenger Services')

    IF RowCount(@MessengerConsentData) >= 1 THEN
        SET @MessengerConsentRow = Row(@MessengerConsentData, 1)
        SET @MessengerConsentId = Field(@MessengerConsentRow, "Id")
        SET @OptOutDate = Field(@rsRow, "OptOutDate")
        SET @Result = UpdateSingleSalesforceObject("ContactPointTypeConsent", @MessengerConsentId, "PrivacyConsentStatus", "OptOut", "ContactPointType", "Phone", "EffectiveFrom", @OptOutDate, "EffectiveTo", @OptOutDate, "CaptureDate", @OptOutDate, "CaptureSource", "MC")
        IF @Result == 1 THEN
            SET @UpsertDE = UpsertDE("Contact Master", 1, "IndividualId", @IndividualId, "Messenger Opt-In", "False")
        ENDIF
    ENDIF
 Next @i
ENDIF
]%%

And I created a SSJS script like below. Here, the ContentBlockByKey is the CustomerKey of the code snippet I mentiond above ;

<script runat="server">
    Platform.Load("core", "1");
    var content = Platform.Function.ContentBlockByKey("f12c5265-0e7e-482b-bc5d-b41656075d94");
</script>

However, when I ran once this SSJS script, I got error message "A runtime error occurred while the SSJS Activity executed the SSJS script. Please review the SSJS activity to ensure that errors are handled properly."

Can anyone help me out with this issue?

Answer 1

I found a post on Stackexchange that mentioned when updating a Salesforce object's datetime field with a value from a Marketing Cloud datetime field using AMPscript, you need to use FormatDate() to convert the time to UTC. I modified the code to:

SET @OptOutDate = FormatDate(Field(@rsRow, "OptOutDate"), "iso")

This resolved the original runtime error. However, even though both the Marketing Cloud timezone and the Salesforce timezone are set to CEST, the OptOutDate in Salesforce UI appears to be +2 hours ahead. I'm trying to figure out what might be causing this issue.