PR

Microsoft Office Script備忘録 〜 ExcelScriptを使ってみた話

2024.03.12

今回は事務系の仕事には必須のMicrosoft Excelの話。工程表から受発注まで色々と管理するのにExcelが使われているのですが、しばらくぶりにExcelを使ってみたら色々とスクリプトが組めるようになっていたので、調べたことを備忘録として投稿します。今どきなら「Copilot for Microsoft 365 」を使って〜とかやりたかったのですが、超零細企業の弊社ではそんなお金は捻出できませんでした(汗。

Microsoft Office スクリプト

Office系で何か自動化するとなるとVBA(Visual Basic for Application)が昔から使えていた記憶があります。Basic系なので何かプログラミング言語を習得している人なら比較的簡単にプログラミングが可能な事から大昔から「ExcelといえばVBA」という感じ。

今回、Excelで処理を自動化しようと調べてみると「Microsoft Office Script」なるものが存在して「簡単なプログラミングができる」との事で使ってみた次第です。

Microsoft Officeスクリプトが使えるライセンス

個人および家庭向けはスクリプトが使えず、Office 365 Business以上が必要だそうです。普通の会社ならばBusiness以上を使ってると思うので、「自動化」タブが表示されていない場合は管理者が制限している可能性があるらしいです。私のアカウントはMicrosoft 365 Business Standardで一人管理者なのでデフォルトで表示されていました。

Microsoft Officeスクリプトで使えるプログラム言語

Officeスクリプトの基礎によるとJavaScript系の言語という事らしいです。スクリプト言語であれば多少の使い勝手の違いがあれど、プログラミング経験者ならVBAより使いやすいと思います。私としてはプログラミング言語よりはAPIの使い勝手の方が重要だと思っています。

Microsoft Office スクリプトの実行環境

ライセンスと権限がクリアされていれば「自動化」のタブが表示されていて「すべてのスクリプト」を選択すると右側に「コードエディター」が表示されます。「＋新しいスクリプト」を選択するとデフォルトの「main」が作成されます。

TypeScript
function main(workbook: ExcelScript.Workbook) {
    // アクティブなセルとワークシートを取得します。
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    // 選択したセルの塗りつぶしの色を黄色に設定します。
    selectedCell.getFormat().getFill().setColor("yellow");

    // TODO: コードを記述するか、下の [挿入] アクション ボタンを使用してください。

}

「▷ 実行」を選択するとアクティブなセルが黄色になるので注意です。

ExcelScript API

ExcelScriptのAPIを参照しながら、今回使ったAPIを記載しておきます。次に作る時に毎回調べる事になるので(汗。

デバッグ出力：console.log()

何は無くともデバッグ出力。コードエディタの下側に表示されます。デバッグ出力とは関係ないですが、スクリプト経由で編集したファイルを元に戻したい場合は「ファイル→バージョン履歴の表示」で実行前のファイルがダウンロード可能です。

TypeScript
function main(workbook: ExcelScript.Workbook) {
    // アクティブなセルとワークシートを取得します。
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    console.log(selectedCell.getAddress());
    console.log(selectedSheet.getName())
}

ブックに対する操作

シート取得/追加：Workbook.get/addWorksheet()

シートの取得や追加です。

TypeScript
function main(workbook: ExcelScript.Workbook) {
    // アクティブなセルとワークシートを取得します。
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    workbook.addWorksheet("Test1");
    console.log(workbook.getWorksheet("Test1").getName());
    console.log(selectedSheet.getName());
}

シートに対する操作

範囲選択：Worksheet.get*Range()

シート内のデータに対して追加・削除・検索など行う時に範囲を選択する必要があります。その範囲を決めてオブジェクトを取得し、そのRangeオブジェクトに対して操作する流れです。例ではA1とM20のセルに値を入れてgetUsedRangeしているので、オブジェクトはA1:M20が選択されている事になります。ちなみに返す範囲はセルに値が無くても罫線などフォーマット変更も「Used」の範囲に入ります。

TypeScript
function main(workbook: ExcelScript.Workbook) {
    // アクティブなセルとワークシートを取得します。
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    selectedSheet.getRange("A1").setValue("A1");
    selectedSheet.getRange("M20").setValue("M20");
    console.log(selectedSheet.getUsedRange().getAddress());
    console.log(selectedSheet.getRange("G:H").getAddress());
    console.log(selectedSheet.getRange("5:8").getAddress());
}

シート表示/名前変更：Worksheet.activate() / setName()

他のシートを処理したあとに対象のシートを表示したい時に使うactivate()。スクリプト処理で実行に多少の時間が必要な場合はactivateでどのシートを処理しているか確認時に利用します。

TypeScript
function main(workbook: ExcelScript.Workbook) {
    // アクティブなセルとワークシートを取得します。
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    console.log(selectedSheet.getName());
    selectedSheet.setName("Sheet1");
    console.log(selectedSheet.getName());
    workbook.getWorksheet("Sheet3").activate();
    selectedSheet = workbook.getActiveWorksheet();
    console.log(selectedSheet.getName());
}

シートのコピーと削除：Worksheet.copy()/delete()

テンプレートから何か作ったり、必要のないシートを削除したりするのに使います。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // アクティブなセルとワークシートを取得します。
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();

  let newSheet = selectedSheet.copy(ExcelScript.WorksheetPositionType.after, selectedSheet);
  console.log(selectedSheet.getName());
  console.log(newSheet.getName());
  selectedSheet.delete();
  console.log(selectedSheet.getName());  
}

レンジに対する操作

スクリプトでExcelデータを取り扱う時はRangeに対して操作を頻繁に使います。使い方も色々で悩ましい所ですが、使いたいように使います。

装飾する：Range.getFormat().*

取得したレンジに色を付けたり罫線を引いたりするため、Formatオブジェクトを取得してから処理します。レンジに対する処理ではなくてフォーマットに対する処理ですね(汗。常に対象のオブジェクトを取得してから設定するので面倒です。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // アクティブなセルとワークシートを取得します。
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();

  const rowMax = 9;
  const columnMax = 10;
  let value = 10;

  for (let i = 0; i < rowMax; i++) {
    for (let j = 0; j < columnMax; j++) {
      selectedSheet.getCell(i,j).setValue(value++);
    }
  }
  selectedSheet.getUsedRange().clear(ExcelScript.ClearApplyTo.formats);

  selectedSheet.getUsedRange().getFormat().autofitColumns();
  selectedSheet.getUsedRange().getFormat().getFill().setColor("orange");
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);
  selectedSheet.getUsedRange().getColumn(0).getFormat().getFill().setColor("green");
  selectedSheet.getUsedRange().getRow(3).getFormat().getFill().setColor("white");
}

