Microsoft 365 Excel: Get Row
Obtains data from the specified row of an Excel sheet as String-type data. The acquisition range can be specified up to 10 columns.
Configs
  • C1: OAuth2 Setting *
  • C2: Target Book URL *
  • C3: Target Sheet Title *#{EL}
  • C4: Row to Get (e.g. “1”, “11”) *
  • C5: Column Range to Get (e.g. “A:J”)(Up to 10 columns) *
  • C6_1: Data item that stores the value in the 1st column
  • C6_2: Data item that stores the value in the 2nd column
  • C6_3: Data item that stores the value in the 3rd column
  • C6_4: Data item that stores the value in the 4th column
  • C6_5: Data item that stores the value in the 5th column
  • C6_6: Data item that stores the value in the 6th column
  • C6_7: Data item that stores the value in the 7th column
  • C6_8: Data item that stores the value in the 8th column
  • C6_9: Data item that stores the value in the 9th column
  • C6_10: Data item that stores the value in the 10th column
Script (click to open)

// 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 = 10;

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 );

// Access to the API 2nd(GET)
const dataStringList = getData( bookInfo.driveId, bookInfo.fileId, sheetName, 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}`;
}

/**
* 指定シートの指定セルのデータを取得する
* @param {String,String} driveId, bookId 参照先ファイルのドライブ ID、ファイル ID
* @param {String} sheetName 参照先シートの名前
* @param {String} rowNo 取得する行
* @param {String} range 取得する列範囲
* @param {String} oauth2 OAuth2 設定
*/
function getData( driveId, bookId, sheetName, rowNo, range, oauth2 ){

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

const getUri = `${GRAPH_URI}${driveId}/items/${bookId}/workbook/worksheets/${sheetName}/range(address='${sheetName}!${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;
}

Download

2022-06-20 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/bpmn-icons/excel-row-get/
The Addon-import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

Capture

See also

%d bloggers like this: