Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Learning Guides for Salesforce Adminelopers

💬 EN

Welcome, Salesforce adminelopers!

Looking for a quick roadmap to useful coding tutorials like Apex, XML/JSON, Python, or APIs?


Coding with Apex

If you’d like to learn Apex, don’t study with me. Check out WomenCodeHeroes and more – see the Salesforce section of my Resources page

If you qualify, be sure to apply to RAD Women Code or PepUpTech for intensive lessons.

And if you have the money, of course, there are always official Salesforce intensive trainings.


XML and JSON

Although XML and JSON are text-formatting standards that are often used when talking to cloud-based services’ “APIs” over the internet, there are ways of obtaining files written according to these standards that don’t require “API programming.”

First, learn all about them in my “Intro to XML and JSON” series.

Then try an exercise putting them to work for you in Salesforce.

  1. My blog post “Setting up VSCode to edit Salesforce metadata” walks you through an exercise to alter the setup of your Salesforce org by downloading, editing, and re-uploading an “XML” file.
  2. If you’re a DemandTools customer, see “XML for Salesforce Administrators: DemandTools Configuration.”

Coding with Python

Python is a general-purpose programming language that helps you make your personal computer “do stuff” the same way that Apex makes your Salesforce org “do stuff.”

If coding in Apex is a way of automating the “clicking and typing” you’d normally be doing into your Salesforce data records, then coding in Python is a way of automating the “clicking and typing” you’d normally be doing on your own personal computer.

A few examples are:

  1. Loop through every file on your desktop and rename its filename in all-caps
    • (Not sure why you’d want to, but you can!) 😊
  2. Open a CSV file on your computer, edit it, and save the “new & improved” version as a new CSV file.
    • Editing CSV files in Python is useful as a “power-tool” companion to Excel when downloading data from Salesforce, modifying it on your computer, and re-uploading it back into Salesforce via the Data Loader.
    • Editing CSV files in Python is as easy as I wish Microsoft had made writing Excel “macros.”
  3. Download data from, and send data back to, a cloud service’s “API”
    • This is an “advanced admineloper” / “intermediate developer” skill, but it can be very useful for tasks like cleaning up bad data in Pardot.
    • I wrote a roadmap for teaching yourself API programming at the end of this page.

Everything I teach about Python can be done with other programming languages, too. I just love Python because it has a very beginner-friendly style for writing commands as code.

Python for CSV files

One day, I hope to develop a full-on Trailhead / WomenCodeHeroes-style “perfect written beginner’s tutorial” to editing CSV and Excel files with Python. Stay tuned! (Writing quality content is hard. 😊)

In the meantime, here’s what I have on offer:

  1. If you prefer Youtube-style learning, start with the videos from my “101” and “102” Salesforce Saturday hands-on workshops.
  2. If you prefer a bite-sized (30 minutes?) read-and-code-along “taster” without a lot of stops to explain concepts, start with these examples and exercises.
  3. Because “editing spreadsheets” is useful for all sorts of Excel users, not just Salesforce admins, you’ll find I don’t always market Python blog posts as “for Salesforce.” Look for blog post titles mentioning “CSV” or “Excel.”

Note that if you search the web for tutorials about the “Pandas” module (plugin) to Python that I frequently use for editing CSV files, it can be very overwhelming. It’s like needing a small utility knife and being pointed to a professional carpenter’s workshop and told, “There’s one in there somewhere.”

The number of commands from “Pandas” that are useful to a total beginner who just wants to edit a few spreadsheets are just a small subset of the total commands available in this part of Python (which is usually used by people doing advanced statistics).

For focus, I recommend starting with the tutorials and blog posts above.


APIs

Interested in learning to code against cloud-hosted services?

Learning to write code against “APIs” to solve Salesforce problems requires mastering some fundamental concepts first.

Here’s a roadmap to teach yourself by:

Step 1: Conceptually understand APIs, XML, & JSON

Start with my “Salesforce REST APIs: A High-Level Primer” post and my “Intro to XML and JSON” series.

I think it’s incredibly important to have a firm grasp of these concepts before you even think about coding.

Step 2: Conceptually understand HTTP

Programming is all about teaching a computer to do stuff on your behalf.

How can you teach a computer what to do if you don’t understand what you want it to do?

Learn what an HTTP(s) request is at a conceptual level first.

Step 3: Get hands-on with HTTP

Cement your understanding of what making an HTTP(s) request does by making a few of your own.

But don’t start doing it with “code” yet.

There’s a wonderful point-and-click tool you can download and run on your computer called Postman that makes it easy to communicate with the “API endpoints” of computers in the cloud.

  • Q: Not sure what cloud-based computer to talk to?
  • A: Try one of these! Choose one that doesn’t require “authentication” (login), because learning to manage passwords properly is a huge topic you’ll need your full attention to master some other time.

