T27 Salesforce REST API Connector via JSLOver the past few months, I've received a few requests from SEs asking how to query Salesforce data through JMP. The attached files provide the scripting framework that I use to connect JMP to Salesforce and query data. Below is a description of the attached scripts, as well as the process on how to use them to successfully set everything up:
- Generate Refresh Token.jsl
- This script should only be run ONCE!!! Once a refresh token is generated, it will persist indefinitely. Rerunning the script will reset the refresh token, which will affect all future steps.
- There are 2 fields that need to be entered in this script: client id and client secret
- These values need to be obtained from within Salesforce.
- These values will need to be entered in the correct positions in lines 13, 19, and 20 in the script.
- Run the script after the client id and client secret are entered, and the "rtoken" field will be displayed in the log. Save this value in a safe place - this is the refresh token that has been generated. As per above, this token will persist indefinitely, so do not rerun this script again unless you need a new refresh token.
- Generate Auth Fields.jsl
- Now that the refresh token has been obtained, the access token needed to access data in Salesforce can be obtained. The function in this script generates the access token and other fields necessary to run a query, returning these values in the form of an Associative Array. There are 3 values that need to be input into this function (listed in the order that they should appear as inputs):
- rtoken - this is the refresh token generated from the above script
- client_id - this is the client id taken from within Salesforce
- client_secret - this is the client secret taken from within Salesforce
- Below are the keys and corresponding values that get returned in the Associative Array generated by this function:
- client_id - this is the client id that was initially input
- access_token - this is the access token needed to access data in Salesforce
- instance_url - this is the URL of your organization's Salesforce instance,
- Query Formula String.jsl
- This script contains the function needed to query data from Salesforce. The function has 5 inputs (listed in order of input below), and returns a data table.
- st - this is the SOQL query string that specifies the data that is being queried. Workbench is a great resource for generating SOQL queries.
- access_token - this should be taken from the Associative Array generated in the above script.
- client_id - this should be taken from the Associative Array generated in the above script.
- instance_url - this should be taken from the Associative Array generated in the above script.
- tablename - this should be set as the desired table name for the data table generated by this function. If left blank, it will automatically name the generated table as "Salesforce Generated Table".
- Example Query.jsl
- This script contains an example of how to use the "Generate Auth Fields.jsl" and "Query Formula String.jsl" scripts to generate a table of Salesforce data. In order for this script to work, the client will have to enter their specialized inputs in the Generate Auth Fields function.
Information ReferencesScriptsTraining MaterialsRe: Salesforce REST API Connector via JSLThanks for the information @andyzhou. I had inferred that the post originated for SEs to potentially tap into SF to aid their sales teams, which suggests the admin privileges weren't required or were already possessed. I'll set aside my JSL tinkering ambitions with SF for the time being.
Re: Salesforce REST API Connector via JSLHi @christianstopp ,
Correct, obtaining a client ID and client secret is done by creating a Connected App. Ours was set up awhile ago, but I believe that only admins can create a Connected App and set it up.
Hmm from what I've seen online, there doesn't look to be a way to get the underlying SOQL code for a report. You should be able be able to somewhat guess what fields are being used, but often the field names in the report don't match up to the API field names. Workbench is a good resource for figuring out what the exact API field names are for a given SF object.
Andy
Re: Salesforce REST API Connector via JSLHi @andyzhou ,
Thanks for breaking this down for potential use. Regarding finding the client ID and client secret, are special privileges required for use? I've been perusing various websites for how to obtain them, but they encourage creating a new app and I have been unable to locate either the specific Platform Tool referenced or the "New" button within the Platform Tool I do find (e.g. Installed Packages > SF Connected Apps).
Also, it appears the request is just a SQL query passed along in the HTTP request, and I assume the reports we create in SF are based on SQL queries. Do you know if there is a way to get the SQL query used for creating a SF report?
Thanks for any insight you have into these items.
Christian
