NOV 26, 2016 – Use the three (3) steps below to convert 15 digit case-sensitive Salesforce.com ids to case-insensitive ids that are 18 digits long.
Step 1. Create a Google Sheet containing the 15 digit Salesforce ids to convert.
Step 2. In the sheet, go to Tools > Script Editor. Paste the below script:
function convertToCaseInsensitiveId(id) {
var hash = '';
for (var c = 0; c < 3; c++) {
var h = 0;
for (var i = 0; i = 65 && ch 25 ? h + 48 - 26 : h + 65);
}
return id + hash;
}
Open.
Paste.
Step 3. In Script Editor, click File > Save. Then go to the Sheet and use the script like any built-in spreadsheet function. For example, if A4 contains a 15 digit Salesforce id, in another cell you would type =convertToCaseInsensitiveId(A4).
Now, you can drag the convertToCaseInsensitiveID() function down each row of your spreadsheet to automatically fill in 18 digit salesforce IDs.
See also: Recording of my Dreamforce 2014 Session Top 10 Salesforce.com adoption tips for small business success
Original Post (outdated)
JUL 5, 2010 – We’ve made it easy for Salesforce CRM admins to convert 15 digit IDs to 18 digit IDs with Google Spreadsheets.
The Problem:
The Salesforce.com CRM assigns 15 digit IDs to records. This is a problem when integrating Salesforce with windows systems (like SQL server or naming files based on Salesforce IDs). Windows is case insensitive, so duplication often happens when using 15 digit IDs.
Salesforce developed 18 digit IDs to ensure uniqueness across case insensitive, Windows platforms. But, they make you convert the IDs yourself with a complicated formula unless you buy an expensive version of Salesforce.
The Solution:
- Generate a report of the records you want to convert in Salesforce
- Export the report as a CSV
- Import the CSV into Google Spreadsheets
- In the Google Spreadsheet, select Tools > Scripts > Insert
- Type Salesforce in the search box
- Click Install on the script by bluep@quanten….
- Select the row containing the IDs you want to convert
- Select from the newly created top menu Salesforce ID > ID 15 => ID 18
- If you want to convert 18 digit IDs to 15 digit IDs, select the row with IDs and select Salesforce ID > ID 18 => ID 15
To see how the spec was written for this script, see the post, How to Manage Outsourced Developers.
Thank you to Damon Douglas, David Padbury, and Stefan Kuehlechner for donating your valuable time to this project.
I got the same error, but found this script to work: https://docs.servicerocket.com/display/CFSJ/How+to+Convert+15-character+Salesforce+IDs+to+18-character+IDs
Thanks for weighing in!
Hi — I got an error while saving the script..
“Missing ; after for-loop condition. (line 5, file “Code”)” Dismiss
I’ll test it on my end and let you know if it works. Last time I used it was November 2016. It worked without issue then.
It appears to me, salesforce id has been removed from add on’s. Can anyone confirm ??
Sam, It has been removed from the addons menu. But the code is available here and you can recreate it by following the 3 steps above.
so Google has merged “Add-Ons” with “Scripts” – do you plan on rolling it out to be available here?
No, sorry.
At this time no
Would you be willing to share the script code? It could still be added by cut-and-paste to the script editor. Maybe on GitHub?
Brent, I have published an update today. Thomas, I have shared the code on this webpage.
Thanks for the blog – after much googling, I keep ending up here but unfortunately with Google docs updating their interface, I can’t seem to find this script. Has anyone had success since the very recent update from Google? While I have added the formula field for CASESAFEID to all of my records in Salesforce, it doesn’t help me with a previously exported list that has been associated to a different database.
Nicole, I have updated the instructions on this page. You can copy & paste the script directly and call it within the sheet like you would any other spreadsheet function.
“Runs like a charm…” – Works like a charm meets runs like a dream 🙂
FYI – In my case I had to update all references to drop the underscore, then re-add it. In other words, change “forEachCell15_” to “forEachCell15” across all affected scripts, save, then revert (repeating for the 18 character function as well). Runs like a charm now
Getting the same “Script function forEachCell15_ could not be found” problem
Tried Tomas’s workaround but that did not work either.
Any help?
Steve, I have updated the instructions on this page. You can copy & paste the script directly and call it within the sheet like you would any other spreadsheet function.
Hey Tom,
I ran into the problem myself. Have no idea what causes it but I tried looking into the code (Tools -> Script Manager then Edit) and change the names of the functions. I just searched the code for forEachCell15_ and changed it to forEachCell15 And the same for forEachCell18_ . It worked although it is just a workaround.
Tomas
Hey, great script. I am using this to assist with a lot of data conversion. When I tried the script recently, I receive and error “Script function forEachCell15_ could not be found”. Any advice on this?
Tom
Tom, I have updated the instructions on this page. You can copy & paste the script directly.
Generate 18-char IDs IN SALESFORCE, WITHOUT CODE, using a formula field: http://tinyurl.com/15CharFix
Thanks for this, Ezra.
You can use this webpage to do a single convert. I am working on a enhancement to take in CSV’s next.