Play with using Postman to write and execute “GET” and “POST” HTTP(s) requests against a variety of “APIs.”

  1. Look at the “body” of the data that comes back (the “HTTP(s) response”). Does it contain what you expected it might?
  2. Look at the “status” code of the response. Is it 200? 400? Something else?
  3. Click on the “Headers” tab of the “response.” Is there any interesting information?
    • Running a simple “GET” request against the API endpoint located at https://yesno.wtf/api will send back a response whose headers include a note called “Content-Type” indicating that the text contents of the response’s “body” are structured according to the “JSON” standard and using letters available in the “UTF-8” alphabet.)
  4. Change something and send another “request” to the same “API.” Does the response look like you expected?
  5. Get a feel for the various options that certain APIs expect you to send with your “requests.” See the next 3 steps for examples.
  6. Some API endpoints want you to change the “URI” (address) of the “endpoint” itself by adding “parameters” to the end of it.
    • A “GET” request against http://jsonplaceholder.typicode.com/posts will give you information about lots of fake blog posts.
    • A “GET” request against http://jsonplaceholder.typicode.com/posts/1 will give you the same information about just the first one.
    • A “GET” request against http://www.recipepuppy.com/api/?i=onions,garlic&q=omelet&p=3 will give you a recipe with its text structured according to the “JSON” standard.
    • A “GET” request against http://www.recipepuppy.com/api/?i=onions,garlic&q=omelet&p=3&format=xml will give you the same recipe, but with its text structured according to the “XML” standard.
  7. Some API endpoints refuse to send you a useful response, or change what response they send you, unless you put special data into the “body” of a request.
    • A “POST” request against https://reqres.in/api/register with nothing in the “body” will return an HTTP(s) response with a “Status” code of 400 (“bad request”) and a body whose contents say {"error":"Missing email or username"}.
    • A “POST” request against https://reqres.in/api/register where you set the request’s “body” type to the “raw” radio button, change the “Text” drop-down just to the right of that radio button to “JSON (application/json)”, and fill in the contents of the request “body” with {"email": "[email protected]", "password": "pistol"}, will return an HTTP(s) response with a “Status” code of 200 (“OK”) and a body whose contents say {"id": 4, "token": "QpwL5tke4Pnpja7X4"}, which means it worked.
    • Note: only certain username and password combinations work against this particular API. Once you get it working, try changing the e-mail address and see if you can get it to go back to failing with a response body of {"error": "Note: Only defined users succeed registration"}. So now you can see that sometimes it’s not just a question of figuring out how to send data in the “body” of an HTTP request, but also about figuring out what data the API wants to see.
  8. Some API endpoints refuse to send you a useful response, or change what response they send you, unless you put special data into the “header” of your request.
    • In our previous example, changing the “Text” drop-down to “JSON (application/json)” while setting up the “body” of our request actually made Postman add a header to our request.
    • Click the “Headers” tab of the request you got working against https://reqres.in/api/register. Un-check the checkbox next to the row in the table that indicates a “Key” of “Content-Type” and a “Value” of “application-json” and send your HTTP request again. It should fail with a response body of {"error":"Missing email or username"}. Now check the box so that the header parameter that Postman added for us does go through with the request and re-send the request. It should succeed.
    • The two most common reasons for adding data to the “headers” of an HTTP(s) request are to specify details about the structure of anything you put into the request’s “body” and to accompany your request like authentication information like passwords.
    • Don’t play with passwords just yet. Please. :)

If you’re ever confused while you try to figure out how some API wants to be talked to, come back to Postman and play around until you make it work. Then move on to writing the same request with code. Don’t worry that it’s too “easy” – it’s what all developers do when they get stuck on a difficult API, no matter how much experience they have!

“More, please”

Note that some APIs will refuse to tell you everything they know at once.

For example, if you do a “GET” request against https://api.coinlore.com/api/tickers/, it will only send you price information about 100 different kinds of cryptocurrency.

Its response body looks kind of like this:

