The Scripting Toolkit

Toolkit architectureThe Scripting Toolkit is a JavaScript Ajax wrapper around the API:
  • The Scripting Toolkit is available for any organization that has API access.
  • The Scripting Toolkit is based on the Ajax Toolkit and Microsoft Windows Scripting Host (native in the OS).
  • The Scripting Toolkit supports the latest Ajax Toolkit v28.0
  • You can execute any call in the API, and access any API object that you normally have access to.
  • You can issue asynchronous calls, and use callback functions to handle the results. For more information on the Ajax Toolkit, see this page.
  • The Scripting Toolkit can be run on any Windows machine
  • You can use any API available on your machine : any COM object, any moniker...
  • You can create batch scripts to create, retrieve, update or delete Salesforce data as well as data in your Back-Office.
  • The Scripting Toolkit is a quick way to interface Salesforce with your information system.
Note
noteBefore you use the Scripting Toolkit, you should be familiar with JavaScript and the AJAX Toolkit and with the information about the API in the Apex Web Services Developer's Guide.
If you feel comfortable with the Ajax Toolkit, you will be able to create your own batchs using the Scripting Toolkit in minutes.

This document explains how to use the Scripting Toolkit to build batch interfaces.

A batch using the Scripting Toolkit is a standalone Javascript file (*.js) called by the engine. A batch run in console mode (command line).
The scripting Toolkit is a standalone Windows Scrirpting File (*.wsf) where you don't have to put your code, the engine can remain invariant. With the Scripting Toolkit, you can create a javascript batch that contains Ajax calls and processes Salesforce data as well as access to your own back-office data.

You can create Javascripts in your favorite development environment, or even using notepad. For example, you can use the SciTe editor, Textpad or Visual Studio.

When to Use the Scripting Toolkit

The following are examples of appropriate uses:

  • You need a batch to load, extract and manage data using business rules
  • Interface Salesforce with any Back-Office (rdbms, webservices, file exchange...)
  • Need a lightweight interface, quick and easy development and deployment.
  • If your skills are only Javascript and not other languages
  • Load or extract CSV files, with transformation of field values
  • Propagate your data to a database
  • Create dynamically a power point presentation based on your data : using OLE to enhance your reporting experience.
  • Identify and correct bad data (data quality)
  • Propagate some data from one org to another
  • Mass update old list values (after an update of the picklist values)
  • Put it in the crontab to send emails with Key indicators on a weekly basis
  • Poll data at a defined frequency to trigger an action (sending mail, running an interface..)
  • Extract documents and attachments to free some storage space
  • ...

The Scripting Toolkit should not be used :

  • if you require user interaction in the browser : prefer the Ajax Toolkit
  • if you need only to update a huge amount of Salesforce data : prefer the Apex language
  • if you need to run your batchs using Unix : develop in Java instead.
  • for an initial data loading : prefer the Data Loader

The following are examples of scenarios that require case-by-case analysis:

  • Running time-critical processes : perhaps using both Apex and the Scripting Toolkit.
  • Running as a service : either a specific development or using srvany.exe to create a scripted service.

An example of inappropriate usage is polling data every second. This would require too much API calls, and there is a risk to reach the maximum amount of API calls.

Scripting Toolkit Support Policy

The current release of the Scripting Toolkit is the only version that receives bug fixes and enhancements. When a new version is released, the previous version is not available anymore.
There is no official support of this Toolkit as it is submitted in the Salesforce Labs
By downloading the Scripting Toolkit, you get all the source code. You cannot redistribute this Toolkit, event if you have made changes.
You can use the toolkit for free, at your own risk (take it "as is")

Download the Scripting Toolkit

Latest version : 0.5.0
For each of these files, right click on them and choose "save as"
scriptingtoolkit.wsf (34 Kb) : the Scripting Toolkit engine
myscript.js (1 Kb) : sample code using the Scripting Toolkit
connection.js (55 Kb) : the Ajax ToolKit
Put these files in the same folder.

Other Resources

In addition to the content of this document, there are other resources available for you as you learn to use the Scripting Toolkit or its technology :

  • Microsoft Script Center : http://www.microsoft.com/technet/scriptcenter/default.mspx
  • An overview of WSH : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
  • Sample WSH scripts : http://www.microsoft.com/technet/scriptcenter/csc/scripts/default.mspx

The Scripting Toolkit vs other languages

Here is a matrix that will let you choose the best system according to your needs.

