Archive | Salesforce

Using PowerShell to extract all contacts from MS CRM 2011

We are moving to Salesforce from MSCRM 2011. We need to get our data out so we can import into Salesforce. Here is the PowerShell script I am using to export contacts to csv.

$url="http://crm.sardverb.com/Company/xrmservices/2011/OrganizationData.svc/ContactSet?`$filter=StatusCode/Value eq 1"

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$count=0
$output = @()

while ($url){
    function GetData ($url) {
    $webclient = new-object System.Net.WebClient
    $webclient.UseDefaultCredentials = $true
    $webclient.Headers.Add("Accept", "application/json")
    $webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
    $data=$webclient.DownloadString($url)
    return $data
    }
    $data=GetData($url) | ConvertFrom-Json
    $output += $data
    $count=$count+$data.d.results.length
    write-host $count
    if ($data.d.__next){
        #$url=$null
        $url=$data.d.__next.ToString()
    }
    else {
        $url=$null
    }
}

$output.d.results | Select -ExcludeProperty ParentCustomerId,__metadata @{l="ParentCustomerID";e={$_.ParentCustomerID.Id}},* | Export-Csv -NoTypeInformation C:\Contact.csv

Hope that helps someone.

0

Using jsforce and node.js to connect to Salesforce

I wanted to write a node.js app to pull data from Salesforce. I found the NPM library jsforce. I added it to my packages in my package.json:

  "dependencies": {
    "express": "*",
    "dotenv": "*",
    "jsforce": "*"
  }

I also added “dotenv” which I am using to load my client secret and all configuration data from a hidden .env file. This is not in my git repo, so I can have different values in production and development.

Here is what I have in my .env file:

CLIENTID=zWHRIM8F87FChMcfHpZKS9LhQeeLwfthDbaiL9iXNO7ZBwfUwFPFqpDzC2HruNkJfIxrOdeITtftxBg20WEIm
CLIENTSECRET=123456789987654
REDIRECTURI=localhost
USERNAME=username@yourdomain.com
PASSWORD=PASSWORDANDCODE
LOGINURL=https://sitename-dev-ed.my.salesforce.com

Here is the code to pull in the .env values, define the oauth2 connection and login.

var dotenv         = require('dotenv').load();
var conn = new jsforce.Connection({
  oauth2 : {
      loginUrl : process.env.LOGINURL,
      clientId : process.env.CLIENTID,
      clientSecret : process.env.CLIENTSECRET,
      redirectUri : process.env.REDIRECTURI
    }
});
var username = process.env.USERNAME;
var password = process.env.PASSWORD;
conn.login(username, password, function(err, userInfo) {
  if (err) { return console.error(err); }
  console.log(conn.accessToken);
  console.log(conn.instanceUrl);
  console.log("User ID: " + userInfo.id);
  console.log("Org ID: " + userInfo.organizationId);
});

Once connected and logged in, we can query using SOQL. This is a query to pull All Opportunities, their contacts and contact roles, and their team members and the team member roles. If that makes sense. I am using this query to show the relationships between Opportunities and their Contacts and team members using d3.js. More on that later.

    var query = "SELECT Id, Name,(SELECT Contact.Name,Contact.Email,Contact.Id,Contact.AccountId,ContactId,Role,Contact.Account.Name FROM OpportunityContactRoles),(SELECT User.Name,User.Email,User.Id,UserId,TeamMemberRole FROM OpportunityTeamMembers) FROM Opportunity"
    conn.query(query, function(err, results) {
      if (err) { return console.error(err); }
      console.log("Query: " + results.totalSize)
      console.log(JSON.stringify(results, null, 2))
    });
0

Connecting to the Salesforce REST api using PowerShell

As I said in my previous post, we are starting to use Salesforce, and I like REST APIs, so I wanted to see how to connect to Salesforce with cuRL and PowerShell.

cURL was pretty easy, PowerShell was not so much. The biggest issue was that when I queried the standard “https://login.salesforce.com/services/oauth2/token” url, I would get one response back, but if I tried again, it wouldn’t work. I had to install fiddler to figure out what was going on. I finally found the error and this solution: use your instance ID in the URL. That took me half a day to figure out. Add-on a typo of not having https in the URL, and I was not having fun. Once I figured out that you need to use your instance url and https I hit this error:

salesforce stronger security is required

So I had to figure out how to force Invoke-WebRequest or Invoke-RestMethod to use TLS 1.2. Here is the code that I finnanly figred out that gets an access token and queries accounts.


[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$tokenurl = "https://InstanceName-dev-ed.my.salesforce.com/services/oauth2/token"
$postParams = [ordered]@{
grant_type="password";
client_id="ReallyLongClientIDReallyLongClientIDReallyLongClientIDReallyLongClientIDReallyLongCli";
client_secret="1234567890123456789";
username="bob@aol.com";
password="PasswordAndTokenNoSpaces";
}

$access_token=(Invoke-RestMethod -Uri $tokenurl -Method POST -Body $postParams).access_token

$url = "https://InstanceName-dev-ed.my.salesforce.com/services/data/v37.0/sobjects/Account"
Invoke-RestMethod -Uri $url -Headers @{Authorization = "Bearer " + $access_token}


you don’t need the [ordered] part of the hash table, i was just using it to troubleshoot.

3

Connecting to the Salesforce REST api using cURL

My company decided to use Salesforce. I have worked with Microsoft CRM, but not Salesforce yet. When learning about a new application, I like to see how I can access the data. PowerShell and cURL are the simplest way for me to understand how to connect to a REST api.

First step is getting an OAuth2 token. This is well documented, but being new to the platform, I needed to start from the beginning.

First you need to create an app. Copy the ID and the Secret (I am using lightning):

Setup Home -> Platform Tools -> Expand Apps -> Apps -> Connected Apps -> New. You can follow these directions

After it is created, you need to create a token for the user:

Top menu on upper right corner click on your “icon” -> Settings -> My Personal Information – Reset My Security Token.
Click reset and it will be emailed to you.

Here is the cURL command to make the connection and get the access_token:

response=$(curl -s https://InstanceName-dev-ed.my.salesforce.com/services/oauth2/token -d "grant_type=password" \
-d "client_id=ReallyLongClientIDReallyLongClientIDReallyLongClientIDReallyLongClientIDReallyLongCli" \
-d "client_secret=1234567890123456789" -d "username=bob@aol.com" \
-d "password=PasswordAndTokenNoSpaces")
ACCESS_TOKEN=$(echo $response | awk -F"," '{print $1}' | awk -F":" '{print $2}' | sed s/\"//g | tr -d ' ')

Things to note: I am using my instance ID in the url. There was mixed documentation as to use this address or the standard logon url. This worked, and with the PowerShell command in the next post it was necessary. Https is required, and the password is a mashup of user name and security token

And the code to pull some data using the token

curl -H "Authorization: Bearer $ACCESS_TOKEN" -H "X-PrettyPrint:1" "https://InstanceName-dev-ed.my.salesforce.com/services/data/v37.0/sobjects/Account"

This was pretty easy as there are many examples out there. PowerShell, not so much.

0

Powered by WordPress. Designed by WooThemes