この記事では、Office Scriptsの技術的な側面について説明します。TypeScriptを使用したスクリプトコードの重要な部分や、ExcelオブジェクトとAPIの連携方法を学ぶことができます。 TypeScriptはOffice Scriptsの言語で、JavaScriptに似た構文を持っています。スクリプトには必ずmain関数が必要で、ExcelScript.Workbookをパラメータとして受け取ります。ワークブック、ワークシート、セルの範囲、テーブル、グラフなどのオブジェクトモデルについて理解し、それらを操作する方法も紹介されています。

Office Scriptsの技術的側面

この記事では、Office Scriptsの技術的側面について紹介します。TypeScriptベースのスクリプトコードの重要な部分と、ExcelオブジェクトおよびAPIがどのように連携するかを学びます。

インタラクティブな体験から始めたい場合は、チュートリアル: Excelテーブルの作成とフォーマットや、サンプルの概要をご覧ください。

TypeScript: Office Scriptsの言語

Office Scriptsは、TypeScriptで書かれています。TypeScriptは、JavaScriptのスーパーセットであり、JavaScriptに慣れている方は、両言語のコードが多く同じであるため、学びやすいでしょう。Office Scriptsのコードを始める前に、初歩的なプログラミング知識を持っていることをお勧めします。以下のリソースは、Office Scriptsのコーディングに関する理解を深める手助けとなります。

main関数: スクリプトの開始点

各スクリプトは、最初のパラメータとしてExcelScript.Workbook型を持つmain関数を含む必要があります。関数が実行されると、Excelアプリケーションはワークブックを第一のパラメータとしてmain関数を呼び出します。ExcelScript.Workbookは常に第一のパラメータである必要があります。

function main(workbook: ExcelScript.Workbook) {
  // ここにコードを記述
}

main関数内のコードは、スクリプトが実行されるときに実行されます。mainはスクリプト内の他の関数を呼び出すことができますが、関数に含まれないコードは実行されません。スクリプトは他のOffice Scriptsを呼び出すことはできません。

Power Automateを使用すると、フロー内でスクリプトを接続できます。データは、main関数のパラメータと返り値を通じてスクリプトとフローの間で渡されます。Office ScriptsとPower Automateを統合する方法については、Power AutomateでOffice Scriptsを実行するで詳しく説明されています。

オブジェクトモデルの概要

スクリプトを書くには、Office Scripts APIがどのように組み合わさって機能するかを理解する必要があります。ワークブックのコンポーネントはそれぞれ特定の関係を持っています。これらの関係は、ExcelのUIの関係と多くの点で一致しています。

• Workbookは1つ以上のWorksheetを含みます。
• WorksheetはRangeオブジェクトを通じてセルにアクセスします。
• Rangeは連続するセルのグループを表します。
• RangeはTables、Charts、Shapesなどのデータ視覚化または整理オブジェクトを作成するために使用されます。
• Worksheetは、個々のシートに存在するデータオブジェクトのコレクションを含みます。
• Workbooksは、全体のWorkbookに対するデータオブジェクト（たとえば、Tables）のコレクションを含みます。

Office Scripts APIのオブジェクトの完全なリストは、ExcelScriptパッケージで詳述されています。

Workbook

すべてのスクリプトは、main関数によってWorkbook型のworkbookオブジェクトが提供されます。これは、スクリプトがExcelワークブックと相互作用するための最上位のオブジェクトを表します。

以下のスクリプトは、ワークブックからアクティブなワークシートを取得し、その名前をログに記録します。

function main(workbook: ExcelScript.Workbook) {
    // アクティブなワークシートを取得
    let sheet = workbook.getActiveWorksheet();

    // 現在のワークシートの名前を表示
    console.log(sheet.getName());
}

Ranges

範囲は、ワークブック内の連続するセルのグループです。スクリプトでは通常、A1スタイルの表記（例: B3は列Bと行3の単一のセル、またはC2:F4は列CからF、行2から4のセル）を使用して範囲を定義します。

範囲には、値、数式、書式の3つのコアプロパティがあります。これらのプロパティは、セルの値を取得または設定し、評価される数式やセルの視覚的な書式を設定します。値と数式は、それぞれsetValuesおよびsetFormulasを使用して変更でき、書式は、個々に設定される複数の小さなオブジェクトからなるRangeFormatオブジェクトです。

