Google スプレッドシート: 行取得

// OAuth2 config
// Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// Token Endpoint URL: https://accounts.google.com/o/oauth2/token
// Scope: https://www.googleapis.com/auth/spreadsheets.readonly
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)

const COLUMN_NUM = 10;

main();
function main(){
  //// == 工程コンフィグの参照 / Config Retrieving ==
  const oauth = configs.get( "conf_OAuth2" );
  const sheetId = configs.get( "conf_SheetId" );
  const sheetTitle = configs.get( "conf_SheetTitle" );
  const rowNo = retrieveRowNo();
  const range = configs.get( "conf_Range" );

  checkRowNoAndRange( rowNo, range );
  const columnDefList = [];
  retrieveValueConfigs( columnDefList );
  
  
  //// == 演算 / Calculating ==
  const dataStringList = getRowData( oauth, sheetId, sheetTitle, rowNo, range );

  //// == ワークフローデータへの代入 / Data Updating ==
  setDataByLists( columnDefList, dataStringList );
}


/**
  * config から行番号 を読み出す
  * @return {String} rowNo  行番号
  */
function retrieveRowNo() {
  const rowNoDef = configs.getObject( "conf_RowNo" );
  let rowNo = configs.get( "conf_RowNo" );
  if ( rowNoDef !== null ) {
    rowNo = engine.findData( rowNoDef );
  }
  return rowNo;
}


/**
  * 行番号が空、不正な文字列であればエラーとする
  * 列範囲が不適切な文字列であればエラーとする
  * @param {String} rowNo  行番号
  * @param {String} range  列範囲
  */
function checkRowNoAndRange( rowNo, range ) {
  if ( rowNo === "" || rowNo === null ) {
    throw "Row number is empty.";
  }
  if ( !isValidNo(rowNo) ) {
    throw "Invalid Row number.";
  }
  if ( !isValidRange(range) ) {
    throw "Invalid Range.";
  }
}


/**
  * 行番号 が有効か（自然数か）を判定する
  * @param {String} noString  行番号 の文字列
  * @return {Boolean}  有効な 行番号 かどうか
  */
function isValidNo( noString ) {
  const idReg = new RegExp( '^[1-9][0-9]*$' );
  return idReg.test( noString );
}

/**
  * 列範囲 が有効かを判定する
  * @param {String} rangeString  列範囲 の文字列
  * @return {Boolean}  有効な 列範囲 かどうか
  */
function isValidRange( rangeString ) {
  const idReg = new RegExp( '^[A-Z]+:[A-Z]+$' );
  return idReg.test( rangeString );
}


/**
  * config の列の値を格納するデータ項目の情報を読み出し、配列に格納する
  * 以下の場合はエラーとする
  * 1. 値を保存するデータ項目が重複して設定されている
  * 2. 列の値を格納するデータ項目が一つも設定されていない
  * @param {Array<ProcessDataDefinitionView>} valueDefList  列の値を格納するデータ項目の ProcessDataDefinitionView を格納する配列
  */
function retrieveValueConfigs( columnDefList ) {
  const dataItemNumList = []; // データ項目の重複確認用
  for (let i = 0; i < COLUMN_NUM; i++) {
    const columnConfigName = `conf_Column${i+1}`;
    const columnValueDef = configs.getObject( columnConfigName );
    columnDefList.push( columnValueDef );
    if ( columnValueDef !== null ) {
      const dataItemNum = configs.get( columnConfigName ); // データ項目の重複確認用
      if ( dataItemNumList.indexOf( dataItemNum ) !== -1 ) { // 既に指定されているデータ項目
        throw "The same data item is set multiple times.";
      }
      dataItemNumList.push( dataItemNum ); // データ項目の重複確認用
    }
  }
  if ( columnDefList.filter( v => v !== null ).length === 0 ) { // 列の値を格納するデータ項目が一つも設定されていない
    throw "No Data Item is set.";
  }
}



/**
 * Google スプレッドシートの行データを取得
 * @param {String} oauth OAuth2 認証設定
 * @param {String} sheetId スプレッドシートの ID
 * @param {String} sheetTitle シート名
 * @param {String} rowNo 取得する行
 * @param {String} range 取得する列範囲
 * @return {Array<String>} jsonObj.values[0] 取得した行データ を格納した配列
 */
