Office スクリプトで Excel 表の合計値を算出するボタンを入れてみた

Guten Morgen、ベルリンの伊藤です。

表題の通り、本日は Office スクリプトのお話です。

以前に Office スクリプトを試してみて、GASとの違いなどをご紹介しました。

そちらでも記載しましたが、やはりGASの方が圧倒的にできることが幅広く、またGASを使ったことがある身としては関数やインターフェイスに慣れず、なかなか不便さを感じました。

それでも、社内の共有ファイルには Sharepoint の方が利用頻度が高いので、簡単な自動化処理なら既存の Excel から Google スプレッドシートに移すのではなく Office スクリプトを使うことにしました。

やったこと

Sharepoint にある共有 Excel ファイルで、社員たちがとある作業の時間を課題ごとに記録していて、不定期で合計作業時間を算出して給与計算に反映させています。

算出には、Total 行に作業時間の合計、その下の Payroll 行に合計時間 x 30 の給与額を算出するだけ。ですが、ただ上段からコピペするだけでなく、Total の =SUM で範囲を指定し直すのが地味に面倒でした。

今回はスクリプトを使って、この作業をボタンをクリックするだけでできるようにしました。

※ 計算データは実業務から少し変えてあります。

※ リージョンがドイツのため、数字の表記でカンマとピリオドが逆です。（eg: 1,234.56→1.234,56）

流れ

1. 自動化したい一通りの作業をマクロ記録でスクリプトに起こす
2. できたスクリプトを修正していく
3. スクリプトのボタンを作る

ボタンはスクリプトを用意してからでないと追加ができないので最後のステップです。

マクロでスクリプト起こし

Automate タブ > Record Actions をクリックしたら、次の作業を行いました

1. 表の一番下のA列（A23）に "Total Hours" と入力、その右隣（B23）に =SUM で表の "Ito" の合計値を算出
2. そのセルをコピーし、同じ行の右方向のセル（C23:G23）に貼り付け
3. 下の行（A24）に ="Payroll in "&TEXT(TODAY(); "MMM JJJJ") と入力（※）
4. そのセルをコピーし、同じセルへ値のみ貼り付け
5. その右隣のセル（B24）に =B23*30 と入力して金額を算出
6. そのセルをコピーし、同じ行の右方向のセル（C24:G24）に貼り付け
7. 下段の数字の範囲（B24:G24）の数字フォーマットを€通貨に指定
8. 入力した全範囲（A23:G24）の背景色を設定
9. 表のヘッダ（A17:G17）をコピーし、新しい行（26）に貼り付け

※ リージョンがドイツのため、関数内のカンマにはセミコロン、年を表すYYYYの代わりにJJJJを使い、セルの出力結果がドイツ語の10月を短縮した Okt となっています。

そして作成されたスクリプトがこちらです。（分かりやすいよう 1. などの採番のみ追記しています）

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// 1. Set range A23:B23 on selectedSheet
selectedSheet.getRange("A23:B23").setFormulasLocal([["Total Hours","=SUM(B18:B22)"]]);
// 2. Paste to range C23:G23 on selectedSheet from range B23 on selectedSheet
selectedSheet.getRange("C23:G23").copyFrom(selectedSheet.getRange("B23"), ExcelScript.RangeCopyType.all, false, false);
// 3. Set range A24 on selectedSheet
selectedSheet.getRange("A24").setFormulaLocal("=\"Payroll in \"&TEXT(TODAY(); \"MMM JJJJ\")");
// 4. Paste to range A24 on selectedSheet from range A27 on selectedSheet
selectedSheet.getRange("A24").copyFrom(selectedSheet.getRange("A24"), ExcelScript.RangeCopyType.values, false, false);
// 5. Set range B24 on selectedSheet
selectedSheet.getRange("B24").setFormulaLocal("=B23*30");
// 6. Paste to range C24:G24 on selectedSheet from range B24 on selectedSheet
selectedSheet.getRange("C24:G24").copyFrom(selectedSheet.getRange("B24"), ExcelScript.RangeCopyType.all, false, false);
// 7. Set number format for range B24:G24 on selectedSheet
selectedSheet.getRange("B24:G24").setNumberFormatLocal("#.##0,00 €");
// 8. Set fill color to D9D9D9 for range A23:G24 on selectedSheet
selectedSheet.getRange("A23:G24").getFormat().getFill().setColor("D9D9D9");
// 9. Paste to range A26 on selectedSheet from extended range obtained by extending right from range A17 on selectedSheet
selectedSheet.getRange("A26").copyFrom(selectedSheet.getRange("A17").getExtendedRange(ExcelScript.KeyboardDirection.right), ExcelScript.RangeCopyType.all, false, false);
}

