Sometimes, after adding several fields to Salesforce’s “Contact” object, I find myself needing to add equivalent fields to Pardot’s “Prospect” table. I could do this by hand, but whenever I get to 5 or more similar fields, I start to think that code might be faster. Let’s take a look at how I’ve used Python to add fields to Pardot.
This is an intermediate-level “Python for Salesforce” post – but still “admineloper”-level (e.g. you can code a basic trigger comfortably).
Our Program’s Recipe
Let’s talk about the general flow of our program – the “algorithm” or “recipe” that we’re going to code it to follow.
I’m going to refer to “API endpoints” as doors of castles – click here for a full explanation of that metaphor.
We’ll need to write code that does the following:
- Knocks on the door of the “Pardot login castle.”
- Slips the guard a Pardot username + password + “user key” through the slot in the door.
- Carefully saves the “API key” that the login castle’s guard slips back through the slot in the door.
- Knocks on the door of the “Pardot custom fields info castle.”
- Slips the guard its “user key” and the “API key” it received from the “login castle” through the slot in the door, along with some instructions.
- The instructions for the guard say: “I’d like to know what custom fields exist in this Pardot org – please keep the response concise and format it in JSON.”
- Carefully saves the data dump that the custom fields info castle’s guard slips back through the slot in the door.
- Looks through the “custom fields” info, setting aside a list of any fields “of interest” that already exist in Pardot.
- Pulls out a list of the “custom fields” it was planning to add to Pardot and does the following steps for each of them, one at a time:
- Makes sure it’s not in the list of “custom fields” that already exist in Pardot. If so, skips the rest of the steps and moves on to the next “field to add.”
- Knocks on the door of the “Pardot custom fields creation castle.”
- Slips the guard its “user key” and the “API key” it received from the “login castle” through the slot in the door, along with some instructions.
- The instructions for the guard say: “I’d like to create a new field called
(some good Pardot field 'API name')
on the back-end of Pardot, and called(some good Pardot field 'label')
in the front-end of Pardot. It’s a plain-text field. It should sync to the Salesforce field whose ‘API name’ is(some Salesforce field's API name)
. This new field should only be able to hold one value at a time. This field doesn’t use any sort of predefined values. - Displays to its “human” (you and me!) what response the castle’s guard slipped back through the slot in the door (hopefully it’s “Gotcha; done”) as well as the name of the field it was requesting the creation of when it received this response.
Password Precautions
Before we launch into what the code is, let’s take a moment to remind ourselves of some important principles about writing code that involves passwords.
To get this code to work, you have to type your Pardot password and “user key” into your code. That’s not a good thing.
- Only run this code on a computer you trust.
- Only run this code over a network connection you trust.
- If you save this Python script in a file, type “CONTAINS PASSWORD” at the beginning of the filename so you’ll remember it’s a file in which you have to clear out passwords before you close it.
- If you’re working in an IDE that treats subsequent “run” button clicks like one big session (and caches the values of variables from “run” to “run”), leverage that to help you remember to clear out your password from your file. Run a few lines of code, with the rest commented out, to fetch an “API key” and save it into a variable called
api_key
.
Then comment out the “fetching” code, backspace over your password, and type the word “PASSWORDGOESHERE
,” and save your script.
Do the same with your e-mail address and user key.
Ta da! Now you can stop worrying about whether you left a password in plain text in the contents of your script. - You’re going to have to get expert advice if you need to store this script and run it in any sort of automated fashion. Typing your password into the body of the script itself and leaving it there is simply not an option.
Code Explanation
Modules needed
First things first … let’s add some code to our program to “import” some modules (extensions to the commands available for the Python programming language) that will make our job easier.
import urllib
import requests
import json
Logging Python into Pardot
This code will knock on the door of the Pardot “login” castle, slip the guard our credentials, and store the guard’s response into a Python “variable” called api_key
.
- According to Wikipedia, Doune Castle, from the “French Taunter” scene in the film “Monty Python and the Holy Grail,” is located at 56°11′07″N 4°03′01″W.
- They’d probably throw a cow at us.
- According to Pardot’s official documentation, the “login” castle is located at
https://pi.pardot.com/api/login/version/3
.- This looks like a better door to knock on!
ukey = 'PUTUSERKEYHERE'
api_key = requests.post(
'https://pi.pardot.com/api/login/version/3'
, params='email='+urllib.parse.quote('PUTEMAILADDRESSHERE')
+'&password='+'PUTPASSWORDHERE'
+'&user_key='+ukey
+'&format=json'
).json().get('api_key')
#print(api_key)
This api_key = ...
is the code I was saying you should leave as “'PUTEMAILADDRESSHERE
,” “'PUTPASSWORDHERE
,” & “'PUTUSERKEYHERE
,” and comment out (put a #
at the beginning of it) when not in use, so that you don’t accidentally leave your credentials in your code.
You can un-comment print(api_key)
for a moment and run that line of code if you want to verify that logging in went well.
Appropriate users
Note that if you normally use Salesforce credentials to log into Pardot, you’ll have to add a new Pardot user with a username that doesn’t exist in your Salesforce org and use that user’s credentials to log into Pardot. “Salesforce-connected” Pardot usernames can’t log into Pardot with code.
Click here for instructions on setting up such a user in Pardot.
Ask Pardot what custom fields already exist
This code will knock on the door of the Pardot “custom fields info” castle, identify itself to the guard, ask for a list of Pardot custom fields, and store the guard’s response into a Python “variable” called customfieldsresponse
.
- According to Pardot’s official documentation, the “custom field manipulation” castle (API) is located at
https://pi.pardot.com/api/customField/version/3/do/query
.
The guard posted at the castle door will want to know:
- Our user key (
'user_key='+ukey
) - Our api key received from the first castle (
'api_key='+api_key
) - Optional: That we want our data back as JSON (
'format=json'
) - Optional: That we don’t need a huge amount of the data (
'output=mobile'
)
To travel to the castle and knock on the door, we’ll use Python’s “requests” plugin’s requests.post()
command.
customfieldqueryurl = 'https://pi.pardot.com/api/customField/version/3/do/query'
customfieldsdata = 'user_key='+ukey+'&api_key='+api_key+'&format=json'+'&output=mobile'
customfieldsresponse = requests.post(customfieldqueryurl, params=customfieldsdata)
Set aside important “custom fields” info
The guard gave us back way more information than we actually need, so now we have to filter through it and set aside the information we care about.
customfields = customfieldsresponse.json().get('result').get('customField')
The guard’s response was a special type of data, specific to our Python “module” called “requests.” The person who wrote the “requests” module was a big believer in simplicity and decided to name this special data type a “Response
.”
If you’re a Salesforce admineloper who’s played with writing Apex, you might have noticed that certain “data types” within Apex have special commands that can come after them.
For example, if you have a variable containing a “String” (plain text) in Salesforce Apex called “myVariable
,” this code would overwrite its contents with an upper-cased version of those contents:
myVariable = myVariable.toUpperCase();
- Q: Why do all “Strings” have a
.toUpperCase()
command in Salesforce Apex? - A: Because Salesforce wrote Apex that way.
Same principle here: the people who wrote our “requests” module decided that any data structured inside its “Response” data type should have certain special commands available.
One of those special commands is .text
. Its output is plaintext (a Python “string”) that, because we asked the guard to send us the repsonse formatted according to the JSON standard, will be formatted according to the JSON standard.
If we ran this Python code:
print(customfieldsresponse.text)
The text displayed on our screen would look something like this (note that I manually added line breaks and indents for readability):
{
"@attributes":{
"stat":"ok",
"version":1
},
"result":{
"total_results":88,
"customField":
[
{
'id': 1010,
'name': 'Pardot Field 1',
'field_id': 'Pardot_Field_1',
'type': 'Dropdown',
'type_id': 4,
'crm_id': 'Custom_Field_1__c',
'is_record_multiple_responses': False,
'is_use_values': True,
'created_at': '2015-01-12 13:02:21',
'updated_at': '2017-02-15 07:25:30'
},
{
'id': 2020,
'name': 'Pardot Field 2',
'field_id': 'Pardot_Field_2',
'type': 'Text',
'type_id': 1,
'crm_id': 'Custom_Field_2__c',
'is_record_multiple_responses': False,
'is_use_values': False,
'created_at': '2015-01-12 13:23:02',
'updated_at': '2017-02-15 07:34:00'
},
...
{
'id': 8888,
'name': 'Pardot Field 88',
'field_id': 'Pardot_Field_88',
'type': 'Dropdown',
'type_id': 4,
'crm_id': 'Custom_Field_88__c',
'is_record_multiple_responses': False,
'is_use_values': True,
'created_at': '2015-01-12 13:23:59',
'updated_at': '2017-02-15 07:21:28'
}
]
}
}
There’s not much we can do with JSON-formatted text until we transform it into something besides text.
In the series introducing JSON, I compared the “objects” and “lists” its punctuation indicates to octopi with tattoos on their arms.
Right now, the plaintext we displayed is … sort of … a painting of octopi.
Have you ever been to one of those group paint-party classes where people drink wine and imitate a painting on the wall, guided by a teacher?
The JSON-formatted text that the guard gave us is the finished painting hanging on the wall.
Python, though, needs to make its own copy of this acquatic scene so that it can more efficiently pay attention to or change details of what the octopi in the painting are holding.
This involves converting the plaintext to native Python lists and dictionaries (which are like Lists and Maps in Salesforce Apex), against which Python is able to quickly perform computations.
Normally this would be done by doing the following command, where “loads” is short for “load string”:
myPythonVariable = json.loads(customfieldsresponse.text)
But “Response” objects in the “requests” module of Python have a handy command that’s a lot shorter to type and does the same thing:
myPythonVariable = customfieldsresponse.json()
In my opinion, it’s a bit confusing that the .json()
command reads JSON rather than producing JSON, but this isn’t our choice to make. We just have to live with it.
Looking at the JSON-formatted text above, you might notice that the data we actually care about is nested “a few octopi deep.”
We can ask the outermost octopus produced by our .json()
command to give us the octopus it’s holding in its “result
” tentacle, and then ask that octopus to give us the octopus it’s holding in its “customField
” tentacle, and make a shortcut to our “3rd-level octopus” called “customfields
,” with this code:
customfields = customfieldsresponse.json().get('result').get('customField')
Now customfields
contains just the list of fields itself:
[{'id': 1010...}, ... {'id': 8888...}]
Furthermore, for each field in the list, we really only care about 1 of the 9 details available about it: its “field_id
” (e.g. ‘Pardot_Field_1’).
customfields
is a “list octopus” with numbered tentacles holding 88 different octopi that have 9 tentacles apiece.
Let’s make a new 88-tentacled octopus called fieldIdsAlreadyInPardot
where each tentacle is sipmly holding a word like 'Pardot_Field_1'
, rather than holding an entire octopus.
We can do that with a special kind of “for loop” known as a “list comprehension” that’s all the rage to do if you’re a Python programmer:
fieldIdsAlreadyInPardot = [x.get('field_id') for x in customfields]
The contents of fieldIdsAlreadyInPardot
, if we printed them out as JSON-formatted text, would look something like this:
['Pardot_Field_1', 'Pardot_Field_2', ... 'Pardot_Field_88']
fieldIdsAlreadyInPardot
will come in handy later when we want to cross-check our “fields to add” against it to avoid adding a field twice.
In fact, let’s do create one more “smaller 88-tentacled octopus:”
salesforceFieldsAlreadyInPardot = [x.get('crm_id') for x in customfields]
The contents of salesforceFieldsAlreadyInPardot
, if we printed them out as JSON-formatted text, would look something like this:
['Custom_Field_1__c', 'Custom_Field_2__c', ... 'Custom_Field_88__c']
Create new fields in Pardot
First, let’s start with a list of Salesforce fields we think we might need to add to Pardot and call it salesforceFieldsToMaybeAdd
.
salesforceFieldsToMaybeAdd = ['Custom_Field_88__c','Custom_Field_89__c','Custom_Field_90__c']
Now let’s copy it into a new list, minus any members that are also a member of salesforceFieldsAlreadyInPardot
or where a conversion to the “way we name fields in Pardot” is already in fieldIdsAlreadyInPardot
.
We’ll call the copy salesforceFieldsToActuallyAdd
.
salesforceFieldsToActuallyAdd = [x for x in salesforceFieldsToMaybeAdd if x not in salesforceFieldsAlreadyInPardot and 'Pardot_' + x.lstrip('Custom_').rstrip('__c') not in fieldIdsAlreadyInPardot]
salesforceFieldsToActuallyAdd
will have just 'Custom_Field_89__c'
and 'Custom_Field_90__c'
. It will not contain 'Custom_Field_88__c'
since it’s already in Pardot.
For every member of salesforceFieldsToActuallyAdd
, this code will knock on the door of the Pardot “create new custom fields” castle, identify itself to the guard, hand over details about a custom field to create, and store the guard’s response into a Python “variable” called customfieldcreateresponse
.
- According to Pardot’s official documentation, the “create new custom fields” castle is located at
https://pi.pardot.com/api/customField/version/3/do/create
.
salesforceFieldsToAdd = ['Custom_Field_88__c','Custom_Field_89__c','Custom_Field_90__c']
customfieldcreateurl = 'https://pi.pardot.com/api/customField/version/3/do/create'
for sfFieldBase in statusFieldBasesToAdd:
customfieldcreatedetaildata = '&field_id='+'ExecEd_'+sfFieldBase+'&name='+'EE%20-%20'+sfFieldBase+'&crm_id='+sfFieldBase+'__c'+'&type_id=1'+'&is_use_values=False'+'&is_record_multiple_responses=False'
customfieldcreatedata = 'user_key='+ukey+'&api_key='+api_key+customfieldcreatedetaildata
customfieldcreateresponse = requests.post(customfieldcreateurl, params=customfieldcreatedata)
print(sfFieldBase+':\r\n'+str(customfieldcreateresponse.content))
print()
Need to spot-check the initial Pardot data a bit more?
TO DO: Pandas looking at Salesforce field IDs and field labels, too?
(This is an example of “JSON to CSV” like we talked about in part 2 of JSON & XML)
Establish the filepath you want to download your Pardot prospects to
Here, we’ll save our output to c:\example\pardotprospectsYYYYMMDDHHMMSS.txt
(with “YY…SS” being replaced by the actual timestamp when we start our code).
pqueryresultsfilepath = 'c:\\example\\pardotprospects'+datetime.datetime.now().strftime('%Y%m%d%H%M%S')+'.txt'
Comment out that last line of code if running in chunks
If you’re planning to run your code in chunks (commenting out parts you aren’t currently running), rather than as one long script, you’ll want to put a #
before the word pqueryresultsfilepath
in the previous line of code as soon as you’ve run it once.
Why?
Well … because you’re going to use that filename twice, in separate chunks of code.
If you run those chunks separately, you don’t want to try to write to a file with “0738” in its filename at 7:38AM, then read from a file with “0741” in its filename at 7:41AM. 😆
Alternatively, you could:
- Use today’s date without a time (
'%Y%m%d'
instead of'%Y%m%d%H%M%S'
) – unless, of course, you’re working around midnight. - Leave out the timestamp from the filename altogether and simply come up with a file path that you know isn’t already on your computer (to avoid overwriting something you cared about).
LINK: http://developer.pardot.com/kb/api-version-3/custom-fields/ http://developer.pardot.com/kb/api-version-3/custom-fields/#supported-operations http://developer.pardot.com/kb/api-version-3/custom-fields/#supported-operations_1
- According to Wikipedia, Doune Castle, from the “French Taunter” scene in the film “Monty Python and the Holy Grail,” is located at 56°11′07″N 4°03′01″W.
- According to Pardot’s official documentation, the “custom field manipulation” castle (API) is located at
https://pi.pardot.com/api/customField/version/3/do/query
.- First, we’ll knock on this castle’s door and ask Pardot what custom fields exist in the “Prospect” table.
The guard posted at the castle door will want to know:
- Our user key (per http://developer.pardot.com/#using-the-api)
- Our session token (per http://developer.pardot.com/#using-the-api)
- Optional: That we want our data back as JSON
- Optional: That we don’t need a huge amount of the data (
output=mobile
)
To travel to the castle and knock on the door, we’ll use Python’s “requests” plugin’s requests.post()
command.
The response will be data that we can append a few magic bits of code to, that magic code being:
.json().get('result').get('customField')
This code will strip away a lot of the junk of the information that Pardot sends back to us after we knock on its door and leave us “just the facts, ma’am.”
Then we’ll turn those key facts into a table using Python’s “pandas” plugin and set the “row IDs” to be the field names instead of “0, 1, 2,” etc.
cfdf = pandas.DataFrame(customfields)
cfdf.set_index('field_id', inplace=True)
import requests
import pandas
import json
'''
em = 'un'+'%40'+'dm' # Note: a "+" needs to be URL-encoded to "%2B"
pw = 'pd'
ukey = 'uk'
authdata = 'email='+em+'&password='+pw+'&user_key='+ukey+'&format=json'
authenticationURL = 'https://pi.pardot.com/api/login/version/3'
api_key = requests.post(authenticationURL, params=authdata).json().get('api_key')
#print(api_key)
'''
#'''
customfieldqueryurl = 'https://pi.pardot.com/api/customField/version/3/do/query'
customfieldsdata = 'user_key='+ukey+'&api_key='+api_key+'&format=json'+'&output=mobile'
customfields = requests.post(customfieldqueryurl, params=customfieldsdata).json().get('result').get('customField')
cfdf = pandas.DataFrame(customfields)
cfdf.set_index('field_id', inplace=True)
#print(cfdf.index[cfdf.index.str.startswith('ExecEd_Status')])
#print(cfdf[cfdf.index.str.startswith('ExecEd_Status')])
#'''
'''
customfieldcreateurl = 'https://pi.pardot.com/api/customField/version/3/do/create'
statusFieldBasesToAdd = ['StatusDecision','StatusDesignThink','StatusPMoverview','StatusVisualFac','StatusZTalentDev']
for sfFieldBase in statusFieldBasesToAdd:
customfieldcreatedetaildata = '&field_id='+'ExecEd_'+sfFieldBase+'&name='+'EE%20-%20'+sfFieldBase+'&crm_id='+sfFieldBase+'__c'+'&type_id=1'+'&is_use_values=False'+'&is_record_multiple_responses=False'
customfieldcreatedata = 'user_key='+ukey+'&api_key='+api_key+customfieldcreatedetaildata
customfieldcreateresponse = requests.post(customfieldcreateurl, params=customfieldcreatedata)
print(sfFieldBase+':\r\n'+str(customfieldcreateresponse.content))
print()
'''
print(cfdf.columns)
'''
ukey = 'f36005801e7d5ab033cd6c2386b6e747'
api_key = requests.post(
'https://pi.pardot.com/api/login/version/3'
, params='email='+urllib.parse.quote('PUTEMAILADDRESSHERE')
+'&password='+'PUTPASSWORDHERE'
+'&user_key='+ukey
+'&format=json'
).json().get('api_key')
print(api_key)
'''