追加・削除： Range.insert/delete()

行や列の挿入と削除。レンジだけではなく、シートに対しても有効。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // アクティブなセルとワークシートを取得します。
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();

  selectedSheet.getUsedRange().getColumn(3).insert(ExcelScript.InsertShiftDirection.right);
  selectedSheet.getUsedRange().getColumn(3).setValue("X");
  selectedSheet.getUsedRange().getRow(5).insert(ExcelScript.InsertShiftDirection.down);
  selectedSheet.getUsedRange().getColumn(0).delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getUsedRange().getRow(0).delete(ExcelScript.DeleteShiftDirection.up);
}

コピー：Range.copyFrom()

moveTo()だと範囲が空になってるけど、その後の操作ができないので、コピーした後に元のデータを行ごと削除するようにしました。コピーであれば引数で渡したRangeの削除が可能でした。

TypeScript
function complete(move: ExcelScript.Range, to: ExcelScript.Worksheet){
  to.getUsedRange().getRow(2).insert(ExcelScript.InsertShiftDirection.down);
  to.getUsedRange().getRow(2).copyFrom(move);
//  move.moveTo(to.getUsedRange().getRow(2));
  move.delete(ExcelScript.DeleteShiftDirection.up);
}

検索：Range.find()

選択範囲内を検索して対象の単一オブジェクトを返します。criteriaの中身は省略可能です。文字列検索なのでシートの値で検索する場合はString()で文字列に変換が必要です。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // アクティブなセルとワークシートを取得します。
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();

  let ad_99 = selectedSheet.getUsedRange().find("99", {}).getAddress();
  console.log(ad_99);
  let ad_blank = selectedSheet.getUsedRange().find("X", {}).getAddress();
  console.log(ad_blank);
  ad_blank = selectedSheet.getUsedRange().find("X", {completeMatch: true, matchCase: true, searchDirection: ExcelScript.SearchDirection.backwards}).getAddress();
  console.log(ad_blank);
}

セル選択(UI的に)：Range.select()

スクリプト処理が終わったり、途中で確認したい場合にポップアップで通知させようと思ったのですが、ポップアップの処理がエラー時の処理のみだったので、別途メッセージ代わりにシートやセルを用意してそこにメッセージを記載するようにしました。その範囲にフォーカスをあてると。シートのactivateだとどこ見てよいかわからない場合があるので。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // アクティブなセルとワークシートを取得します。
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();

  if( workbook.addWorksheet("Message") ){
    workbook.getWorksheet("Message").getRange("A1").setValue("Done!");
  }

  workbook.getWorksheet("Message").getRange("A1").select();
  console.log(workbook.getActiveWorksheet().getName());
  console.log(workbook.getActiveCell().getValue());
}

その他

日付関連：Date()

日付の処理について。セルから日付のデータをgetValue()した場合の処理が特殊だったので記載しておきます。本家にある計算方法でセルから引っ張ったデータをDate()に変換可能との事です。

TypeScript
function main(workbook: ExcelScript.Workbook) {
  // Read a date at cell A1 from Excel.
  let dateRange = workbook.getActiveWorksheet().getRange("A1");

  // Convert the Excel date to a JavaScript Date object.
  let excelDateValue = dateRange.getValue() as number;
  let javaScriptDate = new Date(Math.round((excelDateValue - 25569) * 86400 * 1000));
  console.log(javaScriptDate);
}

雑感

少し使ってみた感想としては「VBAよりは良いかな」という感じです。処理が遅いのとUI的な所がVBAの方が良いと思いますが、データ処理に関してはスクリプトの方が簡単に処理できるので楽でした。

これらのAPIを使って取引先から提供される各種データを管理・操作していこうと思います。電子データでもらえない所が多いので、次は手入力を回避するためにカメラでデータを読み込めるような何かを作ってみようと思います。いつになるかわからないけど。

スポンサーリンク

コメント

タイトルとURLをコピーしました