[Salesforce / Apex] Cleanse your data with super powers: Execute Anonymous Batch

Warning: use this only if you have super powers and you know exactly what you are going to do!

Developers are always lazy people, they prefer (love) writing scripts to handle data cleansing.

I rarely use Data Loader or massive CSV updates using other tools if I can, I always prefer to write my own scripts to update fields, if I’m asked to and, ofcourse, if it is possibile.

The only problem with this approach is that sometimes the logic you implemented in your script cannot be run for more than a couple of objects a time, requiring you to run your script on and on…and this is not good for lazy people.

Talking with my friend and colleague Tonone, he asked me if I had already did something like:

• A way to execute an Apex script in batch style (hell yeah, this is an Apex batch class)
• He didn’t want to deploy in production every new script (well, this can’t be a normal batch class)
• He wanted to provide the algorithm dynamically (still nope in batch)

Just after telling him that he cannot execute an anonymous batch class from the console (or in the ORGanizer 😉 ) in Execute Anonymous mode, I tought why don’t put together a Batch class and an Execute Anonymous call?

To the idea behind this is to create a batch class that executes at each iteration a script provided that is executed in “anonymous” style across all the batch’s scopes objects.

If you want to jump to the code, check out this Github repo.

If you want watch the tool in actiom jump to the Let’s play section.

Getting a Session ID

The only way to invoke Salesforce APIs from a batch job is to make a login call from the script to obtain a Session ID or (easier) to create a named credentials logged with OAuth 2.0.

To get to this create a new Connected App (from Setup > Apps > Connected App):

Save the Consumer Key and Consumer Secret for next step, while the Callback URL will be filled with a fake url (e.g. http://fake.com).

Now create an Authorization Provider ( Setup > Auth. Provider):

Use your org’s full address for the Authorize and Token Endpoint URLs (whether you have My Domain activated or not) and place Consumer Key and Consumer Secret of the previous step.

After saveing you have the callback URL needed to convalidate the OAuth 2.0 dance:

Take the Callback URL and put it in the same field of the Connected App.

Finally let’s create the Named Credential (from Setup > Named Credentials):

After saving the credential you are requested to login with your user (you need admin access or at least an API enabled user to call execute anonymous feature).

The APi name of the credential must match the one provided in the ExecuteAnonymousBatch class:

public class ExecuteAnonymousBatch implements Database.Batchable<SObject>, Database.AllowsCallouts, Database.Stateful {
    private static final String API_VERSION = '40.0';
    private static final String NAMED_CREDENTIAL = 'EXECUTE_ANONYMOUS';

Now you can make API call from asyncrhrous jobs.

Let’s play

Now you can use the tool in the following way:

String script = 'List acList = [Select Id, Name From Account Where Id IN :ID_LIST];' 
    +'for(Account acc : acList){'
    +'   acc.BillingCity = 'Gnite City';'
    +'}'
    +'update acList;';

String query = 'Select Id From Account Where BillingCity != 'Gnite City'';

Boolean sendEmailOnFinish = true;

ExecuteAnonymousBatch batch = new ExecuteAnonymousBatch(query, script, sendEmailOnFinish);
Database.executeBatch(batch, 200);

Where:

• query: is the Batch’s query
• script: is the script you want to execute per batch execution
• sendEmailOnFinish: send or not an email to the executing user in the finish method

The only thing you have to take care is to write a script that is compilable and that uses the ID_LIST variable that is injected in your script and that is of type List<ID> and that contains the IDs of all objects in the execution scope.

You can launch the script right from your tab with the Quick Console of the ORGanizer:

Results

At the end you’ll receive a marvellous email with query, script and elaboration erros (if any):

Subject: 
 [Execute Anonymous Batch] Elaboration completed: with 3 errors.
 
Body:
 Query:
         Select Id From Account limit 40
 Execute anonymous code:
         List<Account> acList = [Select Id, Name From Account Where Id IN :ID_LIST];
         for(Account acc : acList){   acc.BillingCity = 'Gnite City';} 
         update acList;
 Errors:
         '00124000005FoHPAA0': System.DmlException: Update failed. First exception on row 0 with id 00124000005FoHPAA0; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, Account cannot be updated: [] -- AnonymousBlock: line 2, column 1
         '0012400000ZBRAGAA5': System.DmlException: Update failed. First exception on row 0 with id 0012400000ZBRAGAA5; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, Account cannot be updated: [] -- AnonymousBlock: line 2, column 1
         '0012400000ZBRJNAA5': System.DmlException: Update failed. First exception on row 0 with id 0012400000ZBRJNAA5; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, Account cannot be updated: [] -- AnonymousBlock: line 2, column 1


 Sent by: [email protected]

Behind the scenes

There is not trick behind this tool, it’s just putting knowledge over knowledge.

The only thing worth to note is that I’ve used the Execute Anonymous action from the Metadata API. Why not the REST API?

Only because the SOAP version give you the debug log in response if you add the proper headers, while the REST API version needs you to query the log.

To ease script writing you can put your scripts in a File, Document or Static resource, load it by hand and pass it to your script execution.

Document scriptDoc = [Select Body From Document Where Name = 'My_Cleanse_Script'];

String query = 'Select Id From Account';

Boolean sendEmailOnFinish = true;

ExecuteAnonymousBatch batch = new ExecuteAnonymousBatch(query, scriptDoc.Body.toString(), sendEmailOnFinish);
Database.executeBatch(batch, 200);

That’s it!