Simple NodeJS utility that fetches Redash query results via API and upserts the data to desired Salesforce objects using the Salesforce REST Bulk API, leveraging the lightweight node-salesforce-connection package for session authentication and REST request abstraction.

Robust logging with winston.

$ git clone [email protected]:codygordon/redash-to-salesforce.git
$ cd redash-to-salesforce
$ npm i

First, copy .env.example to a new .env (make sure to leave .env.example intact) and enter environmental variables (all are required for script to run).

$ cp .env.example .env

Second, edit lib\config.js and enter objects into the config constant (array) for each ReDash query / Salesforce object being processed, for example:

const config = [
  {
    redashQueryId: 55,
    salesforceObject: 'contact',
    salesforceExtId: 'your_user_id__c',
  },
  {
    redashQueryId: 56,
    salesforceObject: 'opportunity',
    salesforceExtId: 'your_opportunity_id__c',
    salesforceLookups: [{
      relationship: 'contact__r',
      extId: 'your_user_id__c',
    }],
  },
]

Each upsert process will be handled synchronously in the config array's indexed order allowing for lookups to be handled properly (i.e., a Contact must exist before a related Opportunity can reference it), so make sure the objects are in the order you want them to run.

You should set up a dedicated API User to use with this script.

For each Salesforce object with data being upserted into there must be a separate ReDash query with appropriate columns and data formatting.

The script will fail if the following conditions aren't met:

• All columns in query must exist as fields in Salesforce object
• ReDash query column must match existing Salesforce field names exactly
  • Exception: Luckily, Salesforce field names are case insensitive when using the API, as the ReDash query API only exports field names in lowercase
  • Custom fields must end with __c (this will not display in the #table query results, but it will still export correctly when using the API)
• Date columns in query must be in the format of YYYY-MM-DD
  • See "Date" and "Date Time" format in Data Loader and API
• For lookups the script will convert the query column into the required nested JSON format
  • The column name and the config's salesforceLookups.relationship value must both exactly match the relationship name (i.e., Contact__r)
  • The column values must contain the related object's external ID that matches the config's salesforceLookups.extId

Webpack is used to build into single production dist file:

$ npm run build

You may start the script in development mode (which uses nodemon and will log to the console) by running:

$ npm start

You can either have an external service such as crontab run the built script at redash-to-salesforce\dist\index.js, or run:

$ npm run prod

In production mode a log file will be created in the specified directory each time the script is run and no logs will be sent to the console.

1. Write test suites in Mocha