Synchronizing Salesforce fields with Excel data

There are many possible options to synchronize data with Salesforce. Here, I am going to describe how to do a one-way sync (Excel to SFDC) every hour with RAW Labs Just-Ask and some APEX code.

I will first show how to make the Excel data available through a simple REST API and then the work on the Salesforce side.

A typical use case for this is when someone in the company holds information about accounts in an Excel spreadsheet and doesn’t share it. For instance the CFO could maintain a document with debts per accounts.

If you want the sales team to be aware of amounts owed by customers in Salesforce and keep this updated, you could create a Currency field in the Account and name it “Account Debt” for instance and then maintain it.

To synchronize, we need a way to relate the Accounts in Salesforce with the ones listed in the CFO’s spreadsheet. We are going to do this through the “AccountID” custom field. The same field should be common at both ends, on Salesforce and in the Excel file.

Creating the REST API on the Excel side

One easy way for the CFO to share his spreadsheet is to use dropbox. If the data is available there, then in the Just-Ask scratchpad, once the Dropbox credentials correctly set, the following statement will return the requested data.

select AccountID, Debt
from read_excel("dropbox:///Customer_debts.xlsx")

We can publish this as a view and name it customer_debt via the “Publish” button in the scratchpad.

To get access authorization to the endpoint, you need an “access_token” which is made available in the ~/.rawcli file after running the following command :

rawcli login -f

Then you can issue the following curl command to verify if the REST endpoint is working correctly.

curl -X GET https://just-ask.raw-labs.com/api/executor/1/public/query-view/customer_debt — header “Authorization: Bearer your_token_here ” — header “X-RAW-output-format: json”

This will produce a JSON document with the Excel rows. Now that we have an endpoint ready let’s look at the Salesforce part.

Creating the Authentication provider in Salesforce

We need to create an authentication provider in Salesforce in order to be able to access the RAW Labs endpoint thru “Open ID Connect” (a simple authentification provider based on OAuth2.0)

Authentication providers are managed under the Identity menu in Salesforce. Here I created an Open ID Connect authentication provider named “RAWAuth”.

Authentication providers in Salesforce

note: RAW Labs currently uses Auth0 services for authorization and token management, hence the URLs: “http://raw.eu.auth0.com/…”

The provider requires a client_id and a client_secret key that can be found in the ~/raw.ini file under the [auth_oauth2] section.

Client_id and client_secret keys

Salesforce APEX has an elegant way to manage endpoints if you don’t want to code all the http request boilerplate every time, it is called “Named credentials”.

Named credentials in Salesforce

Here, I have created a Named Credential: RAWLabs which uses the RAWAuth authentication provider with the following scope: openid, profile, email and offline_access.

This mechanism enables an easy way to make callouts in Apex:

HttpRequest req = new HttpRequest();
req.setEndpoint('callout:RAWLabs/.../somepath');
req.setMethod('GET');
Http http = new Http();
HTTPResponse res = http.send(req);

Creating the APEX code

Now that we have created the API and the named credential we need a piece of APEX code to fetch the data and update the fields in Salesforce.

We want this code to be ran every hour, so that if the Excel file is updated by the CFO because a customer has paid an invoice or because another has received one, this is reflected within an hour maximum.

To achieve this we need to implement a Schedulable class:

And the class that does the job where we create a method with a special annotation “@future(callout=true)” because we are going to make an asynchronous callout. This method will:

• call the API via the Salesforce callout
• deserialize the {AccountId,Debt} JSON values returned
• query the Accounts in Salesforce that have “Customer — Direct” or “Customer — Chanel” Type because others types do not have debts.
• update all Accounts where the “Debt” value in Salesforce is different from the one found in the Excel file

Both classes need to be compiled in Salesforce to be usable (this is done in the Setup | Apex Classes tab).

Scheduling the job in Salesforce

The last thing we have to do is to tell Salesforce to run the job every hour.

Scheduled jobs are managed in the Setup | Scheduled Jobs tab, but there you can not create an hourly task, only daily/weekly or monthly are configurable.

If you need to create a job that runs several times a day, you need to go to the developer console and create a cron job by hand with the “Open Execute Anonymous Windows” and type for instance the following.

// Cron EXP for an hourly schedule
String CRON_EXP = '0 0 * * * ?';
SheduledBatchable schJob = new CheckDebt();
system.schedule('Hourly Example Batch Schedule job', CRON_EXP, schJob);

Alternatively you can include the code in Apex class.

Once this piece of code executed, the system will start to run the Apex class every hour and update your Account’s Debt field to reflect the changes in the Excel file.