範囲は、二次元配列を使用して情報を管理します。Office Scriptsフレームワークで配列を処理する詳細については、範囲を扱うを参照してください。

範囲のサンプル

次のサンプルは、販売記録を作成する方法を示しています。このスクリプトでは、Rangeオブジェクトを使用して値、数式、書式の一部を設定しています。

function main(workbook: ExcelScript.Workbook) {
    // アクティブなワークシートを取得
    let sheet = workbook.getActiveWorksheet();

    // ヘッダーを作成し、目立つように書式を設定
    let headers = [["Product", "Quantity", "Unit Price", "Totals"]];
    let headerRange = sheet.getRange("B2:E2");
    headerRange.setValues(headers);
    headerRange.getFormat().getFill().setColor("#4472C4");
    headerRange.getFormat().getFont().setColor("white");

    // 製品データ行を作成
    let productData = [
        ["Almonds", 6, 7.5],
        ["Coffee", 20, 34.5],
        ["Chocolate", 10, 9.54],
    ];
    let dataRange = sheet.getRange("B3:D5");
    dataRange.setValues(productData);

    // 売上合計の数式を作成
    let totalFormulas = [
        ["=C3 * D3"],
        ["=C4 * D4"],
        ["=C5 * D5"],
        ["=SUM(E3:E5)"],
    ];
    let totalRange = sheet.getRange("E3:E6");
    totalRange.setFormulas(totalFormulas);
    totalRange.getFormat().getFont().setBold(true);

    // 合計を米ドル額で表示
    totalRange.setNumberFormat("$0.00");
}

このスクリプトを実行すると、現在のワークシートに以下のデータが作成されます。

セルの値のタイプ

各セルには値があります。この値は、セルに入力された基礎的な値であり、Excelに表示されるテキストとは異なる場合があります。たとえば、セルには「5/2/2021」と表示されているかもしれませんが、実際の値は44318です。この表示は数値の書式を変更することによって変更できますが、セルの実際の値やタイプは新しい値が設定されるまで変わりません。

セルの値を使用する際は、TypeScriptにどの値を期待するかを明示することが重要です。セルには、string、number、またはbooleanのいずれかのタイプが含まれます。スクリプトが返された値をこれらのタイプの1つとして扱うためには、タイプを宣言する必要があります。

以下のスクリプトは、前のサンプルのテーブルから平均価格を取得します。コードに注目してください。priceRange.getValues() as number[][]は、範囲の値のタイプをnumber[][]として宣言しています。この配列内のすべての値は、スクリプト内で数値として扱うことができます。

function main(workbook: ExcelScript.Workbook) {
  // アクティブなワークシートを取得
  let sheet = workbook.getActiveWorksheet();

  // "Unit Price"列を取得
  // getValuesの呼び出し結果はnumber[][]として宣言されており、算術演算が可能です。
  let priceRange = sheet.getRange("D3:D5");
  let prices = priceRange.getValues() as number[][];

  // 平均価格を取得
  let totalPrices = 0;
  prices.forEach((price) => totalPrices += price[0]);
  let averagePrice = totalPrices / prices.length;
  console.log(averagePrice);
}

チャート、テーブル、その他のデータオブジェクト

スクリプトは、Excel内のデータ構造や視覚化を作成・操作できます。テーブルやチャートは、最も一般的に使用されるオブジェクトの2つですが、APIはPivotTables、図形、画像などもサポートしています。これらはコレクションに格納されており、この記事の後半で詳しく説明します。

テーブルの作成

データで満たされた範囲を使ってテーブルを作成します。書式設定やテーブルコントロール（たとえばフィルター）が自動的に範囲に適用されます。

以下のスクリプトは、前のサンプルの範囲を使用してテーブルを作成します。

function main(workbook: ExcelScript.Workbook) {
    // アクティブなワークシートを取得
    let sheet = workbook.getActiveWorksheet();

    // B2:E5のデータを使用してヘッダーのあるテーブルを追加
    sheet.addTable("B2:E5", true);
}