ToolkitScripting Toolkit.NetOfficeJavaAjaxPHPPerl
LanguageVbScript, JavascriptC#, VBVBAJavaJavascriptPHPPerl
CompiledNYNYNNN
sControl skills (similar)95%50%50%60%100%40%30%
Browser basedNNNNYNN
Web Server basedNY/NNY/NNYY/N
Batch (console)YYNYNNY
PrerequisitesWindows 2k,XP, 2003 (native).Net FrameworkMS OfficeJDK 1.5IE/Firefox5.1.2 + Soap extensionSoap::Lite
DebuggerVisual Studio or script debuggerVSVBAjdbFirefox error consoleDbg, nusphere...Perl -d
IDEAny, incl. notepadVisual StudioIntegrated VBA editorEclipseSalesforce !Any
ObfuscatorscrenccompiledPassword protectedcompiledNoNu-coderAlready obfuscated ;)
Time execution limitationNoneNone (if not IIS)NoneNoneYes (browser)Yes (web server)None
VariousThe only batch system ready to run (no download, no compile, just save/run)Framework version dependantRestricted to part of the API (COM object, not direct access to the API)Need to compile to testNeed to load the toolkit on each run (takes time) + time to save the code on the serverCannot be used for batchSkill required (difficult syntax). No update since 2004

Sample script Using the Scripting Toolkit

You can cut and paste the following sample code into the myscript.js file. This script queries data and displays it on the screen. For more information about using the API, see Salesforce's API documentation.

function Results(queryResult) {
	if (queryResult.size > 0) {
		var output = "";
		var records = queryResult.getArray("records");
		for (var i=0;i<records.length;i++) {
			var account = records[i];
			output += account.Id + " " + account.Name + "\r\n";
		}
		WScript.echo(output);
	}else WScript.echo("No records matched.");
	ToolKit.Quit();
}
var callback = {onSuccess : Results, onFailure : function(e){WScript.echo(e)}};
var queryResult = sforce.connection.query("Select Id,Name from Account", callback);

What are the differences between Ajax Toolkit and Scripting Toolkit ?

Using Ajax Toolkit, you have an initialization sequence. With the scripting Toolkit you don't.
Because of using Asynchroneous calls (callback functions) you have to tell the engine to stop when your code has been finished to run : ToolKit.Quit();
To print a text in the console (stdout), use WScript.echo(yourmessage); where yourmessage is a string.

Running your script

The Scripting Toolkit can be run using the command-line.
Go to Start/Execute, enter "cmd" and press enter. You see a console with a prompt.
Go to the folder hosting the Scripting Toolkit and your custom script (myscript.js)
Enter the following command line :

cscript //nologo scriptingtoolkit.wsf /?
cscript is Microsoft Windows Scripting Host executable, which can run any *.js, *.wsf or *.vbs files. We ask cscript to run our file scriptingtoolkit.wsf. //nologo means that we don't want cscript to display any banner. /? Is asking the Scripting Toolkit to print the usage and options available through the command line.
You get the folloing output :
This script encapsulates Salesforce's AJAX Toolkit to provide a batch access to Salesforce
Usage : scriptingtoolkit.wsf [/user:value] [/pass:value] [/sid:value] [/server:value] [/proxyserver:value] [/proxyuser:value]
				 [/proxypass:value] [/debug] [/sandbox] [/prerel] [/client_id:value] [/client_secret:value]

Options :

user        : Authorized user for the API.
pass        : User's password.
sid         : session id, if no user/pass provided
server      : the server of your org (ex:na3.salesforce.com or na3-api.salesforce.com)
proxyserver : hostname or IP of your proxy, and port (ex:proxy.internal.yourcompany.com:3128 or 192.168.0.7:3138)
proxyuser   : username if using a proxy with authentication
proxypass   : password if using a proxy with authentication
debug       : To dump on stderr some debug info
sandbox     : To authenticate on test.salesforce.com
prerel        : To authenticate on prerelna1.pre.salesforce.com
client_id     : Consumer Key
client_secret : Consumer Secret
Examples :      cscript //nologo scriptingtoolkit.wsf /user:myself@mycompany.com /pass:secret
                cscript //nologo scriptingtoolkit.wsf /debug /server:emea.salesforce.com /sid:Xabu.ZcS.....9AsNCSeX5jsUoLXQ=
                if no user/password and no server/sid, script tries to get an existing sid from an IE browsing session
                using a proxy : cscript //nologo scriptingtoolkit.wsf /proxyserver:192.168.0.7:3128 /proxyuser:jla /proxypass:jla
                        Proxy error ? See http://support.microsoft.com/kb/289481/en-us
                Compatibility test : cscript //nologo scriptingtoolkit.wsf //Job:diagnose
                On Error, text is dumped to stdErr
