Microsoft 365 Excel: Find Row Number By Specified Value
Microsoft 365 Excel: 指定値で行番号を特定
Gets row number search by specified value in 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_TargetColumn
- C4: Target Column(e.g. “A”) *#{EL}
- conf_StartRow
- C5: Target Start Row(e.g. “1”) *#{EL}
- conf_TargetData
- C6: String type data item for search value *
- conf_RowNumber
- C7: String type data item for row number(upate) *
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/";
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 targetColumn = configs.get( "conf_TargetColumn" ) + "";
const startRow = configs.get( "conf_StartRow" ) + "";
const targetData = engine.findData(configs.getObject( "conf_TargetData" )) + "";
const rowNumberDataDef = configs.get( "conf_RowNumber" ) + "";
//// == 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 rowNumber = getRowNumber( bookInfo, worksheetId, targetColumn, startRow, targetData, oauth2);
//// == ワークフローデータへの代入 / Data Updating ==
engine.setDataByNumber( rowNumberDataDef, rowNumber + "");
}
/**
* 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} targetColumn 検索対象列
* @param {String} startRow 検索対象列の開始行
* @param {String} targetData 検索する値
* @param {String} oauth2 OAuth2 設定
* @return {Number} rowNumber 特定した行番号
*/
function getRowNumber( {driveId, fileId}, worksheetId, targetColumn, startRow, targetData, oauth2 ){
const getUri = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets/${encodeURIComponent(worksheetId)}/usedRange/`;
const response = httpClient.begin()
.authSetting( oauth2 )
.get( getUri );
const responseStr = logAndJudgeError(response, "GET");
const jsonObj = JSON.parse( responseStr );
//取得できたデータの範囲
// Index は0始まり、Count は列や行数
// 例えば columnIndex が2で、columnCount が3なら、C列始まりでE列まで値あり
const columnIndex = jsonObj.columnIndex;
const rowIndex = jsonObj.rowIndex;
const columnCount = jsonObj.columnCount;
const rowCount = jsonObj.rowCount;
engine.log("columnIndex:" + columnIndex);
engine.log("rowIndex:" + rowIndex);
engine.log("columnCount:" + columnCount);
engine.log("rowCount:" + rowCount);
//列を指定するアルファベットを数値変換(Aなら1、Bなら2)
const targetColumnNumber = convertA1toR1C1(targetColumn);
engine.log("targetColumnNumber:" + targetColumnNumber);
//検索対象列・開始行が範囲内かチェック
if ((targetColumnNumber < columnIndex + 1) || (columnIndex + columnCount < targetColumnNumber)) {
return -1;
} else if ((startRow < rowIndex + 1) || (rowIndex + rowCount < startRow)) {
return -1;
}
//指定列・開始行が取得配列のどこに当たるか相対位置を計算
const targetColumnIndex = targetColumnNumber - (columnIndex + 1)
const targetRowIndex = startRow - (rowIndex + 1)
engine.log("targetColumnIndex:" + targetColumnIndex);
engine.log("targetRowIndex:" + targetRowIndex);
for (let i = targetRowIndex; i < jsonObj.values.length; i++) {
let data = jsonObj.values[i][targetColumnIndex] + "";
if (targetData == data) {
return i + rowIndex + 1; //補正して行番号を返す
}
}
return -1; //該当行なしの場合
}
/**
* 列を指定するアルファベットを数値変換する(例:A→1、C→3)
* @param {String} columnString 列を指定するアルファベット
* @return {Number} num アルファベットに対応した数値
*/
function convertA1toR1C1(columnString) {
const RADIX = 26;
const A = 'A'.charCodeAt(0);
let str = columnString.toUpperCase();
let num = 0;
let strLength = str.length;
for (let i = 0; i < strLength; i++) {
num = (num * RADIX) + (str.charCodeAt(i) - A + 1);
}
return num;
}
/**
* ログの出力と、エラー発生時のスローを行う
* @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-find-row-202307.xml
- 2023-07-03 (C) Questetra, Inc. (MIT License)
(Installing Addon Auto-Steps are available only on the Professional edition.)
Notes
- Returns -1 if there are no applicable rows.
- About the settings for integration with Microsoft365 services
- How to register applications on the Microsoft365 (Azure Active Directory) side
- How to Setup HTTP Authentication on the 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 that should 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 the sheet name contains parentheses or other symbols, an error may occur. If an error occurs, consider changing the sheet name.
Capture
