Tuesday, February 14, 2017

Accessing Salesforce from Powershell

I am working on a small side project at work and need to pull some account data from Salesforce to merge into a database. This has to be done with some regularity so instead of continuing to do this manually, I decided to create a Powershell script to get the data, convert it into a form that can be imported, and add that to the existing job that that runs to do other data merge tasks.

To save others some time, I am showing the basic Powershell / Salesforce script here:

# Salesforce web service access

$getUserID = Read-Host -Prompt 'Enter Salesforce user ID' # prompt for the Salesforce user
$getPassword = Read-Host -Prompt 'Enter Salesforce password' -AsSecureString # prompt for the Salesforce user password
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR( $getPassword ) # convert into form that supports decrypting
$plaintextpassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto( $BSTR ) # descrypt string so that we can sent as part of request

# issue the actual OAuth request
$url = "https://login.salesforce.com/services/oauth2/token" # OAuth URL

$params = @{ # OAuth parameters
client_id="{replace with your actual Salesforce client_id}";
client_secret="{replace with your actual Salesforce client_secret}";

# invoke OAuth call to get security token
$token = Invoke-RestMethod -Uri $url -Method Post -Body $params -ContentType "application/x-www-form-urlencoded"

#"here it is: $token"

# show that it works by getting list of available reports...
$WorkingURI = $Token.instance_url+"/services/data/v35.0/analytics/reports"
Invoke-RestMethod -URI $WorkingURI -Method GET -Headers @{ "Authorization" = "OAuth " + $Token.access_token };

# and then run a query...
$sql="select parent.name, name from account"
$WorkingURI = $Token.instance_url+"/services/data/v20.0/query/?q=$sql"
Invoke-RestMethod -URI $WorkingURI -Method GET -Headers @{ "Authorization" = "OAuth " + $Token.access_token } | ConvertTo-Json

# eof

As an added bonus, it prompts the user for their Salesforce user ID and password. This can/will have other uses. The script its self should be self-explanatory. After successful authorization, the script runs a couple of simple calls, one to retrieve a list of reports and the other to run a simple query.

It makes use of the "password" form of OAuth. You will need to configure Salesforce to allow this to work. Part of that is setting up Salesforce for REST web service access. Doing this will cause the system to generate the client_id and client_secret values that you need to pass in order to generate the token. This link will walk you through that. This script will work with a "chatter" user account as long as you set the permissions. You will need something better than a chatter account to get Salesforce configured.

Have fun...

No comments:

Post a Comment