Microsoft 365 Excel: 行取得

// OAuth2 config sample at [OAuth 2.0 Setting]
// - Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
// - Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
// - Scope: https://graph.microsoft.com/Files.ReadWrite.All offline_access
// - Consumer Key: (Get by Microsoft Azure Active Directory)
// - Consumer Secret: (Get by Microsoft Azure Active Directory)

const GRAPH_URI = "https://graph.microsoft.com/v1.0/";

const COLUMN_NUM = 15;

main();
function main(){
  //// == Config Retrieving / 工程コンフィグの参照 ==
  const oauth2 = configs.get( "conf_OAuth2" ) + "";
  const bookUrl = retrieveBookUrl();
  const sheetName = configs.get( "conf_Title" ) + "";
  if(sheetName === "" || sheetName === null){
    throw "Sheet Title is empty.";
  }

  const rowNo = retrieveRowNo();
  const range = configs.get( "conf_Range" );

  checkRowNoAndRange( rowNo, range );
  const columnDefList = [];
  retrieveValueConfigs( columnDefList );

  //// == Calculating / 演算 ==
  // Access to the API 1st(Get Book Info)
  const bookInfo = getFileInfoByUrl( bookUrl, oauth2 );
  const worksheetId = getWorksheetId(bookInfo, sheetName, oauth2);

  // Access to the API 2nd(GET)
  const dataStringList = getData( bookInfo, worksheetId, rowNo, range, oauth2);

  //// == ワークフローデータへの代入 / 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.";
  }
}

/**
  * config からブックの URL を読み出す、空ならエラー
  * @return {String} ブックの URL
  */
function retrieveBookUrl() {
  const bookUrlDef = configs.getObject( "conf_Url" );
  let bookUrl;
  if ( bookUrlDef === null ) {
    bookUrl = configs.get( "conf_Url" )
  }else{
    bookUrl = engine.findData( bookUrlDef );
  }
  if ( bookUrl === "" || bookUrl === null){
    throw "Book URL is empty."
  }
  return bookUrl;
}

/**
  * フォルダの URL からファイル情報（ドライブ ID とファイル ID）を取得し、
  * オブジェクトで返す（URL が空の場合はエラーとする）
  * @param {String} fileUrl  フォルダの URL
  * @param {String} oauth2  OAuth2 設定
  * @return {Object} fileInfo  ファイル情報 {driveId, fileId}
  */
function getFileInfoByUrl( fileUrl, oauth2 ) {
  let fileInfo;
  if ( fileUrl !== "" && fileUrl !== null ) {
    // 分割代入
    const {
      id,
      parentReference: {
        driveId
      }
    } = getObjBySharingUrl( fileUrl, oauth2 );
    fileInfo = {driveId: `drives/${driveId}`, fileId: id};
  }
  return fileInfo;
}

/**
  * ドライブアイテム（ファイル、フォルダ）のメタデータを取得し、JSON オブジェクトを返す
  * API の仕様：https://docs.microsoft.com/ja-jp/onedrive/developer/rest-api/api/shares_get?view=odsp-graph-online
  * @param {String} sharingUrl  ファイルの共有 URL
  * @param {String} oauth2  OAuth2 設定
  * @return {Object} responseObj  ドライブアイテムのメタデータの JSON オブジェクト
  */
function getObjBySharingUrl( sharingUrl, oauth2 ) {
  if (sharingUrl === "" || sharingUrl === null) {
    throw `Sharing URL is empty.`;
  }

  // encoding sharing URL
  const encodedSharingUrl = encodeSharingUrl(sharingUrl);

  // API Request
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .get( `${GRAPH_URI}shares/${encodedSharingUrl}/driveItem` );

  const responseStr = logAndJudgeError(response, "GET");

  return JSON.parse( responseStr );
}

/**
  * 共有URLをunpadded base64url 形式にエンコードする
  * @param {String} sharingUrl  共有 URL
  * @return {String} encodedSharingUrl  エンコードされた共有 URL
  */
function encodeSharingUrl( sharingUrl ) {
  let encodedSharingUrl = base64.encodeToUrlSafeString( sharingUrl );
  while ( encodedSharingUrl.slice(-1) === '=' ) {
    encodedSharingUrl = encodedSharingUrl.slice(0,-1);
  }
  return `u!${encodedSharingUrl}`;
}

/**
  * ワークシートの ID を取得する
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  ワークブックのドライブ ID
  * @param {String} bookInfo.fileId  ワークブックのファイル ID
  * @param {String} sheetName  シートの名前
  * @param {String} oauth2  OAuth2 設定
  */
function getWorksheetId({driveId, fileId}, sheetName, oauth2) {
    const getWorksheetsUrl = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets`;
    const response = httpClient.begin()
        .authSetting(oauth2)
        .get(getWorksheetsUrl);

    const responseStr = logAndJudgeError(response, "2nd GET");
    const jsonRes = JSON.parse(responseStr);
    const worksheet = jsonRes.value.find(worksheet => worksheet.name === sheetName);
    if (worksheet === undefined) {
        throw 'Worksheet not found.';
    }
    return worksheet.id;
}

/**
  * 指定シートの指定セルのデータを取得する
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  ワークブックのドライブ ID
  * @param {String} bookInfo.fileId  ワークブックのファイル ID
  * @param {String} worksheetId  シートの ID
  * @param {String} rowNo 取得する行
  * @param {String} range 取得する列範囲
  * @param {String} oauth2  OAuth2 設定
  */
function getData( {driveId, fileId}, worksheetId, rowNo, range, oauth2 ){

  const rangeArr = range.split(':');

  const getUri = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets/${encodeURIComponent(worksheetId)}/range(address='${rangeArr[0]}${rowNo}:${rangeArr[1]}${rowNo}')/`;

  const response = httpClient.begin()
    .authSetting( oauth2 )
    .get( getUri );

  const responseStr = logAndJudgeError(response, "GET");

  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 );
  }
 */

/**
  * ログの出力と、エラー発生時のスローを行う
  * @param {HttpResponseWrapper} response  リクエストの応答
  * @param {String} requestType リクエストをどの形式で行ったか("GET" or "POST" or "PATCH")
  * @return {String} responseStr レスポンスの文字列
  */
function logAndJudgeError(response, requestType){
  const responseStr = response.getResponseAsString();
  const status = response.getStatusCode();
  if(status >= 300){
    const accessLog = `---${requestType} request--- ${status}\n${responseStr}\n`;
    engine.log(accessLog);
    throw `Failed in ${requestType} request. status: ${status}`;
  }
  return responseStr;
}