Salesforce: Object records BatchGet to Table
Gets target object record times and stores items in a Table-type Data Item.
Configs
- A: Select HTTP_Authz (OAuth2) *
- B: Set Sub-Domain (https://{subdomain}.salesforce.com/) *
- C: Query for Salesforce API Query *#{EL}
- D: Select LIST DATA (update) *
- E: EDIT String for json top level key name eg. records *
- F: json key/Table Column Field Name eg.CaseNumber:Number,… *
- G: Select STRING DATA for Access Log (update)
- H: Select Decimal DATA for Records Count (update)
Script (click to open)
// GraalJS Script (engine type: 2)
// Batch to Get Accounts from Salesforce via Salesforce REST API (ver. 50, Winter'21)
//
// OAuth2 config
// Authorization Endpoint URL: https://login.salesforce.com/services/oauth2/authorize
// Token Endpoint URL: https://login.salesforce.com/services/oauth2/token
// Scope:
// Consumer Key: (Get by Salesforce Connected App screen)
// Consumer Secret: (Get by Salesforce Connected App screen)
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == 工程コンフィグの参照 / Config Retrieving ==
const oauth2 = configs.get( "conf_OAuth2" ); //required
const subDomain = configs.get( "conf_SubDomain" ); //required
const strQuery = configs.get( "conf_Query" ); //required
const objectList = configs.getObject( "conf_DataIdD" ); //required
const strTopKey = configs.get( "conf_DataIdE" ); //required
const strConfigJsonPaths = configs.get( "conf_DataIdF" ); //required
const strResponse = configs.getObject( "conf_DataIdG" );
const decRecordCount = configs.getObject( "conf_DataIdH" );
//// == ワークフローデータの参照 / Data Retrieving ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)
//// == 演算 / Calculating ==
const uri = "https://" + subDomain + ".salesforce.com/services/data/v50.0/query/";
const response = httpClient.begin()
.authSetting(oauth2)
.queryParam("q", strQuery)
.get(uri);
engine.log( " AutomatedTask ApiRequest Start: " + uri );
const responseCode = response.getStatusCode() + ""; // (primitive string);
const responseBody = response.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + responseCode );
if( responseCode !== "200" ){
throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
responseCode + "\n" + responseBody + "\n" );
}
if( strResponse !== null){
engine.setData( strResponse, responseBody );
}
const objJsonText = JSON.parse( responseBody );
// set key for getting data
const arrStrConfigJsonPaths = strConfigJsonPaths.split(",");
let arrJsonPathValues = new Array(objJsonText[strTopKey].length);
engine.log( " AutomatedTask: # lenght of arrJsonPathValues: " + arrJsonPathValues.length );
// setup List Type data item
let table = engine.findData(objectList);
table = objectList.createListArray();
//engine.log("responseBody: " + responseBody );
// parse json
for (let i=0; i < objJsonText[strTopKey].length; i++){
arrJsonPathValues[i] = new Array(arrStrConfigJsonPaths.length);
let row = table.addRow();
for (let j=0; j < arrStrConfigJsonPaths.length; j++){
let strConfigJsonPathAndCol = arrStrConfigJsonPaths[j].split(":");
let arrSubKey = strConfigJsonPathAndCol[0].split(".");
let obj = objJsonText[strTopKey][i];
for (let k=0; k < arrSubKey.length; k++){
obj = obj[arrSubKey[k]];
}
if (obj === null){
arrJsonPathValues[i][j] = "";
}else{
arrJsonPathValues[i][j] = obj;
}
// engine.log("arrJsonPathValues[" + i + "][" + j+ "]" + arrJsonPathValues[i][j]);
row.put(strConfigJsonPathAndCol[1] , arrJsonPathValues[i][j].toString() );
}
}
//// == ワークフローデータへの代入 / Data Updating ==
// テスト用ログ表示
for (let i=0; i < arrJsonPathValues.length; i++){
for (let j=0; j < arrJsonPathValues[i].length; j++){
engine.log("arrJsonPathValues[" + i + "][" + j + "]=" + arrJsonPathValues[i][j] );
}
}
engine.setData(objectList, table);
engine.setData(decRecordCount, new java.math.BigDecimal(objJsonText[strTopKey].length) );
if( strResponse !== null){
engine.setData( strResponse, responseBody );
}
} //////// END "main()" /////////////////////////////////////////////////////////////////
Download
2022-08-02 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/salesforce-object-batchget-to-table-202208/
The Addon-import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.
Notes
- “C: Query for Salesforce API Query” settings
- Describe the SOQL(Salesforce Object Query Language) for the object records you wish to extract.
- Setting example: SELECT id,CaseNumber,RecordTypeId,SuppliedName,Subject,Account.Website FROM Case
- Get the “id,CaseNumber,RecordTypeId, SuppliedName,Subject,Account.Website” Data Items of the “Case” object.
- Example of specifying a Data Item in a relationship with a Salesforce object: “Account.Website” -> `{object name}. {data item name}`
- “F:json key/corresponding Table-type field Id e.g. CaseNumber:Number” setting
- Enumerate the key of the object record (Json) retrieved from Salesforce and the column field name of the corresponding Table-type Data Item.
- Entry format: {API data item name}:{table type data item column field name},{API data item name}:{table type data item column field name}…
- Setting example: SuppliedName:webName
- Salesforce API Data Item Name: SuppliedName
- Questetra table type data item column field name: webName
Capture
See also
Like this:
Like Loading...
Related