{
    "data": [
		{...a currency's price info...},
		...,
        {...a currency's price info...}
    ],
    "info": {
        "coins_num": 2173,
        "time": 1558125575
    }
}

So, we can see at the end of the response body that it actually knows about 2,173 currencies, even though there were only 100 {...a currency's price info...} chunks in the “data” list.

Even if I think I can “trick” it by making a request to https://api.coinlore.com/api/tickers/?start=1&limit=3000, I get the same response. Darnit.

What this API expects me to do is call it 100 chunks at a time, keeping track of where I left off, and taking my own responsibility to stop when I get to the end of the list.

It expects me to do:

  1. https://api.coinlore.com/api/tickers/?start=1&limit=100
  2. https://api.coinlore.com/api/tickers/?start=101&limit=100
  3. https://api.coinlore.com/api/tickers/?start=201&limit=100
  4. https://api.coinlore.com/api/tickers/?start=2101&limit=100

And it expects me to be responsible and not call it with “2201” since it already told me, in the very first response, that it only knew about 2,173 coins.

Obviously, this kind of repetition is going to be a lot easier to write into code (due to the ability to “loop”) than it is to do by hand in Postman.

Still, Postman is perfectly adequate for the kind of tests I just did like whether I could “trick” the API into giving me all of its data at once.

The takeaway is that when programming against APIs, you’ll need to read the documentation, and/or carefully inspect HTTP(s) responses, to determine if a single HTTP(s) request will actually give you everything you want to know.

Step 4: Repeat step 3, but with code

Now it’s time to learn how to use your programming language of choice to send HTTP(s) requests to servers and read the HTTP(s) responses they send back.

Salesforce Apex

Let’s say you want to teach Salesforce Apex code hosted in your org to make an “API call” to someone else’s service (e.g. one that knows things about U.S. postal ZIP codes).

First, practice writing and running Apex code that does the same tasks you previously explored using Postman.

Of course, you’ll want to be comfortable using System.debug(...) to see the textual output of Apex code you run so that you can see what comes back in the HTTP(s) responses from the APIs you talk to!

See these instructions on Trailhead to get started.

Python

Let’s say you want to download all the Prospects in your Pardot instance to your computer.

First, practice writing and running Python code that does the same tasks you previously explored using Postman.

Of course, you’ll want to be comfortable using print(...) to see the textual output of Python code you run so that you can see what comes back in the HTTP(s) responses from the APIs you talk to!

See these links for some documentation on dealing with HTTP(s) requests and responses in Python.

Step 5: Level up your programming skills in general

You won’t get far doing anything useful with a recipe from RecipePuppy or a fake blog post from JSONPlaceholder without knowing some “programming 101” fundamentals.

In your programming language of choice…

  1. Learn about “data structures” like:
    • Lists / Arrays
    • Dictionaries / Maps / Objects
    • Strings (you should understand the difference between a plain-text “string” in your code’s “memory” and a file saved on your hard drive containing a bunch of plain text).
  2. Learn how to select a given item from a list (by position number) or dictionary (by “key” name).
  3. Learn how to “loop” over the contents of a list or dictionary one item at a time.
    • In Python, you may quickly feel pressure to learn about “comprehensions,” a shorthand way to code loops. It’s what all the “cool kids” like to do, so you’ll see a lot of it in answers on StackOverflow.
  4. Learn how to translate back and forth between:
    • The plain-text representations of list/dictionary-like data (such as JSON or XML) that are in HTTP(s) requests/responses
    • The actual lists and dictionaries (and, in the case of Python when dealing with XML, “etree”s) that your programming language can actually process and “loop” over gracefully.
    • This is sometimes called “serialization(data structures to text) and “deserialization(text to data structures) in documentation you find online about your programming language.
  5. Practice doing these translations. Here’s an exercise:
    • Take a plain-text “string” that says {'animal1':'dog','animal2':'cat'} and transform it into a “string” that says {'pet-1':'DOG','pet-2':'CAT'}.
    • Do this by converting it into a dictionary/map/object with “animal1” and “animal2” keys, transforming that dictionary’s contents (either editing it or using it to build a new one), and converting the resulting dictionary back into text.
    • No cheating: don’t just do “find and replace” against the text itself.
  6. Learn how to read and write data to and from files on your hard drive (presuming you’re writing code that runs on your computer, like Python).
    • (Pro tip: never tell your code to overwrite the file you just read data from. Write to a different filename. That way, if you make a mistake, you can start over.)
    • Writing data to/from your hard drive is particularly important when downloading large data sets from other people’s cloud services. It’s rude and slow to re-download the same data every time you test whether your program works. Proper etiquette is to write one program to download the data to your hard drive and run it once. Then play around writing a different program that processes the data from the file on your hard drive. Don’t re-run the first program until the second program is working well enough that you actually need fresh data.
  7. Put it all together
    • Write code that makes an HTTP(s) “GET” request to https://yesno.wtf/api and save the response’s “body” to a file on your hard drive.
    • Write another program that reads the file from your hard drive and turns its contents into a dictionary/map/object with 3 “keys” (answer, forced, and image).
    • All-caps the “value” corresponding to the “answer” key.
    • Turn your dictionary/map/object back into “JSON”-formatted plain text and save it to a different file on your hard drive.
    • Your 2nd file should look just like the 1st file, only the yes/no should be YES/NO.
    • (If you’re doing this exercise in Salesforce Apex, skip the “file” parts of the exercise. You can see the output of your final plain-text String with System.debug(...).)

Step 6: SECURITY SECURITY SECURITY

Be sure you understand how careful you need to be any time you type a password into code.

  1. Promise me you won’t start trying to program against “APIs” that require passwords irresponsibly.
  2. Postman lets you save configuration files for later reuse. Promise me you won’t save passwords, “api keys,” “tokens,” etc. in Postman configuration files.
  3. Promise me you won’t save passwords, “api keys,” “tokens,” etc. in your code when you’re not actively editing it.
  4. Promise me you will never ever ever simply type a password, “api key,” or “token” into code and leave it somewhere on a server so that it can “run automatically.”

Step 7: Find inspiration

Browse my blog posts tagged “API” for inspiration about projects I’ve done like clean up data in Pardot.

Happy progrmaming!