This page is an example of how to write a clear programming spec or user story. It is a supplement for the article, How to Manage Outsourced Developers.

The finished product that this user story describes is here.

By: David Eisaiah Engel on 6/8/10

Converts 15 digit Salesforce IDs to 18 digit, case-insensitive IDs.

Compensation
$30 USD upon successful completion. Paid via PayPal.
You may claim authorship and post your Google Spreadsheet Script and anywhere on the web, including the Google Script Gallery. Please mention david_padburry as the author of the original convertToCaseSensitiveID function within the source code of your script.

Problem
The Salesforce.com CRM outputs IDs in 15 digit case-sensitive format. These IDs cause ambiguity in Windows programming where case sensitivity is not allowed. To solve this, Salesforce also has a 18 digit ID. The problem is that they charge big $$ to export in 18 digit formats.

Solution
Build a Google Spreadsheet Script that converts 15 digit Salesforce IDs into 18 digit salesforce IDs. The core logic has already been written in Javascript.

function convertToCaseInsensitiveId(id) {
var hash = ”;
for (var c = 0; c < 3; c++) {
var h = 0;
for (var i = 0; i < 5; i++) { var ch = id.charCodeAt(i + c * 5); if (ch >= 65 && ch <= 91) h += Math.pow(2, i); } hash += String.fromCharCode(h > 26 ? h + 48 – 26 : h + 65);
}
return id + hash;
}

This script was written by david_padburry and accessed on this Salesforce web forum post on June 8, 2010.

User Story

1. User opens/creates a spreadsheet containing a column of Salesforce IDs. Other data may be present in other columns as well.
2. User defines a range by either selecting all text in a column (i.e. A:A) or by manually selecting a list of columns
3. User runs the script (probably from Tools > Scripts > Manager)
4. Script prompts user to type the name of the range containing the IDs
5. User types the name of the range
6. For each 15 digit value in the range, the script replaces the original 15 digit value with the new 18 digit ID value
   1. if value in a cell is not 15 digits, take no action and move to the next cell in the range
7. Script stops