Officeスクリプト フォルダ内の複数ファイルからデータをコピーする

Officeスクリプトには別のブック・別のファイルを開いて直接データをコピーする機能はありません（2024年時点）

別ブックのデータを活用したい場合はPowerAutomateを利用するか、手動でデータをコピーするしかありません。

ドノ

ここではOfficeスクリプトとPowerAutomateを組み合わせた方法を紹介します。

それぞれの役割分担は下記の通りです。

• Officeスクリプト：データの取得・書き込み
• PowerAutomate：複数ファイルへのアクセス

概要説明（複数ファイルからのコピー）

コピーまず こちらのページで解説する処理内容について説明します。

説明例として下図のような処理が実現できるようなOfficeスクリプトとPowerAutomateフローを作成します。

具体的にはフォルダ内に格納された3つファイル（売上2024年4月.xlsx、売上2024年5月.xlsx、売上2024年6月.xlsx）からデータをコピーして1つのファイル（売上データ.xlsx）にデータを集約します。

それぞれのファイルからヘッダーを除いてデータをコピーして 集約ファイルのデータ最終行に新規データを追加していきます。これによりデータを1つのファイルにまとめます。

Officeスクリプトの作成

まずは別ブックを開いたときにデータをコピーするための2つのスクリプトを作成します。

ドノ

シート名・セル範囲・変数名などは例の値が入っているので変更して活用してください。

コピー元からデータを取得するスクリプト

コピー元からデータを取得するスクリプトの例を紹介します。

スクリプトの最初で戻り値のデータの型string[][]を宣言して、スクリプトの最後でreturnで値を返すように記載することでPowerAutomateで取得したデータを利用できるようになります。

範囲指定したデータ取得

ワークシート「sheet1」のセル範囲A2:D4のデータを取得するスクリプト例が下記です。

function main(workbook: ExcelScript.Workbook): string[][] {

    // コピー元のシートを取得
    let sheet = workbook.getWorksheet("Sheet1");  // シート名は任意に設定

    // コピーする範囲を取得
    let range = sheet.getRange("A2:D4");         // 範囲は任意に設定

    // 範囲の値を取得
    let values = range.getValues();

    // データを返す
    return values;
}

指定行から最終行までのデータ取得（指定列から最終列）

ワークシート「sheet1」の指定セルから最終列・最終行までの範囲を取得するスクリプト例はこちらです。

function main(workbook: ExcelScript.Workbook): string[][] {

    // コピー元のシートを取得
    let sheet = workbook.getWorksheet("Sheet1");  // シート名は任意に設定

    // 開始行・開始列を設定
    let startRow = 2;                             // 行を任意に設定
    let startCol = 1;                             // 列を任意に設定

    // 最終行・最終列を取得
    let usedRange = sheet.getUsedRange();      //シートの使用範囲を取得
    let lastRow = usedRange.getRowCount();     //最終行を取得
    let lastCol = usedRange.getColumnCount();  //最終列を取得

    // 指定した行から最終行までの範囲を取得
    let range = sheet.getRangeByIndexes(startRow - 1, startCol - 1, lastRow - (startRow - 1), lastCol - (startCol - 1));

    // 範囲の値を取得
    let values = range.getValues();

    // データを返す
    return values;
}

コピー先にデータを書き込むスクリプト

コピー先へデータを書き込むスクリプトの例を紹介します。

スクリプトの最初のdata: string[][]で使用するデータを宣言することでPowerAutomateで利用するデータを設定できるようになります。

最終行の下にデータを書き込むスクリプト

ワークシートの既存データの下側にデータを書き込むスクリプト例です。

データ書き込みのための最終行を取得する列（先頭列）を設定が必要です。例ではA列としています。

function main(workbook: ExcelScript.Workbook, data: string[][]): void {

    // コピー先のシートを取得
    let sheet = workbook.getWorksheet("Sheet1");  // シート名は任意に設定

    // データの先頭列を設定（最終行の取得列）
    let startCol = 1;                             // 任意の列番号を設定
    let startColIndex = startCol - 1;

    // 指定列の使用されている範囲を取得
    let columnRange = sheet.getRangeByIndexes(0, startColIndex, 1048576, 1).getUsedRange();

    // 指定範囲の最終行を取得(デフォルト値 -1) 
    let lastRowIndex = - 1;
    if (columnRange) {
        lastRowIndex = columnRange.getLastCell().getRowIndex();
    }

    // 書き込む範囲を設定
    let range = sheet.getRangeByIndexes(lastRowIndex + 1, startColIndex, data.length, data[0].length);

    // データを書き込む
    range.setValues(data);
}

