ExcelデータをJSON形式で出力 – Office Scripts

Excelのテーブルデータは、JSON形式のオブジェクトの配列として表現できます。各オブジェクトはテーブルの行を表し、ユーザーに見やすい一貫した形式でデータを抽出します。このデータは、Power Automateフローを通じて他のシステムに渡すことができます。サンプルコードでは、ExcelのテーブルデータをJSONとして返す方法が示されています。また、ハイパーリンクを含むカラムも処理可能で、特定のカラムからリンクを抽出できます。Power Automateでの自動化ワークフローの作成方法も提供されています。

ExcelデータをJSONとして取り扱う方法

Excelの表データは、各オブジェクトが表の行を表す形式で、オブジェクトの配列としてJSON（JavaScript Object Notation）で表現できます。これはUXに優れた一貫したフォーマットでExcelからデータを抽出し、他のシステムに提供する際に便利です。特に、Power Automateフローを通じてデータを別のシステムへ渡すことが可能です。

セットアップ: サンプルExcelファイル

このワークブックには、スクリプトが期待するデータ、オブジェクト、フォーマットが含まれています。

このサンプルのバリエーションには、テーブルの列の1つにハイパーリンクが含まれており、JSON内で追加のセルデータレベルを表示することができます。

サンプルコード: テーブルデータをJSONとして返す

以下のスクリプトをサンプルワークブックに追加し、自分でサンプルを試してみましょう！

function main(workbook: ExcelScript.Workbook): TableData[] {
  // "PlainTable"ワークシートの最初のテーブルを取得
  const table = workbook.getWorksheet('PlainTable').getTables()[0];

  // テーブルのすべての値をテキストとして取得
  const texts = table.getRange().getTexts();

  // 行構造に一致するJSONオブジェクトの配列を作成
  let returnObjects: TableData[] = [];
  if (table.getRowCount() > 0) {
    returnObjects = returnObjectFromValues(texts);
  }

  // 情報をログに記録し、Power Automateフローへ返す
  console.log(JSON.stringify(returnObjects));
  return returnObjects;
}

// 2次元配列の値を一般的なJSONオブジェクトに変換する関数
function returnObjectFromValues(values: string[][]): TableData[] {
  let objectArray: TableData[] = [];
  let objectKeys: string[] = [];
  for (let i = 0; i < values.length; i++) {
    // コードを続けます...
  }
}

サンプル出力: "PlainTable" ワークシートからの出力

[{
    "Event ID": "E107",
    "Date": "2020-12-10",
    "Location": "Montgomery",
    "Capacity": "10",
    "Speakers": "Debra Berger"
}, 
// 他のオブジェクト...
]

サンプルコード: ハイパーリンクテキストとしてテーブルデータを返す

function main(workbook: ExcelScript.Workbook): TableData[] {
  // "WithHyperLink"ワークシートの最初のテーブルを取得
  const table = workbook.getWorksheet('WithHyperLink').getTables()[0];

  // テーブルのすべての値をテキストとして取得
  const range = table.getRange();

  // 行構造に一致するJSONオブジェクトの配列を作成
  let returnObjects: TableData[] = [];
  if (table.getRowCount() > 0) {
    returnObjects = returnObjectFromValues(range);
  }

  // 情報をログに記録し、Power Automateフローへ返す
  console.log(JSON.stringify(returnObjects));
  return returnObjects;
}

function returnObjectFromValues(range: ExcelScript.Range): TableData[] {
  let values = range.getTexts();
  let objectArray : TableData[] = [];
  // コードを続けます...
}

サンプル出力: "WithHyperLink" ワークシートからの出力

[{
    "Event ID": "E107",
    "Date": "2020-12-10",
    "Location": "Montgomery",
    "Capacity": "10",
    "Search link": "https://www.google.com/search?q=Montgomery",
    "Speakers": "Debra Berger"
}, 
// 他のオブジェクト...
]

Power Automateでの使用

このようなスクリプトをPower Automateで使用する方法については、以下を参照してください：
Power Automateでの自動化ワークフローの作成。

この方法を使用すれば、Excelのデータを効果的に管理し、他のシステムへスムーズに連携することが可能になります。

————-

Output Excel data as JSON – Office Scripts

Source link

Excel table data can be effectively transformed into an array of objects formatted as JSON, where each object corresponds to a row in the Excel table. This JSON representation allows for a consistent data structure that can be easily utilized in various systems, particularly through integrations with Power Automate flows.

Setup:

To demonstrate this, a sample Excel workbook is utilized. This workbook contains predefined data, objects, and formatting that are necessary for the accompanying script to function properly. Additionally, variations of the sample include hyperlinks within a specific table column, allowing for further data exploration in the JSON output.

Sample Code:

The provided sample scripts illustrate how to return table data as JSON. Users can implement these scripts in their sample workbook to see the results firsthand.

• Basic JSON Extraction:
  The main function retrieves the first table from the specified worksheet ("PlainTable"), collects the textual data, and converts it into a JSON object array, which is then logged and returned. It’s important to note that table column names with spaces should be enclosed in quotation marks.

  Sample Output:

  [
  {"Event ID": "E107", "Date": "2020-12-10", "Location": "Montgomery", "Capacity": "10", "Speakers": "Debra Berger"}, 
  ...
  ]

• Including Hyperlink Data:
  For tables that include hyperlinks, a similar approach is used but extracts hyperlink information specifically from designated columns. The script modifies the extraction process to incorporate the hyperlink text where applicable.

  Sample Output (with hyperlinks):

  [
  {"Event ID": "E107", "Date": "2020-12-10", "Location": "Montgomery", "Capacity": "10", "Search link": "https://www.google.com/search?q=Montgomery", "Speakers": "Debra Berger"},
  ...
  ]

Power Automate Integration:

These JSON outputs can be directly fed into Power Automate to create workflows and automate processes. Users can refer to additional resources for guidance on integrating such scripts into Power Automate and enhancing their automated tasks.

Overall, the ability to represent Excel table data as JSON streamlines data handling and provides versatile integration options with various applications.