TypeScriptで始めるExcel自動化のススメ

本記事は【Advent Calendar 2024】 24日目②の記事です。
🌟🎄 24日目① ▶▶ 本記事 ▶▶ 25日目 🎅🎁

はじめに
Officeスクリプトとは
マクロとの違い
- Officeスクリプトのメリット
- Officeスクリプトのデメリット
実際に使ってみた
おわりに

はじめに

お久しぶりです。川畑です。クリスマスイブのみ登場する人として３回目の登場となります。私はイブにしか書きませんと駄々をこね続けたことが功を奏して、この地位が盤石のものとなりつつあります。何故そんなことをしているのかは自分でもわかりません。

さて、クリスマスイブと言えばやっぱりツリーです。何歳になってもこれを見ると心が躍りますよね。クリスマスツリーは季節限定となってしまいますが、これを見ると年中クリスマスツリーを見た気分になれる！というものが皆さんの身近にもあります。

そうです、Microsoft Excel（以下Excel）です。これさえ開ければクリスマスツリー気分を味わえる！お得な製品ですね。持っていき方が強引だという意見は聞こえないふりをします。ということで、今回はそんなExcelの機能の一つであるOfficeスクリプトについて紹介していこうと思います。

Officeスクリプトとは

まずOfficeスクリプトはMicrosoft 365で使える機能です。Excel 2016の場合は使えないのでご了承ください。ではOfficeスクリプトとは何ぞや？ということから見ていきましょう。下記公式ドキュメントの紹介文となります。

Excel の Office スクリプトを使用すると、日常のタスクを自動化できます。アクションレコーダーを使用して、手動の手順を再利用可能なスクリプトに変換します。これらのスクリプトを編集するか、コードエディターを使用して新しいスクリプトを作成します。ブック内の他のユーザーがこれらのスクリプトを 1 つのボタンで実行できるようにします。次に、全員がワークフローを改善できるように、同僚と共有します。

引用元： https://learn.microsoft.com/ja-jp/office/dev/scripts/overview/excel

超ざっくり要約すると下記3点が実現できる機能になります。

Excelブックに対してTypeScriptベースのスクリプトを実行できる
ソースコードを書かなくても、実際にExcelブックに対して実行した動作をレコーディングしてソースコードに変換してくれる
作ったコードは他の人と共有できる

マクロとの違い

Excel自動化というとマクロを思い浮かべる人が大多数かと思います。ではマクロと比較した際のメリット・デメリットは何があるのでしょう。主観とはなりますが、それぞれ考えてみました。

Officeスクリプトのメリット

同じスクリプトを複数ブックに実行できる。

マクロと異なり、ブック自体に紐づくものではないので、汎用的なスクリプトを作成すればどんなブックに対しても利用できます。

他の人とのスクリプトの共有ができる

作成したスクリプトはOneDriveなどで他の人と共有できます。（ソースコードのファイルを送ればOneDriveでなくとも共有可能）

TypeScriptベースのためスクリプトの作成にとっかかりやすい

超主観です。マクロと違い、TypeScriptベースなのでjavascriptをかじっている人などは取っつきやすい印象です。

Officeスクリプトのデメリット

実行トリガーはスクリプト実行のみ

マクロと違って、ボタンを準備して押したタイミングで処理が実行されるといった視覚的な動きはできません。私が知らないだけで出来るのであれば申し訳ありません。

比較的新しい機能なので参考文献が少ない

こういう処理を作りたいと思っても参考に出来る記事が少ない印象を受けます。結構手あたり次第にチャレンジする必要があるかもしれません。

実際に使ってみた

さて、私には珍しく長々説明を記載しましたが、文章で書かれてもよく分からん！となると思いますので、実際にスクリプトを作ってみた例を２つ挙げます。

Officeスクリプトの新規作成方法

Officeスクリプトは「自動化」タブを選択し、「新しいスクリプト」を押下するとエクセル上でコードエディタが開くので、そこにコードを記載して実行ボタンを押下することで実行できます。

利用例①）シート量産スクリプト

Excelユーザ全員が自動でやりたいと考えている操作、シートの量産をスクリプト化してみました。 SE風にいうと、テストエビデンスをテスト項番ごとにシート作って貼っていくような利用シーンですね。001,002,003...と手作業でシートを作成することの虚無感たるやこの上なしです。

スクリプトは下記です。 ※突貫で作ったものなので書き方がよくないなどはご了承ください。

function main(workbook: ExcelScript.Workbook) {
    // 現在開いているシートを参照する
    let selectedSheet = workbook.getActiveWorksheet();

    var i = 1;
    while(true) {
        // ① B1セルから順に作成したいシート名を取得する
        var sheetName = selectedSheet.getRange("B" + i).getValue();
        var str: String = String(sheetName);
        if(str === "") {
            // 空のセルに到達したら処理を終了する
            break;
        }
        // ② ①で取得したシート名のシートを追加する
        workbook.addWorksheet(String(sheetName));
        i++;
    }
}

左図のようなシートでスクリプトを実行するとB1セルに入れた値から順にシートが作成されます。

利用例②）全シートに決まった条件付き書式をつける

テーブル定義書のように同一フォーマットのシートが大量にあるブックを調査する時にあると便利かなと思った利用例です。下図のH列に要・不要を入力していくというタスクがあった場合に、H列の記入値によって、No.1の行のように行全体に色がついていると分かりやすいですよね。１シートであれば手作業で条件付き書式を設定すればよいですが、これが50シート、100シートとあると地獄です。

そんな条件付き書式を全シートに設定するには下記のようなコードを実行します。こちらも細かい書き方等はご容赦ください。

function main(workbook: ExcelScript.Workbook) {
    let ConditionalFormat: ExcelScript.ConditionalFormat;

    var workSheets: ExcelScript.Worksheet[] = workbook.getWorksheets();
    // 全てのワークシートを対象にする
    for (var workSheet of workSheets) {
        // 条件付き書式を付与する。getRange()で対象範囲のセルを指定
        ConditionalFormat = workSheet.getRange("A1:H200").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
        // 条件として設定したい式を記載する
        ConditionalFormat.getCustom().getRule().setFormula("=$H1=\"要\"");
        // 条件付き書式の設定（背景色）
        ConditionalFormat.getCustom().getFormat().getFill().setColor("#fff2cc");
        // 条件付き書式の設定（文字色）
        ConditionalFormat.getCustom().getFormat().getFont().setColor("#c00000");
        ConditionalFormat.setStopIfTrue(false);
        ConditionalFormat.setPriority(0);
    }
}