Add the above parameters according to your network, and you will be able to run your script.

If you want to check your Windows configuration and identify any issue, run this command line :

cscript //nologo scriptingtoolkit.wsf //Job:diagnose

Note
noteYou can redirect the output of your batch to a file using this command line :
cscript //nologo scriptingtoolkit.wsf /user:xxxx@yyy.com /pass:toto >myfile.txt
You can still use the /debug parameter as its output is redirected to stderr, not stdout

Debugging your scripts

You can use Visual Studio or Microsoft Script Debugger. You will be able to add break points and watch the value of objects.

Encoding your scripts

If you don't want somebody to see your code, you can encode it using Microsoft Script Encoder.
People will not be able to modify your code. Take care as this encoding is bijective : some tools can retrieve your source code.

oAuth

The Scripting Toolkit is oAuth enabled by default, this means that when login to your Org, it will use your credentials + ids from the predefined app named "Scripting Toolkit".
Each time you run the toolkit, you can track in the user login history the value "Scripting Toolkit" in the column named "Application".
You can remove this behaviour by emptying (not removing) 2 ressources at the top of the scripting toolkit code like this: <resource id="client_id"></resource><resource id="client_secret"></resource>
To customize the identifier in the login history, change the existing client_id and client_secret values with the "Consumer Key" and "Consumer Secret" values of a new Connected App that you will have to create in your org.


The ToolKit object

By using the Scripting Toolkit, you have access to an automatically instanciated object : ToolKit.
This object has public properties and methods.

These are available properties :

ValueDescription
className"ScriptingToolKit"
UserThe user retrieved from the command line parameter /user:
PasswordThe password retrieved from the command line parameter /pass:
ServerThe server address of the proxy, retrieved from the command line parameter /server:
Proxy with basic authentication or NTLM are supported, incl. BlueCoat, ISA Server etc.
SIDThe command line parameter /sid:
HasToExitFor internal use, always false. If the Quit() method has been called, set to true.
ErrCode0 if no error. Can have another value if there was an error in a method call.

These are available methods :

ValueDescription
ClearError() Purpose : Clear error information, setting to 0 the error code.
input : none
output : none
SetError(errorcode, errortext) Purpose : Set the error code and error text.
input : errorcode : integer, errortext : string
output : none
ErrText() Purpose : Get the error text, based on the current error code, used to dump error info
input : none
output : a string containing the generic error text based on the error code and custom contextual error text
VerifyLoginParameters() Purpose : internal, undocumented
input : none
output : none
Debug(text) Purpose : Display debugging information
input : a string
output : none
The text is printed to stderr if the /debug parameter has been used in the command line
Login() Purpose : internal, undocumented
input : none
output : none
Quit() Purpose : Used to stop the script
input : none
output : none
As callback functions can be used, the only way to know that the script has finished is to explicitely call this method.
If you forget to call this method at the end of your job, the script will never end (press Ctrl+C to stop it)
SOQL2CSV(SOQL,Header,Sep,cr,strnull) Purpose : export a SOQL query to a CSV file, can manage parent relationship data
input : SOQL : a SOQL query returning a result set
input : Header : optional : an array of columns for the first line of the CSV file. The name should match the fields in the SOQL. The order of the array define the order of the fields in the CSV file.
input : Sep : optional : a separator, default to ","
input : cr : optional : the cariage return (end of line) used : \r, \n, \r\n ... default value is \r\n
input : strnull : optional : a string value to use for any field value being null. default value is "" (including the double quotes)
output : null if any error, or a string in the CSV format
CSV2Array(path,file) Purpose : read a csv file into an array in memory
input : path : folder where the file can be found
input : file : the csv file without the path
output : an 2-dimension array, the first is the record, the second the field. Fields can be referenced by their name, taken from the column name
If you encounter an error, ensure the following registry key is valid :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format
This could be either Delimited(,) or Delimited(;)
InsertFromArray(sObject,theArray,size) Purpose : insert data into Salesforce from an array in memory
input : sObject : the name of the Salesforce object
input : theArray, an array the same than CSV2Array() result.
input : size : optional : the maximum amount of records per Ajaxcall
output : true on success, false on error
Prerequisite : array index name should match Salesforce field names
DeleteFromSOQL(SOQL,size) Purpose : Delete Salesforce data based on a SOQL Query
input : a SOQL query selecting Id(s)
input : size : optional : the maximum of amount records per Ajaxcall
output : true on success, false on error
sObject2OracleCreateTable(sObjectName) Purpose : Build an SQL query based on the structure of the sObject, to create an Oracle table
input : sObjectName : the name of the sObject (string)
output : null if any error, or a string containing the SQL
StringtoFile(str,filename) Purpose : write to a file the content of a string
input : str : the content of the file
input : filename : the file name to create or overwrite
output : none
StringtoFileUTF8(str,filename) Purpose : write to an UTF8 file the content of a string
input : str : the content of the file
input : filename : the file name to create or overwrite
output : none
FileToString(filename,unicode) Purpose : read the content of a file. The file should be less than 1 Mb, to keep good performance.
input : filename : the name of the file to read
input : unicode : true if the file should be read as a unicode file
output : the content of the file
StartTimer() Purpose : Start the timer to record the duration of an operation
input : none
output : none
StopTimer() Purpose : Stop the timer to record the duration of an operation
input : none
output : none
TimeDiff() Purpose : Get the duration that has been benchmarked
input : none
output : amount of milliseconds
SendMail(from,to,subject,body) Purpose : Send an email using CDO. See the diagnose job to dump the configuration
input : from : sender of the email
input : to : recipient of the email
input : subject : title of the email
input : body : content of the email, that can be text or html (format autodetected)
output : true if success, false on error
GetFile(SOQL,filename) Purpose : Retrieve a Salesforce remote document or attachment and save it locally
input : SOQL : a query on the Body field, returning only one id. Ex : "Select Body from Document where Id='015300000000CJuAAM'"
input : filename : the file name to create or overwrite
output : true if success, null on error

