Tag Archives | 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
[email protected]
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 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 "[email protected]" \
-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