Querying data from Salesforce using Displayr and R
You can easily extract data from Salesforce.com using Displayr and the Salesforce.com API's. In this post, we show you how to generate a Security Token in Salesforce which is then used in Dispalyr to create an API call. The API brings the Salesforce data into Displayr which can be then be analyzed and charted as needed.
Generating a Security Token
You need to first generate a Security Token in Salesforce which will be used for authenticating your API calls. To generate the token, first login to your Salesforce.com account and then go to your account Settings page. From the left panel menu, select Reset My Security Token and then click the Reset Security Token button.
A security token will be emailed to you. Store this token somewhere safe and do not share the token publicly.
Storing Your Authentication Credentials
Before creating the API call object, we will first create objects to store our authentication credentials. From Displayr, select Insert > Analysis (Group) > R Output. Enter the following into the R CODE section, replacing yourusername with your own Salesforce.com username.
username <- 'yourusername'
Create another R Output, and enter the following:
userpw <- 'yourpassword' token <- 'yoursecuritytoken' password.token <- paste(userpw,token,sep="")
Note the password.token parameter combines your Salesforce.com account password and token into a single string which is used for authentication.
Creating the API Call
Create a new R Output and enter the following line of R code which loads the RForcecom library. This library is necessary to make the API calls to Salesforce.com.
Next enter the following lines of code.
session <- rforcecom.login(username, password.token) # Execute a SOQL soqlQuery <- "SELECT account.ID, account.Name, account.OwnerID FROM Account" accounts <- rforcecom.query(session, soqlQuery)
The session variable takes the stored values from the username and password.token variables created above and initiates a login to Salesforce.
The soqlQuery variables stores the SQL statement to be executed. Note that Salesforce utilizes a variation of standard SQL called Salesforce Object Query Language (SOQL). Reference the Salesforce SOQL Documentation library for more details on how to structure SOQL syntax.
The accounts variables executes the API by passing the session credentials and query to Salesforce.
Click the Calculate button to execute the R code. A data set containing the account ID, account name and account owner ID is returned.
The example above uses just a couple of fields from the account table object. There are several other Salesforce API database objects which can be accessed. A complete list of available objects can be found in the Salesforce Object Manager.