Singleline CSV String, Extract Cell
Extracts the cell value of the specified ID, starting from zero. The extracted cell string can be stored not only in the String-type data but also in the Numeric-type, Date-type, Datetime-type, Select-type, User-type, and Org-type data.
Configs
  • A: Set Singleline CSV String *#{EL}
  • B: Set ID of Cell to be extracted *#{EL}
  • C: Select DATA to store Extracted Cell String (update) *
Script (click to open)
// GraalJS Script (engine type: 2)
/*
NOTES
- An error will occur, if Singleline CSV String is empty.
- An error will occur, if ID does not exist (eg. greater than the number of tabs)
- The data will be deleted if the extracted Cell string is empty. (overwritte with "null")
- Numerical String is evaluated with "parseFloat()" after removing the comma.
  - An error will occur, if failed to parse.
  - Truncation after the decimal point depends on the format of DataItem definition
- Date or Datetime String is evaluated with "Date()"
  - An error will occur, if failed to parse.
  - new Date('December 17, 1995 03:24:00')
  - new Date('1995-12-17T03:24:00')
  - new Date('1995-12-17 03:24')
  - new Date('1995-12-17')
    - In principle, "format including time" is interpreted as local time.
    - In principle, "date only format" is interpreted as UTC time.
    - However, in case of "YYYY-MM-DD", "T00:00:00" is added and localized.
- SELECT String will be stored only if match the Choice ID (option id)
  - NO ERROR will occur (cannot be detected), even if it does not match any ID.
- QUSER String will be stored only if match the user id or email address.
  - An error will occur, if user not found.
- QORG String will be stored only if match the organization id.
  - An error will occur, if organization not found.
- 単一行CSV文字列が空文字列の場合、エラーとなります
- 指定した抽出IDが存在しない場合(CSVのタブ数を超える場合など)、エラーとなります
- 抽出した文字列が空文字列の場合、格納先のデータは削除されます(null で上書)
- 数値文字列は、"parseFloat()" 関数で評価されます
  - カンマ(桁区切り文字)は、含まれていないことが前提
  - 文字列の解析に失敗した場合、エラーとなります
  - 小数点以下の切り捨て処理は、格納先データ項目の書式に寄ります
- 日付文字列および日時文字列は、"Date()" 関数で評価されます
  - 文字列の解析に失敗した場合、エラーとなります
  - new Date('December 17, 1995 03:24:00')
  - new Date('1995-12-17T03:24:00')
  - new Date('1995-12-17 03:24')
  - new Date('1995-12-17')
    - なお「時刻を含む形式」はローカルタイムとして解釈されます
    - また「日付のみの形式」は原則 UTC 時刻と解釈されます
    - ただし "YYYY-MM-DD" 書式は "T00:00:00" が自動付加(ローカルタイム化)されます
- 選択文字列は、選択肢IDに合致する場合に限り代入されます
  - なお、いずれのIDにも合致しない場合でも、エラーになりません(検知できません)
- ユーザを表す文字列は、ユーザIDもしくはメールアドレスに合致する場合に限り代入されます
  - ユーザが存在しない場合、エラーとなります
- 組織を表す文字列は、組織IDに合致する場合に限り代入されます
  - 組織が存在しない場合、エラーとなります
*/
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const strCsv = configs.get( "StrConfA" ) + ""; // required
const strCellId = configs.get( "StrConfB" ) + ""; // required
const multiPocketC = configs.getObject( "SelectConfC" ); // required
if( strCsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
            " Config {A: CSV} is empty \n" );
}
if( strCellId === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
            " Config {B: ID} not specified \n" );
}
const regPositiveInt = /^([1-9]\d*|0)$/; // RegExp
if( ! regPositiveInt.test( strCellId ) ){
  throw new Error( "\n AutomatedTask ConfigError:" +
            " Config {B:ID} must be a positive integer or zero \n" );
}
let numCellId = parseInt( strCellId, 10 );
//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)
//// == Calculating / 演算 ==
const arrCsv = strCsv.split(",");
engine.log( " AutomatedTask Csv.length: " + arrCsv.length );
engine.log( " AutomatedTask ID of Cell: " + strCellId );
if( arrCsv.length < numCellId ){
  throw new Error( "\n AutomatedTask ConfigError:" +
            " Config {B:ID} must be less than CSV length\n" );
}
const strCellValue = arrCsv[ numCellId ];
if( strCellValue === "" ){
  engine.log( " AutomatedTask CellString[" + numCellId + "]: (empty)" );
  engine.setData( multiPocketC, null );
  return;
}
engine.log( " AutomatedTask CellString[" + numCellId + "]: " + strCellValue );
//// == Data Updating / ワークフローデータへの代入 ==
const regBpmsYMD = /^\d{4}-\d{2}-\d{2}$/; // RegExp
const regEmail = /^[\w][\w_\.\-\+]*@[\w\.\-]+\.[a-zA-Z]+$/; // RegExp
// ref) Retrieving / Updating
// https://questetra.zendesk.com/hc/en-us/articles/360024574771-R2301
// https://questetra.zendesk.com/hc/ja/articles/360024574771-R2301
if( multiPocketC.matchDataType("STRING") ){
  engine.setData( multiPocketC, strCellValue );
}else if( multiPocketC.matchDataType("DECIMAL") ){
  let numCellValue = parseFloat( strCellValue.replace(/,/g, "") );
  if( isNaN( numCellValue ) ){
    throw new Error( "\n AutomatedTask UnexpectedStringError:" +
              " Invalid Numeric Value \n" );
  }
  engine.setData( multiPocketC, new java.math.BigDecimal( numCellValue ) );
}else if( multiPocketC.matchDataType("DATE") ){
  if( regBpmsYMD.test( strCellValue ) ){
  engine.setData( multiPocketC, java.sql.Date.valueOf( strCellValue ) );
  }else{
    engine.log( " AutomatedTask DateStringWarning:" +
          " CellValue should be YYYY-MM-DD" );
    let numMillSec = Date.parse( strCellValue );
    if( isNaN( numMillSec ) ){
        throw new Error( "\n AutomatedTask UnexpectedStringError:" +
                  " Invalid Date Value \n" );
    }
    engine.setData( multiPocketC, new java.sql.Date( numMillSec ) );
  }
}else if( multiPocketC.matchDataType("DATETIME") ){
  let strDatetimeTmp = strCellValue;
  if( regBpmsYMD.test( strDatetimeTmp ) ){
    strDatetimeTmp += "T00:00:00";
  }
  let numMillSec = Date.parse( strDatetimeTmp );
  if( isNaN( numMillSec ) ){
    throw new Error( "\n AutomatedTask UnexpectedStringError:" +
              " Invalid Datetime Value \n" );
  }
  engine.setData( multiPocketC, new java.sql.Timestamp( numMillSec ) );
}else if( multiPocketC.matchDataType("SELECT_SINGLE") ){
  let arrTmp = new java.util.ArrayList();
  arrTmp.add( strCellValue );
  engine.setData( multiPocketC, arrTmp );
}else if( multiPocketC.matchDataType("QUSER") ){
  let quserTmp;
  if( regPositiveInt.test( strCellValue ) ){
    quserTmp = quserDao.findById( parseInt(strCellValue) );
    if( quserTmp === null ){
      throw new Error( "\n AutomatedTask UnexpectedStringError:" +
                " Invalid User Value \n" );
    }
    engine.setData( multiPocketC, quserTmp );
  }else if( regEmail.test( strCellValue ) ){
    quserTmp = quserDao.findByEmail( strCellValue );
    if( quserTmp === null ){
      throw new Error( "\n AutomatedTask UnexpectedStringError:" +
                " Invalid User Value \n" );
    }
    engine.setData( multiPocketC, quserTmp );
  }else{
    throw new Error( "\n AutomatedTask UnexpectedStringError:" +
              " CellValue must be UserId or UserEmail \n" );
  }
}else if( multiPocketC.matchDataType("QGROUP") ){
  let qorgTmp;
  if( regPositiveInt.test( strCellValue ) ){
    qorgTmp = qgroupDao.findById( parseInt(strCellValue) );
    if( qorgTmp === null ){
      throw new Error( "\n AutomatedTask UnexpectedStringError:" +
                " Invalid Organization Value \n" );
    }
    engine.setData( multiPocketC, qorgTmp );
  }else{
    throw new Error( "\n AutomatedTask UnexpectedStringError:" +
              " CellValue must be OrganizationId \n" );
  }
}else{ // Unknown
  throw new Error( "\n AutomatedTask CriticalError:" +
            " DataType Unmatched \n" );
}
} //////// END "main()" /////////////////////////////////////////////////////////////////
/*
APPENDIX
- Error Log sample1
  - AutomatedTask ConfigError: Config {B:ID} must be less than CSV length
- Error Log sample2 (Wrong Email, Wrong Id, etc)
  - AutomatedTask UnexpectedStringError: Invalid User Value
*/

