Microsoft 365 Excel: Find Row Number By Specified Value

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