function getRowData( oauth, sheetId, sheetTitle, rowNo, range ) {

  const rangeArr = range.split(':');
  const enSheetId = encodeURIComponent(sheetId);
  const enSheetTitle = encodeURIComponent(sheetTitle);

  // Method: spreadsheets.values.get
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${enSheetId}/values/${enSheetTitle}!${rangeArr[0]}${rowNo}:${rangeArr[1]}${rowNo}`;
  const response = httpClient.begin()
    .authSetting( oauth )
    .queryParam( "valueRenderOption", "UNFORMATTED_VALUE" )
    .queryParam( "dateTimeRenderOption", "FORMATTED_STRING" )
    .queryParam( "majorDimension", "ROWS" )
    .get( uri );
  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if (status !== 200) {
    const accessLog = `---GET request--- ${status}\n${responseStr}`;
    engine.log( accessLog );
    throw `Failed to get. status:${status}`;
  }
  const jsonObj = JSON.parse( responseStr );
  if (jsonObj.values === undefined ) {
    throw `No Data in range.`;
  }

  const dataStringList = jsonObj.values[0]
    .slice(0, COLUMN_NUM) // 最大10列
    .map( v => v.toString() ); // すべて String に変換

  return dataStringList;
}


/**
  * データ項目に出力する
  * @param {Array<ProcessDataDefinitionView>} valueDefList  保存先データ項目の ProcessDataDefinitionView が格納された配列
  * @param {Array<String>} dataStringList  出力するデータが格納された配列
  */
function setDataByLists( valueDefList, dataStringList ) {
  valueDefList.forEach( (valueDef, i) => {
    if ( valueDef !== null) {
      if ( i > dataStringList.length - 1 ) { // dataStringList の要素がない場合
        engine.setData( valueDef, null ); 
        return;
      }
      const dataString = dataStringList[i];

//    if ( valueDef.matchDataType("STRING") ) { // 保存先データ項目が文字型の場合
        // 保存先データ項目が改行に対応しておらず、保存する文字列に改行が含まれる場合のエラーは QBPMS のバリデーションに任せる
        engine.setData( valueDef, dataString );
//      } else if ( valueDef.matchDataType("DECIMAL") ) { // 保存先データ項目が数値型の場合
//        convertTypeAndSetData( valueDef, dataString, "DECIMAL", "Numeric" );
//      } else if ( valueDef.matchDataType("SELECT") ) { // 保存先データ項目が選択型の場合
//        convertTypeAndSetData( valueDef, dataString, "SELECT", "Select" );
//      } else if ( valueDef.matchDataType("DATE") ) { // 保存先データ項目が日付型の場合
//        convertTypeAndSetData( valueDef, dataString, "DATE", "Date" );
//      } else if ( valueDef.matchDataType("DATETIME") ) { // 保存先データ項目が日時型の場合
//        convertTypeAndSetData( valueDef, dataString, "DATETIME", "Datetime" );
//      }
      
    }
  });
}


/**
  * データ項目の型にしたがってデータを変換して出力する
  * 対応しないフィールド型の場合はエラーとする
  * 変換できない値の場合はエラーとする
  * @param {ProcessDataDefinitionView} dataDef  保存先データ項目の ProcessDataDefinitionView
  * @param {String} dataString  出力するデータ（変換前の文字列データ）
  * @param {String} dataType  保存先データ項目のデータ型
  * @param {String} dataTypeLabel  保存先データ項目のデータ型の表示名（エラー出力用）
  */
/*
  function convertTypeAndSetData( dataDef, dataString, dataType, dataTypeLabel ) {
  
    let convertedData;
    if ( dataString === "" || dataString === null ) { // 空値の場合は null を設定
      convertedData = null;
    } else {
      try { //  try-catch でエラーを捕捉
        switch (dataType) {
          case 'DECIMAL':
            convertedData = new java.math.BigDecimal( dataString );
            break;
          case 'SELECT': // 一致する選択肢 ID がない場合のエラーは QBPMS のバリデーションに任せる
            convertedData = new java.util.ArrayList();
            convertedData.add( dataString );
            break;
          case 'DATE':
            convertedData = java.sql.Date.valueOf( dataString );
            break;
          case 'DATETIME':
            const dateFormatter = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm" );
            convertedData = new java.sql.Timestamp( dateFormatter.parse( dataString ).getTime() );
          break;
        }
      } catch (e) { // 変換できない値の場合はエラー
        throw `Returned value "${dataString}" cannot be saved to ${dataTypeLabel} type data item.`;
      }
    }
    engine.setData( dataDef, convertedData );
  }
 */