Download

2022-02-20 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/singleline-csv-string-extract-cell-2023/
The Addon-import feature is available with Professional Edition.

Notes

  • An error will occur if the single line TSV String is empty
  • An error will occur if the ID does not exist (e.g. greater than the number of tabs)
  • The data will be deleted if the extracted cell string is empty (overwritten with “null”)
  • A numerical string is evaluated with the “parseFloat()” function after removing the comma
    • An error will occur if the string parse fails
    • Truncation after the decimal point depends on the format of the Data Item
  • A Date or Datetime String is evaluated with “Date()”
    • An error will occur if the string parse fails
    • new Date(‘December 17, 1995 03:24:00’)
    • new Date(‘1995-12-17T03:24:00’)
    • new Date(‘1995-12-17 03:24’)
    • new Date(‘1995-12-17’)
      • In principle, “format including time” is interpreted as local time
      • In principle, “date only format” is interpreted as UTC time
      • However, in case of “YYYY-MM-DD”, “T00:00:00” is added and localized
  • Select strings will be stored only if they match the Choice ID
    • NO ERROR will occur (cannot be detected) even if it does not match any ID
  • A QUSER String will be stored only if it matches the User ID or email address
    • An error will occur if the user is not found
  • A QORG String will be stored only if it matches the Organization ID
    • An error will occur if the organization is not found

Capture

Appendix

  • Error Log sample1
    • AutomatedTask ConfigError: Config {B:ID} must be less than CSV length
  • Error Log sample2 (Wrong Email, Wrong Id, etc)
    • AutomatedTask UnexpectedStringError: Invalid User Value

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: