Convert 15 to 18 Digit Salesforce IDs with Google Spreadsheets

NOV 26, 2016 – Use the three (3) steps below to convert 15 digit case-sensitive 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;

tools-script-editor-in-google-sheets  Open.

google-drive-script-editor  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 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 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:

  1. Generate a report of the records you want to convert in Salesforce
  2. Export the report as a CSV
  3. Import the CSV into Google Spreadsheets
  4. In the Google Spreadsheet, select Tools > Scripts > Insert
  5. Type Salesforce in the search box
  6. Click Install on the script by bluep@quanten….
  7. Select the row containing the IDs you want to convert
  8. Select from the newly created top menu Salesforce ID > ID 15 => ID 18
  9. 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.