How to “Export” Bulk Data Load Jobs data in Salesforce
If you’re a Salesforce Admin, there’s a good chance you’ve checked out the “Bulk Data Load Jobs” admin page to monitor the status of a big batch update or debug why an integration push didn’t go as expected.
This page is unique, it’s the ONLY place in Salesforce to see:
➤ What Object was Updated
➤ # of Records Processed
➤ # of Records Failed
➤ Job Start Time
For a lot of debugging, you’ll want to count the total number of records updated in a given time window, or figure out how many records a specific user submitted for updates.
This is all super helpful data, but there’s a catch.
The table view has two main issues,
1) It only shows ~50 records per page in Lightning and 110 per page in Classic
2) Clicking “View more records” on the page only adds 10 records, 10!
There is no way to export this data, no way to query it with SOQL or Apex, and it’s a total pain in the ass to page through it manually.
Lucky for you, I was determined to get the full set of our batch jobs into Excel.
My Attempts to Solve This
Write a SOQL query
My end goal was to filter this data down by the parameters in the table, and then export it. A SOQL query seemed perfect. I found the AsyncApexJob object which seemed to roughly mirror what the table was showing, but it was missing a few critical updates, namely the User who submitted the request and # of Records updated.
Write JavaScript to click the “View More” button programmatically
Trying to view the last 3,000 batch jobs by slowly paging through wasn’t going to happen manually.
I know how to write some code though, I thought, let me try to click the “View More” button with JavaScript every few seconds. I’ll let it sit for an hour and come back to the full table view, it sounded great.
It didn’t go very well…
- In Lightning Mode, the table is actually loaded as an iframe on the page holding a VisualForce component, trying to click on things programmatically in an iframe that reloads itself every time it’s clicked was proving harder than imagined.
- In Classic, the page refreshed itself every time the “View More” or “Next Page” button was clicked so looping through with JavaScript wasn’t working out well. This ultimately proved to be key to the solution.
The Break Through
When testing the JavaScript method in Salesforce Classic, I realized the URL had pagination parameters that would appear in the query string only after clicking the “View More” and “Next Page” links.
If you were to click those a few times, you’d get an ugly link like below, but it has two very important components:
https://na1.salesforce.com/750?j_id0%3Aj_id6%3Arowsperpage=140&j_id0%3Aj_id6%3Alsr=280&retURL=%2Fui%2Fsetup%2FSetup%3Fsetupid%3DJobs&setupid=AsyncApiJobStatus
Rows per Page
The first parameter ending in “rowsperpage=140” is how many rows that page should return.
Page Number
The second highlighted parameter ending in “lsr=280” is how many records it should skip, or what page it’s on.
In my example, I’m returning 140 batch jobs per page, and I’m on page 3 because I’m skipping the first two pages (140 * 2 = 280 records)
Let’s Optimize
After some testing, you can return a maximum of 1,000 records per page. This means you can click on the below URLs to get the full set of the data and follow the pattern:
First Page:
https://na1.salesforce.com/750?j_id0%3Aj_id6%3Arowsperpage=1000
Second Page:
https://na1.salesforce.com/750?j_id0%3Aj_id6%3Arowsperpage=1000&j_id0%3Aj_id6%3Alsr=1000
Third Page:
https://na1.salesforce.com/750?j_id0%3Aj_id6%3Arowsperpage=1000&j_id0%3Aj_id6%3Alsr=2000
This MUST be done in Salesforce Classic because Lightning doesn’t let you pass in the pagination parameters.
Getting into Excel
Alright, so this part isn’t quite as sexy, but it’s surprisingly effective.
Salesforce is rendering the table, as well, an actual HTML table. This means when we copy it, it can be nicely pasted into Excel.
Doing this, I was able to make a pivot table of record updates submitted by user, by object for the last 5,000 batch jobs in about 5 minutes, it was great!
Do you have a better way to do this?
I’d love to hear it, while the approach above seems a lot better than the alternatives, it’s still not as sexy as I’d like it to be. If there are some cool SOQL queries you can run to avoid all this completely, I’d love to highlight that here.
Leave a comment or email me at shaun.t.vanweelden [at] gmail.com
