ワークシートを 1 つのブックに結合する

このサンプルでは、複数のブックから 1 つの一元化されたブックにデータをプルする方法を示します。 2 つのスクリプトを使用します。1 つはブックから情報を取得し、もう 1 つは、その情報を含む新しいワークシートを作成します。 OneDrive フォルダー全体に対して動作する Power Automate フロー内のスクリプトが組み合わされています。

重要

このサンプルでは、他のブックの値のみをコピーします。 書式設定、グラフ、テーブル、またはその他のオブジェクトは保持されません。

ソリューション

1. OneDrive に新しい Excel ファイルを作成します。 このサンプルでは、ファイル名 "Combination.xlsx" を使用します。
2. このサンプルの 2 つのスクリプトを作成して保存します。
3. OneDrive にフォルダーを作成し、データを含む 1 つ以上のブックを追加します。 このサンプルでは、フォルダー名 "output" を使用します。
4. フローを構築して (この記事の 「Power Automate フロー 」セクションで説明されているように)、次の手順を実行します。
   1. "出力" フォルダーのすべてのファイルを一覧表示します。
   2. 各ブック内のすべてのワークシートからデータを取得するには、ワークシートのデータを 返す スクリプトを使用します。
   3. [ワークシートの追加] スクリプトを使用して、他のすべてのファイル内のすべてのワークシートの "Combination.xlsx" ブックに新しいワークシートを作成します。

サンプル コード: ワークシート データを返す

/**
 * This script returns the values from the used ranges on each worksheet.
 */
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  // Create an object to return the data from each worksheet.
  let worksheetInformation: WorksheetData[] = [];

  // Get the data from every worksheet, one at a time.
  workbook.getWorksheets().forEach((sheet) => {
    let values = sheet.getUsedRange()?.getValues();
    worksheetInformation.push({
       name: sheet.getName(),
       data: values as string[][]
    });
  });

  return worksheetInformation;
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

サンプル コード: ワークシートを追加する

/**
 * This script creates a new worksheet in the current workbook for each WorksheetData object provided.
 */
function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
    let sheet = workbook.addWorksheet(`${workbookName}.${value.name}`);

    // If there was any data in the worksheet, add it to a new range.
    if (value.data) {
      let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
      range.setValues(value.data);
    }
  });
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Power Automate フロー: ワークシートを 1 つのブックに結合する

1. Power Automate にサインインし、新しいインスタント クラウド フローを作成します。

2. [ フローを手動でトリガーする ] を選択し、[ 作成] を選択します。

3. 結合するすべてのブックをフォルダーから取得します。 アクションを追加し、 OneDrive for Business コネクタの [フォルダー内のファイルの一覧表示] アクションを選択します 。 [ フォルダー] フィールドで、ファイル ピッカーを使用して "出力" フォルダーを選択します。

   

4. 各ブックからすべてのデータを取得する Return ワークシート データ スクリプトを実行するアクションを追加します。 Excel Online (Business) コネクタの [スクリプトの実行] アクションを選択します。 アクションには次の値を使用します。 ファイルの ID を 追加すると、Power Automate によってアクションが For each コントロールにラップされるため、アクションはすべてのファイルに対して実行されることに注意してください。

   • 場所: OneDrive for Business
   • ドキュメント ライブラリ: OneDrive
   • ファイル: ID ( フォルダー内のファイルの一覧からの動的コンテンツ)
   • スクリプト: ワークシート データを返す

   

5. 作成した新しい Excel ファイルで ワークシートの追加 スクリプトを実行するアクションを追加します。 これにより、他のすべてのブックのデータが追加されます。 前の [スクリプトの実行 ] アクションと [ For each ] コントロールの後に、 Excel Online (Business) コネクタの [スクリプトの実行 ] アクションを使用するアクションを追加します。 アクションには次の値を使用します。

   • 場所: OneDrive for Business
   • ドキュメント ライブラリ: OneDrive
   • ファイル: "Combination.xlsx" (ファイル ピッカーによって選択されたファイル)
   • スクリプト: ワークシートを追加する
   • workbookName: 名前 ( フォルダー内のファイルの一覧からの動的コンテンツ)
   • worksheetInformation (次の画像のメモを参照): result ( Run script からの動的コンテンツ)

   

   注:

   [ 配列全体を入力に切り替える ] ボタンを選択して、配列の個々の項目ではなく、配列オブジェクトを直接追加します。 結果を入力する前に、これを行 います。

   

6. フローを保存します。 フロー デザイナーは次の図のようになります。

   

7. フロー エディター ページの [テスト ] ボタンを使用するか、[ マイ フロー ] タブでフローを実行します。プロンプトが表示されたら、必ずアクセスを許可してください。

8. "Combination.xlsx" ファイルに新しいワークシートが追加されました。

トラブルシューティング

• 同じ名前または識別子を持つリソースが既に存在する: このエラーは、"Combination.xlsx" ブックに同じ名前のワークシートが既に存在することを示している可能性があります。 これは、同じブックでフローを複数回実行する場合に発生します。 新しいブックを毎回作成して、結合されたデータを格納するか、異なるファイル名を "出力" フォルダーに使用します。

• 引数が無効または欠落しているか、形式が正しくない: このエラーは、生成されたワークシート名が Excel の要件を満たしていないことを意味する可能性があります。 これは、名前が長すぎるためである可能性があります。 ワークシート名が 30 文字を超える場合は、 を呼び出す addWorksheet "ワークシートの追加" のコードを文字列を短くするものに置き換えます。 ブック名自体が長すぎる可能性があるため、ワークシート名の末尾にインクリメント番号を追加します。 ループの外側でこの数値を宣言します forEach 。

  let worksheetNumber = 1;
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
      let worksheetName = `${workbookName}.${value.name}`;
      let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);
  

  さらに、ブック名が 30 文字を超える場合は、フローでそれらを短縮する必要があります。 まず、ブックの数を追跡するために、フローに変数を作成する必要があります。 これにより、同じ短縮名がスクリプトに渡されるのを回避できます。 フローの前に変数の初期化アクション (型 "Integer") と、2 つの [スクリプトの実行] アクションの間に [変数の増分] アクションを追加します。 次に、"スクリプト 1 の実行" で workbookName として Name を使用する代わりに、変数の式substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20))と動的コンテンツを使用します。 これにより、ブック名が 20 文字に短縮され、スクリプトに渡される文字列に現在のブック番号が追加されます。

  

  

  注:

  フローとスクリプトを複雑にするのではなく、ファイル名とワークシート名が十分に短いことが保証される方が簡単な場合があります。