2023-08-29 / Add-ons /
Singleline TSV String, Extract Cell
Singleline TSV 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 TSV 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 TSV 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.

- 単一行TSV文字列が空文字列の場合、エラーとなります
- 指定した抽出IDが存在しない場合（TSVのタブ数を超える場合など）、エラーとなります
- 抽出した文字列が空文字列の場合、格納先のデータは削除されます（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 strTsv       = configs.get( "StrConfA" ) + "";     // required
const strCellId    = configs.get( "StrConfB" ) + "";     // required
const multiPocketC = configs.getObject( "SelectConfC" ); // required

if( strTsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {A: TSV} 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 arrTsv       = strTsv.split("\t");
engine.log( " AutomatedTask Tsv.length: " + arrTsv.length );
engine.log( " AutomatedTask ID of Cell: " + strCellId );
if( arrTsv.length < numCellId ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {B:ID} must be less than TSV length\n" );
}
const strCellValue = arrTsv[ 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 TSV length
- Error Log sample2 (Wrong Email, Wrong Id, etc)
    - AutomatedTask UnexpectedStringError: Invalid User Value 
*/

Download

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

Extracts the cell value of the specified ID. The ID starts 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.

Appendix

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

See also

Multiline String, Dequeue First
Google Sheets: Sheet, Append TSV

1 thought on “Singleline TSV String, Extract Cell”

  1. Pingback: Multiline String, Dequeue First – Questetra Support

Leave a Reply

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

%d bloggers like this: