Calculates the sum of the values in a numeric column. If non-numeric data is mixed in the specified column, the record is regarded as zero and will not be added.
Configs
A: Select STRING DATA for TSV Text *
B: Set ID of Numeric Column to be summed (e.g. “0”) *
C: Select NUMERIC DATA for Sum of Column (update) *
Script (click to open)
// GraalJS Script (engine type: 2)
// (c) 2021, Questetra, Inc. (the MIT License)
/*
=input tsv=
A1 10000 C1
A2 $9900 C2
A3 10800 C3
=column id=
1
=output value=
20800
*/
//////// START "main()" ////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA = configs.get( "conf_DataIdA" ) + ""; // config required
const columnB = configs.get( "conf_ColumnB" ) + ""; // config required
const dataIdC = configs.get( "conf_DataIdC" ) + ""; // config required
// 'java.lang.String' to javascript primitive 'string' by ""
engine.log( " AutomatedTask Config: ID of Numeric Column: " + columnB );
// Design-time Config Format Check
const intColumnB = parseInt( columnB );
if ( isNaN( intColumnB )){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {ID of Numeric Column} is not an integer \n" );
}
//// == Data Retrieving / ワークフローデータの参照 ==
// Run-time Data Format Check
if( engine.findDataByNumber( dataIdA ) === null ){
throw new Error( "\n AutomatedTask UnexpectedStringError:" +
" String {A} as TSV is null \n" );
}
const inputText = (engine.findDataByNumber( dataIdA ) + "").trim();
if( inputText === "" ){
throw new Error( "\n AutomatedTask UnexpectedStringError:" +
" String {A} as TSV is empty \n" );
}
//// == Calculating / 演算 ==
let outputNum = 0;
const arrInputText = inputText.split("\n");
engine.log( " AutomatedTask MultilineString:" +
" String {A} as TSV, number of lines: " + arrInputText.length );
for( let i = 0; i < arrInputText.length; i++ ){
let arrCellValues = arrInputText[i].split("\t");
let strValueB = arrCellValues[ intColumnB ];
let numValueB = parseFloat( strValueB );
// Run-time Data Format Check
if ( isNaN( numValueB )){
engine.log( " AutomatedTask StringWarning:" +
" CellStr is not numeric at " + i + ": " + strValueB );
numValueB = 0;
}
outputNum += numValueB;
}
//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdC, new java.math.BigDecimal( outputNum ) );
} //////// END "main()" ////////