このスクリプトを、前のデータが含まれているワークシートで実行すると、次のようなテーブルが作成されます。

チャートの作成

範囲内のデータを視覚化するためにチャートを作成します。スクリプトは、ニーズに応じてカスタマイズできる多様なチャート種類をサポートします。

以下のスクリプトは、3つのアイテムの簡単な縦棒グラフを作成し、ワークシートの上部から100ピクセル下に配置します。

function main(workbook: ExcelScript.Workbook) {
    // アクティブなワークシートを取得
    let sheet = workbook.getActiveWorksheet();

    // B3:C5のデータを使用して縦棒チャートを作成
    let chart = sheet.addChart(
        ExcelScript.ChartType.columnStacked,
        sheet.getRange("B3:C5")
    );

    // チャートのマージンを画面の上部から100ピクセルに設定
    chart.setTop(100);
}

このスクリプトを以前のテーブルがあるワークシートで実行すると、次のようなチャートが作成されます。

コレクション

Excelオブジェクトに同じタイプのオブジェクトが1つ以上のコレクションがある場合、それらは配列に格納されます。たとえば、WorkbookオブジェクトはWorksheet[]を含みます。この配列は、Workbook.getWorksheets()メソッドを使用してアクセスします。getメソッドが複数形の場合（たとえばWorksheet.getCharts()）、オブジェクトコレクション全体を配列として返します。

返された配列は通常の配列であるため、スクリプトに利用可能な標準の配列操作をすべて使用できます。また、配列インデックス値を使用してコレクション内の個々のオブジェクトにアクセスすることもできます。たとえば、workbook.getTables()[0]は、コレクション内の最初のテーブルを返します。Office Scriptsフレームワークで組み込みの配列機能を使用する方法については、コレクションを扱うをご覧ください。

個々のオブジェクトは、getメソッドを介してコレクションからアクセスされます。単数形のgetメソッド（たとえばWorksheet.getTable(name)）は、個々のオブジェクトを返し、特定のオブジェクトのIDまたは名前を必要とします。このIDまたは名前は通常、スクリプトによって設定されるか、ExcelのUIを介して設定されます。

以下のスクリプトは、ワークブック内のすべてのテーブルを取得します。その後、ヘッダーが表示され、フィルターボタンが表示され、テーブルスタイルが「TableStyleLight1」に設定されるか確認します。

function main(workbook: ExcelScript.Workbook) {
  // テーブルコレクションを取得
  let tables = workbook.getTables();

  // すべてのテーブルにフォーマットプロパティを設定
  tables.forEach(table => {
    table.setShowHeaders(true);
    table.setShowFilterButton(true);
    table.setPredefinedTableStyle("TableStyleLight1");
  });
}

スクリプトでExcelオブジェクトを追加する

親オブジェクトで利用可能な対応するaddメソッドを呼び出すことによって、テーブルやチャートなどのドキュメントオブジェクトをプログラム的に追加できます。

以下のスクリプトは、ワークブックの最初のワークシートにExcelでテーブルを作成します。作成されたテーブルはaddTableメソッドによって返されます。

function main(workbook: ExcelScript.Workbook) {
    // 最初のワークシートを取得
    let sheet = workbook.getWorksheets()[0];

    // A1:G10のデータを使用してテーブルを追加
    let table = sheet.addTable("A1:G10", true /* ヘッダーがあるためtrue */);

    // 他のスクリプトで簡単に参照できるよう、テーブルに名前を付ける
    table.setName("MyTable");
}

コレクション内にオブジェクトが存在するか確認する

スクリプトでは、処理を進める前にテーブルや類似のオブジェクトが存在するかを確認することがしばしば必要です。スクリプトまたはExcelのUIで割り当てられた名前を使用して必要なオブジェクトを特定し、処理を行います。getメソッドは、要求されたオブジェクトがコレクション内に存在しない場合、undefinedを返します。

以下のスクリプトは、"MyTable"という名前のテーブルを要求し、テーブルが見つかったかどうかをif...elseステートメントを使用して確認します。

