A Dumb Salesforce and Spreadsheet Thing

There are two separate issues with the technology I use at work that conspired to confound me earlier this year.

1. Salesforce objects have two different unique ID constructions.
2. Common lookup functions in Google Sheets are case-insensitive.

There are two ways to determine the ID of a Salesforce object. If you need to grab a single ID quickly, you can peek at the URL. For example, you may have something akin to:

[domain].salesforce.com/u/Account/[18 Character ID]

That 18-character string is a unique ID that is case insensitive. It doesn’t matter if you make it all upper- or lower-case.

However, suppose you want to pull IDs from hundreds or thousands of objects at once. The only option is to pull a report, which gives you a different 15-character ID that is case-sensitive: if you change an A into an a, that ID could now reference a different object.

This is not explicitly documented by Salesforce anywhere I could find. It’s also an extremely curious decision: what are we gaining by not providing the full 18-character ID?

Of course, the reason I discovered this is because in Google Sheets both the VLOOKUP and MATCH functions—commonly used for finding and referencing data across different sheets—are case insensitive. This means if I have a sufficiently large report and attempt to use IDs to match data, which is the best option since account names (or contact names) may not be unique, I can get incorrect data in my cells. The spreadsheet function will find the first ID that matches while disregarding case, while I’m using an ID that assumes it can use case as a differentiating factor.

This was infuriating and required me to concoct alternative approaches to extremely common functions to work around this.

Instead of using MATCH to determine whether an account exists in some other sheet, I built this:

=IF(ISERROR(FILTER(accounts!A:A,EXACT(accounts!A:A,I2))),FALSE,TRUE)

And instead of a classic VLOOKUPI need this:

=INDEX(B2:B10, MATCH(TRUE, EXACT("a", A2:A10), 0))

These are snippets I’ve now saved because I would never remember the exact incantation otherwise, but it leaves me convinced that both of these common functions should be adjusted to at least provide an option for case sensitivity in how they run.

I discussed this in OHAC 59: Paranoid About the Cloud, but wanted to put it all in writing because I found this situation tremendously annoying.