スクリプトの修正

さて、上記のマクロではセル番号 A23 など固定値で書かれているため、次回に流用することができません。起こされたものを参考に、書き直したスクリプトがこちらです。

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
// 値を持つアクティブなセル範囲と、最後の行を取得
let range = sheet.getUsedRange(); // '2022'!A1:G22
let lastRow = range.getLastRow(); // A22:G22

// サブヘッダの Total Hours と Payroll in を入力
let subHeaderRange = sheet.getRangeByIndexes(lastRow.getRowIndex() + 1, 0, 2, 1);
subHeaderRange.setFormulas([["Total Hours"], ["=\"Payroll in \"&TEXT(TODAY(); \"[$-en]MMM JJJJ\")"]]);
let payrollCell = subHeaderRange.getCell(1, 0);
payrollCell.copyFrom(payrollCell, ExcelScript.RangeCopyType.values, false, false);

// Total Hours を算出
let headerCell = range.find("No.", {
completeMatch: false,
matchCase: true,
searchDirection: ExcelScript.SearchDirection.backwards
});
let rowCount: number = lastRow.getRowIndex() - headerCell.getRowIndex();
let sumRange = headerCell.getCell(0, 1).getRowsBelow(rowCount);

let totalCell = subHeaderRange.getCell(0, 1);
totalCell.setFormulas([[`=SUM(${sumRange.getAddress()})`]]);

// Payroll を算出
let payCell = totalCell.getCell(1, 0);
payCell.setFormulas([[`=${totalCell.getAddress()}*30`]]);
payCell.setNumberFormat("#,##0.00 €");

// 算出した2つのセルをコピーして右方向に貼り付け
let copyFromRange = sheet.getRange(totalCell.getAddress() + ':' + payCell.getAddress());
let colCount: number = range.getLastColumn().getColumnIndex() - copyFromRange.getColumnIndex();
let pasteToRange = copyFromRange.getColumnsAfter(colCount);
pasteToRange.copyFrom(copyFromRange, ExcelScript.RangeCopyType.all, false, false);

// 2行の背景色をグレーに変更
let colorRange = subHeaderRange.getResizedRange(0, colCount + 1);
colorRange.getFormat().getFill().setColor("D9D9D9");

// 新しいヘッダの追加
subHeaderRange.getCell(3, 0).copyFrom(headerCell.getExtendedRange(ExcelScript.KeyboardDirection.right), ExcelScript.RangeCopyType.all, false, false);
}

以下、少し補足します。

■ Total Hours の合計対象とする範囲を特定

対象となる最初のセル（マクロ記録時の例ではB18）を特定するために、初めにアクティブレンジ内で「No.」を含むセルを逆向きに検索し、”headerCell” としてA17を特定しています。

その後、隣のB17から行数分の getRowsBelow() を使って対象範囲 “sumRange” を取得しました。

なお、行数を算出して getRowsBelow() を使う代わりに、getRangeByIndexes() で行と列を指定する方法でも可能です。

let sumRange = sheet.getRangeByIndexes(headerCell.getRowIndex() + 1, headerCell.getColumnIndex() + 1, lastRow.getRowIndex() - headerCell.getRowIndex(), 1);

■ getAddress() でセル番号を取得