Sample scripts for the Scripting Toolkit

Save these samples into myscript.js and run them using the command line.

Hello World

WScript.echo("Hello World !");
ToolKit.Quit();

The first line uses the method to print a text to the console. The second line tells the Scripting Toolkit to end.
No access to salesforce data, but as it is using the Scripting Toolkit, an implicit login occurs to the application.

Retrieving data synchroneously

try{
	var queryResult = sforce.connection.query("Select Id,Name from Account");
	if (queryResult.size > 0) {
		var output = "";
		var records = queryResult.getArray("records");
		for (var i=0;i<records.length;i++) {
			var account = records[i];
			output += account.Id + " " + account.Name + "\r\n";
		}
		WScript.echo(output);
	}else WScript.echo("No records matched.");
}catch(e){
	WScript.echo(e);
}
ToolKit.Quit();

To retrieve data asynchroneously, see the sample in the previous chapter.

Benchmark your scripts

ToolKit.StartTimer();
var queryResult = sforce.connection.query("Select Id,name,Industry from Account");
ToolKit.StopTimer();
WScript.echo(ToolKit.TimeDiff()+" milliseconds");
ToolKit.Quit();

Timer functions are provided to identify how long it takes to run a few lines of code.

Extracting data to a CSV file

//var strTest=ToolKit.SOQL2CSV("Select type,Id,name,Industry from Account",["Name","Id","Type","Industry"],";");
var strTest=ToolKit.SOQL2CSV("Select Name,Owner.FirstName,Owner.LastName,Owner.Id,Account.Name from Opportunity");
WScript.echo(strTest);
if(strTest)ToolKit.StringtoFile(strTest,"test.csv");
ToolKit.Quit();

Two steps :

  • Get the result set into a string in a CSV format
  • Save the string to a file
If the string is null, there was an error, see ToolKit.ErrText();

Reading a file

WScript.echo(ToolKit.FileToString("test.csv",true));
ToolKit.Quit();

The file can be Ascii or Unicode (second parameter=true for unicode)

Identifying available objects

var describeGlobalResult=sforce.connection.describeGlobal();
var types=describeGlobalResult.sobjects;
for (i=0;i<types.length;i++) {
	WScript.echo(types[i].name);
}
ToolKit.Quit();

You can retrieve the list of objects which you can use in your SOQL queries.

Describing an object

var describeResult=sforce.connection.describeSObject("Attachment");
var props=["activateable","createable","deletable","custom","keyPrefix","label","labelPlural","layoutable","mergeable","name"
 	,"queryable","replicateable","retrieveable","searchable","undeletable","updateable","urlDetail","urlEdit","urlNew"];
