Generates a new TSV string by multiplying N by each number in the number column. Calculation results are rounded off and added at the end of the line. This can be used for tax calculations, etc. Removes whitespace from both ends of a TSV string.
Configs
A: Select STRING DATA for TSV Text *
B: Set ID of Numeric Column to be multiplied (e.g. “0”, “3”) *
C: Set Value to multiply (e.g. “1.1”, “1.0185185185”) *
D: Select STRING DATA for New TSV Text (update) *
Script (click to open)
// GraalJS Script (engine type: 2)
// (c) 2021, Questetra, Inc. (the MIT License)
/*
=input tsv=
A1 10000 C1
A2 10800 C2
=N=
1.0185185185
=output tsv=
A1 10000 C1 10185
A2 10800 C2 11000
*/
//////// START "main()" ////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA = configs.get( "conf_DataIdA" ) + ""; // config required
const columnB = configs.get( "conf_ColumnB" ) + ""; // config required
const valueC = configs.get( "conf_ValueC" ) + ""; // config required
const dataIdD = configs.get( "conf_DataIdD" ) + ""; // config required
// 'java.lang.String' to javascript primitive 'string' by ""
engine.log( " AutomatedTask Config: ID of Numeric Column " + columnB );
engine.log( " AutomatedTask Config: Value to multiply " + valueC );
// 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" );
}
const numValueC = parseFloat( valueC );
if ( isNaN( numValueC )){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {Value to multiply} is not a number \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 outputText = "";
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 )){
throw new Error( "\n AutomatedTask UnexpectedStringError:" +
" String {A} as TSV, Specified Column is not numeric at " + i + "\n" );
}
let newValue = Math.round( numValueB * numValueC );
outputText += arrInputText[i] + "\t" + newValue;
if( i !== arrInputText.length - 1 ){
outputText += "\n";
}
}
//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdD, outputText );
} //////// END "main()" ////////
