Microsoft 365 Excel: Get Row
Gets data in a specified row from a sheet.
Configs for this Auto Step
- conf_OAuth2
- C1: OAuth2 Setting *
- conf_Url
- C2: Target Book URL *
- conf_Title
- C3: Target Sheet Title *#{EL}
- conf_RowNo
- C4: Row to Get (e.g. “1”, “11”) *
- conf_Range
- C5: Column Range to Get (e.g. “A:O”)(Up to 15 columns) *
- conf_Column1
- C6_1: Data item that stores the value in the 1st column
- conf_Column2
- C6_2: Data item that stores the value in the 2nd column
- conf_Column3
- C6_3: Data item that stores the value in the 3rd column
- conf_Column4
- C6_4: Data item that stores the value in the 4th column
- conf_Column5
- C6_5: Data item that stores the value in the 5th column
- conf_Column6
- C6_6: Data item that stores the value in the 6th column
- conf_Column7
- C6_7: Data item that stores the value in the 7th column
- conf_Column8
- C6_8: Data item that stores the value in the 8th column
- conf_Column9
- C6_9: Data item that stores the value in the 9th column
- conf_Column10
- C6_10: Data item that stores the value in the 10th column
- conf_Column11
- C6_11: Data item that stores the value in the 11th column
- conf_Column12
- C6_12: Data item that stores the value in the 12th column
- conf_Column13
- C6_13: Data item that stores the value in the 13th column
- conf_Column14
- C6_14: Data item that stores the value in the 14th column
- conf_Column15
- C6_15: Data item that stores the value in the 15th 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 = 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;
}
Download
- excel-row-get-202307.xml
- 2023-07-03 (C) Questetra, Inc. (MIT License)
(Installing Addon Auto-Steps are available only on the Professional edition.)
Notes
- About the settings for integration with Microsoft365 services
- How to register applications on Microsoft365 (Azure Active Directory) side
- How to Setup HTTP Authentication on Questetra side
- How to Output Files from Cloud-based Workflow Questetra to OneDrive
- “2.2: OAuth settings on the Questetra side”
- ※ Note: If the Excel Online file is on a SharePoint Online (SPO) document library, the scope to be specified is different
- Not on SPO document library → https://graph.microsoft.com/Files.ReadWrite offline_access
- On the SPO document library → https://graph.microsoft.com/Sites.ReadWrite.All offline_access
- How to Output Files from Cloud-based Workflow Questetra to OneDrive
- If there is a cell that does not contain a value but is formatted, the row containing that cell is also considered to be used and the last row is identified. (Internally useRange() is used to identify the last row)
Capture
