【業務自動化】OfficeScriptsでExcelテーブルを、JSONやCSVで出力する

概要

受託開発企業で働くfussasyといいます。普段は、PaaS環境上でアプリケーション開発をしています。技術検証の過程で、顧客からExcelで受領したデータをJSON形式・CSV形式に変換する際に、OfficeScriptsを利用してみました。

OfficeScriptsとExcelVBAの違いについて

OfficeScriptsは、Microsoft Office製品で使用されるスクリプト言語です。OfficeScriptsを使用すると、Office製品でのタスクの自動化やカスタマイズが可能になります。OfficeScriptsは、JavaScript（TypeScript）に基づいおり、Web上で実行・保存・共有されるため、複数のユーザーが同時作業が可能です。OfficeScriptがオンラインがベースである一方、ExcelVBAはオフライン状態でのデスクトップ実行・保存、デスクトップ間共有が主です。OfficeScriptとExcelVBAは自動化という目的では、利用範囲に大きな差異はないです。ただ現在、OfficeScriptsは他のブックの操作・外部連携・イベント・CSVファイルの操作などが、まだできないみたいです。（MicrosoftはOfficeScriptsを推しているので、将来的には解決される可能性は大きいと思われます。）

OfficeScriptsでテーブルデータをJSON形式やCSV形式で出力する

今回は、顧客から提供されたExcelのテーブルデータを、JSON形式やCSV形式に変換する際にOfficeScriptsを利用しました。JSON形式に変換するコードについては、デフォルトで提供されています。以下、公式ページでサンプルデータも提供されているので、ダウンロードして試すこともできます。

Excelの自動化タブを押して、目的の作業の標準スクリプトを選びましょう。今回は「テーブルデータをJSONとして返す」の標準スクリプトを参考に、新しいスクリプトを作成します。

以下、ダウンロードしたサンプルデータです。

Event ID	Date	Location	Capacity	Speakers
E107	2020-12-10	Montgomery	10	Debra Berger
E108	2020-12-11	Montgomery	10	Delia Dennis
E109	2020-12-12	Montgomery	10	Diego Siciliani
E110	2020-12-13	Boise	25	Gerhart Moller
E111	2020-12-14	Salt Lake City	20	Grady Archie
E112	2020-12-15	Fremont	25	Irvin Sayers
E113	2020-12-16	Salt Lake City	20	Isaiah Langer
E114	2020-12-17	Salt Lake City	20	Johanna Lorenz

以下、「テーブルデータをJSONとして返す」の標準スクリプトを微修正したものです。

function main(workbook: ExcelScript.Workbook): TableData[] {
  // 選択中のテーブルを取得。
  const table = workbook.getActiveWorksheet().getTables()[0];
  // テーブルからすべての値をテキストとして取得。
  const texts = table.getRange().getTexts();
  // 行の構造に一致するJSONオブジェクトの配列を作成。
  // returnObjectFromValues関数を使用して値からJSONオブジェクトの配列を作成。
  let returnObjects: TableData[] = [];
  if (table.getRowCount() > 0) {
    returnObjects = returnObjectFromValues(texts);
  }

  // 情報をログに出力。
  console.log("レコード件数 : " + returnObjects.length);
  // JSON形式出力しない場合はコメントアウト
  let outputJson = JSON.stringify(returnObjects, undefined, 1) 
  console.log(outputJson);
  // CSV形式出力しない場合はコメントアウト
  let outputCsv = json2csv(returnObjects);
  console.log(outputCsv);

  return returnObjects
}

// JSON形式に変換。
function returnObjectFromValues(values: string[][]): TableData[] {
  let objectArray: TableData[] = [];
  let objectKeys: string[] = [];
  for (let i = 0; i < values.length; i++) {
    if (i === 0) {
      objectKeys = values[i]
      continue;
    }
    let object: { [key: string]: string } = {}
    for (let j = 0; j < values[i].length; j++) {
      object[objectKeys[j]] = values[i][j]
    }

    objectArray.push(object as unknown as TableData);
  }
  return objectArray;
}

// JSONをCSV形式に変換。
function json2csv(json: TableData): string {
  const header = Object.keys(json[0]).join(',') + "\n";
  const length = Object.keys(json).length;
  let body = "";
  for (let i = 0; i < length; i++) {
    body += Object.values(json[i]).join(",") + "\n";
  }
  return header + body;
}

// TableDataというインターフェースを定義。
interface TableData {
}

上記を実行すると、JSON形式とCSV形式に変換されてログ出力されます。.jsonや.csvファイルを併せて作成しようとしましたができませんでした。なので、別でファイルを作る場合、Console.logで出力されるログを手動でコピペすることになりそうです。

さいごに

OfficeScriptsはJavaScript（TypeScript）で処理の記述が可能ですし、汎用的な作業については、標準スクリプトが提供されているので、使いやすさを感じています。ExcelVBAのアップデートは無いに等しい一方、OfficeScriptsのアップデートはどんどんされているので、今後もできることの幅が増えていくだろうという気はしています。Office系の作業はどうしても多いので、アップデート情報は常にキャッチアップしていきたいところです。

参考記事