【Office Scripts×Power Automate】UserFormの代替で完了通知を自動化する

// 修正前（Excel Onlineの手動実行では動くが、Power Automateでは失敗しうる）
const prefSh = workbook.getActiveWorksheet();

// 修正後：シート名を明示的に指定する
const prefSh = workbook.getWorksheet("貼付元");

function main(workbook: ExcelScript.Workbook): { count: number; hasError: boolean; message: string } {

  try {
    // ★ Power Automate実行時はアクティブシートが「最後に保存した時点のシート」になるため、
    //   getActiveWorksheet()ではなくシート名を直接指定する
    const prefSh = workbook.getWorksheet("貼付元");
    const sName  = prefSh.getRange("K1").getValue() as string;
    const workSh = workbook.getWorksheet(sName);

    if (!workSh) {
      return { count: 0, hasError: true, message: `シート "${sName}" が見つかりません` };
    }

    // ▼マーク列の自動検出 = VBAの WorksheetFunction.Match("▼", ...)
    const ptCol = findValueInRow(prefSh, 1, "▼");
    const wtCol = findValueInRow(workSh, 1, "▼");

    if (ptCol === -1 || wtCol === -1) {
      return { count: 0, hasError: true, message: "▼マークが見つかりません（2行目を確認してください）" };
    }

    // マッピング列数の取得 = VBAの WorksheetFunction.Max(...)
    const pMCol = getMaxInRow(prefSh, 1);
    const wMCol = getMaxInRow(workSh, 1);

    if (pMCol !== wMCol) {
      return { count: 0, hasError: true, message: "引き当てる列の数が不整合のため中止しました" };
    }

    // データ開始行の取得 = VBAの prefSh.Range("G1") / workSh.Range("G1")
    const pRow = prefSh.getRange("G1").getValue() as number;
    let   wRow = workSh.getRange("G1").getValue() as number;

    // 列マッピング配列の構築 = VBAの pCol() / wCol()
    const pCol: number[] = [];
    let pFlgCol = 0;
    for (let i = 1; i <= pMCol; i++) {
      const col = findValueInRow(prefSh, 1, i);
      pCol.push(col);
      if (i > 1 && col !== pCol[i - 2] + 1) pFlgCol = 1;
    }

    const wCol: number[] = [];
    let wFlgCol = 0;
    for (let i = 1; i <= wMCol; i++) {
      const col = findValueInRow(workSh, 1, i);
      wCol.push(col);
      if (i > 1 && col !== wCol[i - 2] + 1) wFlgCol = 1;
    }

    // 最終行の取得 = VBAの Cells(Rows.Count, col).End(xlUp).Row
    const workShEndR = getLastRow(workSh, wtCol);
    const prefShEndR = getLastRow(prefSh, ptCol);

    // ID照合用にターゲット列を配列として一括取得
    const tgetRng = prefSh
      .getRangeByIndexes(pRow - 1, ptCol, prefShEndR - pRow + 1, 1)
      .getValues();

    // 開始行の上書き指定（N1セル）= VBAの再開始行機能
    const n1Val = prefSh.getRange("N1").getValue();
    if (typeof n1Val === "number" && n1Val > wRow) wRow = n1Val;

    let lngHcount = 0;

    // メインループ = VBAの For tgetTmpR = wRow To workShEndR
    for (let tgetTmpR = wRow; tgetTmpR <= workShEndR; tgetTmpR++) {

      const tmpStr = workSh
        .getRangeByIndexes(tgetTmpR - 1, wtCol, 1, 1)
        .getValue();

      // ID照合 = VBAの WorksheetFunction.Match(tmpStr, tgetRng, 0)
      let matchRng = -1;
      for (let r = 0; r < tgetRng.length; r++) {
        if (tgetRng[r][0] === tmpStr) {
          matchRng = r + pRow;
          break;
        }
      }

      if (matchRng === -1) continue;

      // 転記元データの取得
      const myArray: (string | number | boolean)[] = [];
      if (pFlgCol === 1) {
        for (let i = 0; i < pMCol; i++) {
          myArray.push(
            prefSh.getRangeByIndexes(matchRng - 1, pCol[i], 1, 1)
                  .getValue() as string | number | boolean
          );
        }
      } else {
        const vals = prefSh
          .getRangeByIndexes(matchRng - 1, pCol[0], 1, pMCol)
          .getValues()[0];
        myArray.push(...(vals as (string | number | boolean)[]));
      }

      // 貼付先への書き込み
      if (wFlgCol === 1) {
        for (let i = 0; i < wMCol; i++) {
          workSh.getRangeByIndexes(tgetTmpR - 1, wCol[i], 1, 1)
                 .setValue(myArray[i]);
        }
      } else {
        workSh.getRangeByIndexes(tgetTmpR - 1, wCol[0], 1, pMCol)
               .setValues([myArray]);
      }

      lngHcount++;

      if (tgetTmpR % 1000 === 0) {
        console.log(`処理中... ${tgetTmpR} / ${workShEndR} 件`);
      }
    }

    console.log(`完了！【データ更新件数： ${lngHcount} 件】`);

    // ★ Power Automateに返す結果オブジェクト
    return {
      count: lngHcount,
      hasError: false,
      message: `転記が完了しました（${lngHcount} 件）`
    };

  } catch (e) {
    // ★ エラー発生時もオブジェクトとして返す
    return {
      count: 0,
      hasError: true,
      message: `エラーが発生しました：${e}`
    };
  }
}

// ── ヘルパー関数 ──────────────────────────────────

// 行内で値を検索して列インデックスを返す（0-indexed）
// = VBAの WorksheetFunction.Match の代替
function findValueInRow(
  sheet: ExcelScript.Worksheet,
  rowIndex: number,
  value: string | number
): number {
  const colCount = sheet.getUsedRange().getColumnCount();
  const rowValues = sheet
    .getRangeByIndexes(rowIndex, 0, 1, colCount)
    .getValues()[0];
  return rowValues.indexOf(value);
}

// 行の数値の最大値を返す
// = VBAの WorksheetFunction.Max の代替
function getMaxInRow(sheet: ExcelScript.Worksheet, rowIndex: number): number {
  const colCount = sheet.getUsedRange().getColumnCount();
  const rowValues = sheet
    .getRangeByIndexes(rowIndex, 0, 1, colCount)
    .getValues()[0];
  const nums = rowValues.filter(v => typeof v === "number") as number[];
  return nums.length > 0 ? Math.max(...nums) : 0;
}

// 最終行を返す（1-indexed）
// = VBAの Cells(Rows.Count, col).End(xlUp).Row の代替
function getLastRow(sheet: ExcelScript.Worksheet, colIndex: number): number {
  const rowCount = sheet.getUsedRange().getRowCount();
  const colValues = sheet
    .getRangeByIndexes(0, colIndex, rowCount, 1)
    .getValues();
  for (let r = rowCount - 1; r >= 0; r--) {
    if (colValues[r][0] !== "" && colValues[r][0] !== null) return r + 1;
  }
  return 1;
}