Add Opportunity Contact Roles on Opportunity Update

We're sending an email campaign through Pardot excluding customers who placed an order with us or prospects whom we provided Customer Quotation (SCM Module by Financial Force) with past 30 days. To exclude this list, we must sync Salesforce Opportunity to Pardot Opportunity. Pardot opportunity is tied to a prospect where Salesforce Opportunity is not directly tied to contacts but through Accounts and OpportunityContactRoles. In order to sync Salesforce Opportunity with Pardot Opportunity, OpportunityContactRoles must be created in Salesforce Opportunity. When a Salesforce lead is converted (creating account, contact and opportunity), OpportunityContactRoles are automatically created. Similarly, whan a new Salesforce Opportunity is created OpportunityContactRoles are automatically created (via traversing Opportunity -> Account -> Contact). However, when a new contact is added to an Account within Salesforce; the new OpportunityContactRole does not automatically created. To exclude those newly added contacts to be excluded, we must run one-time script via Developer Console to add OpportunityContactRole.

To add OpportunityContactRoles for opportunities that are "Closed Won" and closing date less than 30 days; you may have to use the following Apex Code in Developer Console. Please note that Apex has per transaction governor's limit of 100 DML statements per transaction (as of this writing), so you may have to run the script multiple times with narrow CloseDate window.

List<Opportunity> opportunities = [SELECT Id, AccountId FROM Opportunity 
        WHERE StageName = 'Closed Won' AND 
        CloseDate >= 2017-02-01 and CloseDate <= 2017-02-28];
List <OpportunityContactRole> newContactRoles = 
        new List<OpportunityContactRole>();
for (Opportunity opportunity : opportunities) {
    List <Contact> contacts =
          [SELECT Id FROM Contact WHERE accountId = :opportunity.accountId];
    List <OpportunityContactRole> contactRoles = 
          [SELECT Id, ContactId FROM OpportunityContactRole 
            WHERE OpportunityId = :opportunity.Id];
    if (contacts.size() > contactRoles.size()) {
      Set<Id> myContacts = new Set<Id>();
      for (OpportunityContactRole contactRole : contactRoles) {
        myContacts.add(contactRole.ContactId);
      }
      for (Contact contact : contacts) {
        if (!myContacts.contains(contact.Id)) {
          OpportunityContactRole myContactRole = new OpportunityContactRole();
          myContactRole.ContactId = contact.Id;
          myContactRole.OpportunityId = opportunity.Id;
          newContactRoles.add(myContactRole);
          myContacts.add(contact.Id); // To prevent duplicate.
        }
      }
  } 
}
if (newContactRoles.size() > 0) insert newContactRoles;

For permanent solution, you may wish to add a "before update" trigger on Opportunity or "after insert" trigger on Contact to automatically create OpportunityContactRoles in existing opportunity.

public class OpportunityTriggerHandler {
  public static void createContactRoles(List&st;Opportunity> opportunities)
  {
    for (Opportunity opportunity : opportunities) {
      List <Contact> contacts =
            [SELECT Id FROM Contact WHERE accountId = :opportunity.accountId];
      List <OpportunityContactRole> contactRoles = 
            [SELECT Id, ContactId FROM OpportunityContactRole 
              WHERE OpportunityId = :opportunity.Id];
      if (contacts.size() > contactRoles.size()) {
        Set<Id> myContacts = new Set<Id>();
        for (OpportunityContactRole contactRole : contactRoles) {
          myContacts.add(contactRole.ContactId);
        }
        for (Contact contact : contacts) {
          if (!myContacts.contains(contact.Id)) {
            OpportunityContactRole myContactRole = new OpportunityContactRole();
            myContactRole.ContactId = contact.Id;
            myContactRole.OpportunityId = opportunity.Id;
            newContactRoles.add(myContactRole);
            myContacts.add(contact.Id); // To prevent duplicate.
          }
        }
      } 
    }
    if (newContactRoles.size() > 0) insert newContactRoles;
  }
}

To automatically add OpportunityContactRoles when an opportunity is updated, you'll need to add a "before update" trigger on Opportunity and call the static OpportunityTriggerHandler.createContactRoles(opportunities) method inside the trigger as shown below.

trigger Opportunity on Opportunity(before update) {
  if (Trigger.isBefore && Trigger.isUpdate) {
    OpportunityTriggerHandler.createContactRoles(Trigger.new);
  }
}

References:

Opportunity Syncing with Salesforce