例えば “totalCell”や”payCell”など、getCell() を使ってセルを取得していますが、これはセルオブジェクトなのでそのまま文字列として使うことはできません。

なので、getRange() でB23:B24のように範囲を指定したい時にも totalCell.getAddress() + ':' + payCell.getAddress()のようにいちいち getAddress() メソッドを組み合わせます。

開発段階で、定義したセルの位置を見たい場合にも以下のように記述します。

console.log(totalCell.getAddress());

■ getExtendedRange() で Shift+Cmd+矢印キーで指定する範囲を取得

例えば、あるセルから右方向に値がすべて入っている範囲までを選択したい場合、キーボードを使えば Shift+Command+右矢印 (Mac) / Shift+Control+右矢印 (Win) で選択することができますね。

スクリプトでも、getExtendedRange() を使えば同じことが実現できます。

上の例では “headerCell” である A17 からこのメソッドを使って G17 までを範囲選択しています。

【2024/2更新】言語とロケール問題の修正

上記でも少し触れましたが、ドイツの言語においては数字のカンマとピリオドは逆になり、Excelで年を表すYYYYはJJJJと表記したり関数は=SUMが=SUMMEとなるといった違いがあります。

自分自身で使うファイルで設定をすべてドイツリージョンで統一する場合は問題ありませんが、今回 Sharepoint に配置して他のユーザも利用できるようにしたため、ユーザ固有の言語・ロケール設定によってスクリプト実行が失敗する問題がありました。

そこで次の点を修正しました：

• 〜Local() メソッドの修正

当初使っていた setFormulasLocal() や setNumberFormatLocal() は "〜Local" の付かないメソッドにそれぞれ置き換えました。

ドキュメントによるとLocalが付く場合は、ユーザーの言語と数値書式ロケールによって引数を指定するようです。

ユーザーの言語と数値書式ロケールで、A1 スタイル表記の数式を表します。 たとえば、英語の数式 "=SUM(A1, 1.5)" は、ドイツ語では "=SUMME(A1; 1,5)" になります。

今回のようにSharepointで実行ユーザに関わらず成功させるには、setNumberFormat("#,##0.00 €") のようにLocalの付かないメソッドを使用し、指定する値も英語表記に従います。

• ファイルの「地域の書式設定」をドイツ語に

ファイルを表示する際の数字書式はドイツ形式（#.##0,00) としたかったので、File > Options > Regional Format Settings よりドイツを指定（確認）しました。

一方で、月の短縮表記はドイツ語「Okt 2022」ではなく英語の「Oct 2022」で表示させたいと思ったので、TEXT関数の文字列指定に [$-en] を追記しました。（参考：言語コード）

"=\"Payroll in \"&TEXT(TODAY(); \"[$-en]MMM JJJJ\")"

なお、ここは追究できなかったのですが、setFormulas（Localなし）に変更して、値にTODAY()などの英語関数で指定するにも関わらず、TEXTの表示形式であるJJJJはファイルで設定した地域書式設定を継承してドイツ語でないといけないようでした。

ボタン作り

GAS ではオブジェクトを追加して、ボタンのように好きにデザインし、それにスクリプトを紐づけます。なので、後から紐づけるスクリプトを変更することもできます。

一方の Office スクリプトでは、スクリプトを作ってから、そのスクリプトのボタンを追加します。 具体的には、スクリプトのメニューをクリックして、ボタン追加を選ぶだけ。

ボタンが出現したら、一度右クリックして選択モードにすることで、配置を動かすことができます。

なお、cut & paste したらボタン機能がなくなり、ただの図形となります。

参考ページ

• ボタン：Run Office Scripts in Excel with buttons
• 書き方や構文の一例に - Office Scripts Loops and Setting Formulas (YouTube)
• Formulas - https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/excel-samples#formulas
• Web 用 Excel で地域の書式の設定を変更する
• setFormulasLocal() メソッドなど - ExcelScript.Range interface - Office Scripts