Sponsored link

PowerAutomateフロー作成

次に作成したスクリプトを他の複数ファイルで実行するPowerAutomateフローを作成します。

注意点として、ファイルはOneDriveまたはSharepointに保存されている必要があります。

フロー作成手順

それでは説明例として下図のように同じOneDrive上の「データ」フォルダにある3つファイル「売上2024年4月.xlsx」「売上2024年5月.xlsx」「売上2024年6月.xlsx」からデータをコピーして1つのファイル「売上データ.xlsx」にまとめます。

各ファイルのコピーするシートは「Sheet1」です。フローのトリガーは手動トリガーとして作成します。

STEP

PowerAutomateクラウドフロー作成

まずはPowerAutomateフローを作成します。

PowerAutomateのページで 「マイフロー」->「新しいフロー」->「インスタントクラウドフロー」を選択します。

定期実行したい場合は「スケジュール済みのクラウドフロー」を選択してください

「フローを手動でトリガーする」を選択して「作成」をクリック。フロー名は任意で設定してください。

STEP

フォルダ内のファイル情報を取得

次にフォルダ内にあるファイル情報を取得するアクションを追加します。

フロー下部の＋マークをクリックして「OneDrive for Business」->「フォルダー内のファイルのリスト」を選択する。

パラメータの設定で対象のフォルダを選択します。

STEP

それぞれにファイルに処理を適用するアクションを追加

フロー下部の＋マークをクリックして「Control」->「それぞれに適用する」を選択する。

パラメータの設定でひとつ前のアクションで取得した「body/value」を選択します。

動的コンテンツの利用方法

動的コンテンツ（フロー内で取得したデータ）を利用する場合は項目欄を選択して、表示された下図のマークをクリックすればOKです。取得しているデータの一覧が表示されます。

STEP

複数ファイルを開いてスクリプトを実行するアクション設定

次にファイルからデータを取得するアクションを追加します。

「それぞれに適用する」の中の＋マークをクリックして「Excel Online (Business)」->「スクリプトの実行」を選択する。

パラメータの設定でフォルダの場所と実行するスクリプトを選択します。スクリプト「データ取得」には前述の「コピー元からデータを取得するスクリプト」に記載のスクリプトが書かれています。

ファイルにおいては「格納フォルダ名」と「前のステップで取得したファイル名（動的コンテンツ）」を組み合わせてファイルを指定しています。

Teamsやsharepointに保存してあるファイルも選択可能です

STEP

集約先のブックでスクリプトを実行するアクション設定

次にコピー先にデータを書き込むアクションを追加します。

上記と同様に「それぞれに適用する」の中の＋マークをクリックして「Excel Online (Business)」->「スクリプトの実行」を選択する。

パラメータの設定でデータ集約ファイル「売上データ.xlsx」とスクリプトを選択します。スクリプト「データ書き込み」には前述の「コピー先にデータを書き込むスクリプト」に記載のスクリプトが書かれています。

引数（ScriptParameters）には「データ取得」のスクリプトで取得したデータ「body/result」を選択します。

STEP

PowerAutomateフロー完成

これでフォルダ内の複数ファイルからデータをまとめるフローは完成です。

フロー実行

上記で作成したフローは手動トリガーとしたので PowerAutomateページの「マイフロー」にある対象フローの実行ボタンをクリックすればフローを開始できます。

少し待つと下図のようにフローが問題なく完了できているのがわかります。

「売上データ.xlsx」を開くと、下図のようにデータが集約されているのがわかります。

ドノ

スケジュール済みのクラウドフローで定期実行の設定をした場合はテスト機能などを使い確認してみてください

Sponsored link

SharePoint/Teamsへの対応

One Drievと同様にSharePointのフォルダ内にある複数ファイルに対しても適用は可能です。

その場合は下記のようにPowerAutomateのフローを変更して使用してください。そのほかは同様の設定で大丈夫です。

「フォルダー内のファイルのリスト」を SharePointの「フォルダの一覧」に変更する。項目欄は下記を設定する。

• サイトのアドレス：SharePoint（Teams）のURLを設定
• ファイル識別子：対象のフォルダを選択

Sponsored link