はじめに
Microsoft 365やOneDirve Businessなどのプランを契約していて管理者が自動化を有効にしている場合、Excel on the webには作業を自動化する方法として下記の２つの方法が用意されています。
- PowerAutomateを利用する
- OfficeScriptsを利用する
この記事では、OfficeScriptsを使ってNulabのBacklogから課題の情報を取得する方法について説明します。
Backlogから課題を取得する
Backlogの情報を取得するにはBacklogが用意しているBacklog APIを利用して各種の情報を取得します。Excel on the webでは5/27に一般公開になったOfficeScriptsを使用してExcelからこのAPIを呼び出すことができます。
JavaScriptからBacklog APIを呼び出す場合、Nulabが提供するbacklog-jsを利用すると簡単に機能を実装できますが、現時点のOfficeScriptsでは外部ライブラリが利用できないため自前でAPIを呼び出す必要があります。
Backlog APIキーの準備
Backlog APIの認可と認証にはAPIキーを利用した方法と、OAuth 2.0を利用した方法が説明されていますがOffceScriptsではAPIキーを利用した方法で認証と認可を行います。
まずはBacklogヘルプセンターのAPIの設定に従って、API連携を行いたいBacklogユーザーのAPIキーを発行します。
OfficeScriptsをはじめる
OfficeScriptsを新たに作成する場合は、Excelの自動化] > すべてのスクリプト > + 新しいスクリプト からスクリプトを作成していきます。
BacklogAPIを呼び出してみる
OfficeScriptsから外部のAPIを呼び出す場合はfetch APIを利用します。まずはBacklogのスペース情報を取得してみましょう。
Backlog APIを呼び出すにはBacklogのURLとAPIキーが必要です。OfficeScriptsには秘密情報を管理する仕組みがないので設定値もシート上で管理してしまうのがお手軽です。
次の例ではSheet1シート上に定義されたBacklogURL（B2）とAPIキー（B3）の情報をもとにBacklog APIからスペース情報を取得しB4に表示しています。
async/awatを利用したいのでmain関数のシグネチャをfunction main(workbook: ExcelScript.Workbook)からasync function main(workbook: ExcelScript.Workbook): Promise<void>に変更しています。また、OfficeScriptsでは型定義でAnyが許されない点に注意してください。
async function main(workbook: ExcelScript.Workbook): Promise<void>
{
const sheet = workbook.getWorksheet("Sheet1");
const backlogUrl = sheet.getRange("B1").getValue();
const apiKey = sheet.getRange("B2").getValue();
const response = await fetch(`${backlogUrl}/api/v2/space?apiKey=${apiKey}`);
const space: Space = await response.json();
sheet.getRange("B4").setValue(space.spaceKey);
}
interface Space {
spaceKey: string,
name: string,
ownerId: number
lang: string,
timezone: string,
reportSendTime: string,
textFormattingRule: string,
created: string,
updated: string
}
実行ボタンを押すとOfficeScriptsが実行されます。
Excelにテーブルを作成
課題一覧を取得する前に結果を表示するためのテーブルをExcel上に作成します。
7行目に取得したいデータの見出しを作成して、テーブルにしたい範囲を選択後にリボンのホーム > テーブルとして書式設定 からテーブルの書式を選択してテーブルにします。
先頭行をテーブルの見出しとして使用するをチェックします。
このテーブルはわかりやすいように課題一覧という名前にしておきましょう。
課題の取得
課題一覧の取得APIを利用して課題を取得し課題の一覧を表示していきます。
先ほど設定したテーブルを取得後、いったん現在表示されている行をクリアします。
// テーブルの取得と初期化
const dataTable = sheet.getTable("課題一覧");
var rowCount = dataTable.getRowCount();
if (rowCount > 0) {
dataTable.deleteRowsAt(0, dataTable.getRowCount());
}
その後、Backlogの課題一覧の取得 APIを呼び出して先ほど初期化したテーブルの見出しに合う形に取得したデータを成型して追加します。Issueの型定義は長くなってしまうのでこの記事の最後に乗せた付録 コード全体を参照してください。
// 課題の取得と表示
const issues: Issue[] = await (await fetch(`${backlogUrl}/api/v2/issues?apiKey=${apiKey}`)).json();
dataTable.addRows(-1, issues.map(i =>
[
i.issueKey,
i.summary,
i.status.name,
i.assignee.name
]));
どうですか？課題が課題一覧テーブルに表示されましたか？
外部API呼び出し時の注意点
OfficeScriptsの外部APIの呼び出しについて2つ注意点があります。
API呼び出しはCORSの制約に縛られる
OfficeScriptsでfetch APIを利用してAPIを呼び出す場合、ブラウザーからJavaScriptを実行した場合と同様にCORSの制約に縛られます。
Backlog APIはどのオリジンからのリクエストも受け付けているため問題になりませんが、他のAPIを利用する場合はそのAPIがオリジンを超えてAPIを呼び出せるかを確認してください。CORS違反でAPIが呼び出せない場合ブラウザーの開発者ツールにエラーメッセージが表示されます。
PowerAutomateからの実行
PowerAutomateからOfficeScriptsを呼び出す場合、呼び出し先のスクリプトにfetch APIがあると呼び出しに失敗します。Office スクリプトでの外部 API 呼び出しのサポート - 外部からの外部通話Power Automateに記載がある通りセキュリティー上の制限です。「PowerAutomateを利用して定期的に外部APIを呼び出すOfficeScriptsを実行してデータを最新にする」というシナリオは現在のところ実現できません。
外部APIのデータを定期的に更新する場合はPowerAutomate側で処理を実行する必要があります。
まとめ
- OfficeScriptsではfetch関数を使って外部APIを呼び出すことができる
- テーブルを使うと取得した一覧形式のデータを簡単に処理できる
- CORSやPowerAutomateからの実行などの制限に注意する
付録 コード全体
async function main(workbook: ExcelScript.Workbook): Promise<void>
{
const sheet = workbook.getWorksheet("Sheet1");
const backlogUrl = sheet.getRange("B1").getValue();
const apiKey = sheet.getRange("B2").getValue();
const space: Space = await (await fetch(`${backlogUrl}/api/v2/space?apiKey=${apiKey}`)).json();
sheet.getRange("B4").setValue(space.spaceKey);
// テーブルの取得と初期化
const dataTable = sheet.getTable("課題一覧");
var rowCount = dataTable.getRowCount();
if (rowCount > 0) {
dataTable.deleteRowsAt(0, dataTable.getRowCount());
}
// 課題の取得と表示
const issues: Issue[] = await (await fetch(`${backlogUrl}/api/v2/issues?apiKey=${apiKey}`)).json();
console.log(issues);
dataTable.addRows(-1, issues.map(i =>
[
i.issueKey,
i.summary,
i.status.name,
i.assignee.name
]));
}
interface Space {
spaceKey: string,
name: string,
ownerId: number
lang: string,
timezone: string,
reportSendTime: string,
textFormattingRule: string,
created: string,
updated: string
}
interface IssueType {
id: number,
projectId: number,
name: string,
color: string,
displayOrder: number
}
interface Resolution {
id: number,
name: string
}
interface Priority {
id: number,
name: string
}
interface Status {
id: number,
projectId: number,
name: string,
color: string,
displayOrder: number
}
interface User {
id: number,
name: string,
roleType: number,
lang: string,
mailAddress: string
}
interface Category {
id: number,
name: string,
displayOrder: number
}
interface Version {
id: number,
projectId: number,
name: string,
description: string,
startDate: Date,
releaseDueDate: Date,
archived: boolean,
displayOrder: number
}
interface Attachment {
id: number,
name: string,
size: number
}
interface SharedFile {
id: number,
type: string,
dir: string,
name: string,
size: number,
createdUser: User,
created: Date,
updatedUser: User,
updated: Date
}
interface Star {
id: number,
comment: string,
url: string,
title: string,
presenter: User,
created: Date
}
interface Issue {
id: number,
projectId: number,
issueKey: string,
keyId: number,
issueType: IssueType,
summary: string,
description: string,
resolutions: Array<Resolution>,
priority: Priority,
status: Status,
assignee: User,
category: Array<Category>,
versions: Array<Version>,
milestone: Array<Version>,
startDate: Date,
dueDate: Date,
estimatedHours: number,
actualHours: number,
parentIssueId: number,
createdUser: User,
created: Date,
updatedUser: User,
updated: Date,
customFields
attachments: Attachment,
sharedFiles: Array<SharedFile>,
stars: Array<Star>
}