function main(workbook: ExcelScript.Workbook) {
  // "MyTable"という名前のテーブルを取得
  let myTable = workbook.getTable("MyTable");

  // テーブルがワークブックに存在する場合、myTableは値を持ちます。
  // そうでなければ、変数はundefinedになり、else句に移ります。
  if (myTable) {
    let worksheetName = myTable.getWorksheet().getName();
    console.log(`MyTableは${worksheetName}ワークシートにあります`);
  } else {
    console.log(`MyTableはワークブックにありません。`);
  }
}

Office Scriptsの一般的なパターンは、スクリプトが実行されるたびにテーブル、チャート、または他のオブジェクトを再作成することです。古いデータが必要ない場合は、古いオブジェクトを削除してから新しいオブジェクトを作成することが最適です。これにより、名前の衝突や他のユーザーによって導入されたその他の差異が回避されます。

以下のスクリプトは、"MyTable"という名前のテーブルが存在する場合はそれを削除し、その後同じ名前の新しいテーブルを追加します。

function main(workbook: ExcelScript.Workbook) {
  // 最初のワークシートから"MyTable"という名前のテーブルを取得
  let sheet = workbook.getWorksheets()[0];
  let tableName = "MyTable";
  let oldTable = sheet.getTable(tableName);

  // テーブルが存在する場合は削除
  if (oldTable) {
    oldTable.delete();
  }

  // 同じ名前の新しいテーブルを追加
  let newTable = sheet.addTable("A1:G10", true);
  newTable.setName(tableName);
}

スクリプトでExcelオブジェクトを削除する

オブジェクトを削除するには、そのオブジェクトのdeleteメソッドを呼び出します。

以下のスクリプトは、ワークブック内の最初のワークシートを削除します。

function main(workbook: ExcelScript.Workbook) {
    // 最初のワークシートを取得
    let sheet = workbook.getWorksheets()[0];

    // そのワークシートをワークブックから削除
    sheet.delete();
}

オブジェクトモデルに関するさらに詳しい情報

Office Scripts APIリファレンスドキュメントは、Office Scriptsで使用されるオブジェクトの包括的なリストです。ここでは、目次を使用して、調べたい任意のクラスに移動できます。以下は、一般的に参照されるページのいくつかです。

PivotTableオブジェクトモデルに特有の情報については、Office Scripts内のPivotTablesを扱うをご覧ください。

その他の情報

Office Scriptsの使用に関する情報や、スクリプトの例、APIの詳細を知るには、Microsoftの公式ドキュメントを参照してください。

————-

Fundamentals for Office Scripts in Excel – Office Scripts

Source link

This article provides an introduction to the technical aspects of Office Scripts, focusing on how to use TypeScript to interact with Excel objects and APIs effectively. It emphasizes that Office Scripts is built on TypeScript, a superset of JavaScript, encouraging readers to have a foundational understanding of programming before diving into scripting.

Key Highlights:

1. Main Function:

   • Each script must start with a main function, which takes an ExcelScript.Workbook object as the first parameter. This function is the entry point for executing the script.

2. Office Scripts Object Model:

   • The article outlines the fundamental structure of an Excel workbook, which contains worksheets, rows, columns, and ranges. It explains how to access and manipulate these objects using the Office Scripts APIs.

3. Ranges:

   • Ranges are groups of contiguous cells, and scripts can manipulate their values, formulas, and formatting. The article provides examples of how to set values and apply formatting.

4. Data Objects:

   • Office Scripts can create and manage various Excel data structures such as tables and charts. The scripts can automate tasks such as adding tables and generating charts based on data from ranges.

5. Collections:

   • The article explains how Excel objects are organized in collections that can be accessed and manipulated through methods. This allows for streamlined management of multiple objects of the same type.

6. Adding and Removing Objects:

   • The article illustrates how to programmatically add and remove objects (like tables and worksheets) using the appropriate methods instead of direct manipulation of collections.

7. Error Handling:

   • It discusses checking for the existence of objects, which can help prevent errors during script execution.
8. Further Reading:
   • The article encourages readers to explore the Office Scripts API reference documentation for a deeper understanding of the available objects and how to work with them.

Overall, the article serves as a comprehensive guide for beginners looking to utilize Office Scripts for automating tasks in Excel, providing foundational knowledge and practical coding examples.