for(x=0;x<props.length;x++){
	WScript.echo(props[x]+"="+describeResult[props[x]]);
}
for(recordNumber in describeResult.fields){
	WScript.echo("Field "+recordNumber+":"+describeResult.fields[recordNumber].name+" ("+describeResult.fields[recordNumber].label+")");
	for(columns in describeResult.fields[recordNumber]){
		//if(typeof(describeResult.fields[recordNumber][columns])!='function')
			//WScript.echo(columns+"="+describeResult.fields[recordNumber][columns]);
	}
}
ToolKit.Quit();

This script lists available fields and their attributes for one sObject.

Generate an Oracle "create table" SQL query based on the sObject

SQL=ToolKit.sObject2OracleCreateTable("Account");
if(SQL){
	WScript.echo(SQL);
}else{
	WScript.echo(ToolKit.ErrText());
}
ToolKit.Quit();

This is a way to manage more records than a result set restricted to the batchsize.

Too many records

for(x=0;x<queryResult.size;x++){
	//your code here to use queryResult.records[x]);
	//...
	if(queryResult.done=='false')queryResult=sforce.connection.queryMore(queryResult.queryLocator);
}

This is a way to manage more records than a result set restricted to the batchsize.

Retrieving a document or an attachment

if(ToolKit.GetFile("Select Body from Document where Id='015300000000CJuAAM'","test.doc")){
	WScript.echo("success!")
}else{
	WScript.echo(ToolKit.ErrText());
}

ToolKit.Quit();

This will write the remote file locally.
The field "Body" should be in your SOQL. Only one file is retrieved per query : you can filter on the Id

Internal Scheduler

var frequency=10*1000;
function myScheduler(){
	WScript.echo(Date()+" scheduled.");
	//Run your scheduled code here
	window.setTimeout( "myScheduler()", frequency);
}
window.setTimeout( "myScheduler()", frequency );

You don't need a scheduler to run a script every X minutes or hours, even every minute.
The frequency is in milliseconds :

  • Every 10 seconds => 10*1000
  • Every hour => 60*60*1000
Use it the same way as if it were an sControl in the browser.

Sending an email

ToolKit.SendMail("Jean-Luc Antoine <antoinejl@xxartabus.com>"
	,"Jean-Luc Antoine <xxantoinexx@xxsalesforce.com>"
	,"Just a test"
	,"This is a text body\nwhich has two lines.");
ToolKit.SendMail("Jean-Luc Antoine <antoinejl@xxartabus.com>"
	,"Jean-Luc Antoine <xxantoinexx@xxsalesforce.com>"
	,"My second email"
	,"<html><body><h2>Cool !</h2>Very nice HTML</body></html>");

You can send an email with only 1 line of code (arguments : from, to, subject, body). The body can be either text or html (autodetected). The technical parameters are defined by the CDO configuration : you can dump them using the diagnose mode (see above in this page).

Deleting data based on a SOQL

if(ToolKit.DeleteFromSOQL("Select Id from Account where Name like 'ZZZyourprefix%'")){
	WScript.echo("Succefully deleted");
}else WScript.echo(ToolKit.ErrText());
ToolKit.Quit();

A quick way to drop some records with conditional filters.

Calling an Apex WebService

result = sforce.apex.execute('MyWebServiceClass' ,'MyMethod',{theParam:"Something", secondParam:"Something else"});
WScript.echo(result);
ToolKit.Quit();

Call an Apex Web Service the same way you would do it from within an SControl. executeAnonymous() is available too.

The WebService being called would be as an example :

global class MyWebServiceClass {
	webService static String MyMethod(String theParam, String secondParam) {
		return 'Hello '+theParam;
	}
}

Insert a CSV file into Salesforce

var CSVResult=ToolKit.CSV2Array("c:\\temp\\","account.csv");
if(ToolKit.InsertFromArray("Account",CSVResult,200)){
	WScript.echo("Succefully inserted");
}else WScript.echo(ToolKit.ErrText());
ToolKit.Quit();

This example uses a file with this content :

NAME,INDUSTRY
My new account 1,Manufacturing
My new account 2,Services
My new account 3,Manufacturing
My new account 4,Services


Note
noteThis Scripting Toolkit has been developed by Jean-Luc Antoine : antoinejl at artabus.com.
Various trademarks held by their respective owners.


 
© 2017 Jean-Luc Antoine, All Rights Reserved