最近Excelの細かな操作について聞かれることが多いので、メモ。
Office スクリプト（Office Scripts）
Office スクリプトは、Excel Onlineでタスクを自動化できるTypeScriptベースのプログラミング機能。
ユーザーはコードを記録・編集して複雑なデータ処理を自動化でき、Power Automateと連携することでより広範なワークフローの自動化も実現可能。
今回はその中のシートへの操作をまとめ。
事前準備
今回はSharePoint上のExcelに対し、操作を実施していく。
Officeスクリプトは[自動化]→[新しいスクリプト]から編集が可能で、
Power Automateからは[スクリプトの実行]アクションで呼び出す。
シートの作成
シートの作成はWorkbookの「addWorksheet」関数を使う。
function main(workbook: ExcelScript.Workbook, sheetName: string) {
// 同名のシートが既に存在するかチェック
if (workbook.getWorksheet(sheetName)) {
console.log(`${sheetName} という名前のシートは既に存在します`);
return;
}
// 新しいシートを作成
const newSheet = workbook.addWorksheet(sheetName);
// 作成したシートをアクティブにする
newSheet.activate();
console.log(`${sheetName} という名前の新しいシートを作成しました`);
}
Power Automateから実行すると、指定の名前のシートが作成される。
シートの名前変更
シートの名前の変更は、Worksheetの「setName」関数を使用する。
function main(workbook: ExcelScript.Workbook, currentSheetName: string, newSheetName: string) {
// 対象のシートを取得
const worksheet = workbook.getWorksheet(currentSheetName);
// 対象のシートが存在しない場合
if (!worksheet) {
console.log(`${ currentSheetName } というシートが見つかりません`);
return;
}
// 変更後の名前が既に存在する場合
if (workbook.getWorksheet(newSheetName)) {
console.log(`${ newSheetName } という名前のシートは既に存在します`);
return;
}
// シートの名前を変更
worksheet.setName(newSheetName);
console.log(`シート名を ${ currentSheetName } から ${ newSheetName } に変更しました`);
}
Power Automateから実行するとこんな感じ。
シートのコピー
シートのコピーはWorksheetのcopy関数を使用する。
※引数に?をつけると、任意の引数にすることも可能。
※引数に?をつけると、任意の引数にすることも可能。
function main(workbook: ExcelScript.Workbook, sourceSheetName: string, newSheetName?: string) {
// 元のシートを取得
const sourceSheet = workbook.getWorksheet(sourceSheetName);
// 元のシートが存在しない場合
if (!sourceSheet) {
console.log(`${sourceSheetName} というシートが見つかりません`);
return;
}
// シートをコピー（デフォルトでは末尾に配置）
const copiedSheet = sourceSheet.copy(ExcelScript.WorksheetPositionType.end);
// 新しいシート名を設定
if (newSheetName) {
// 指定されたシート名が既に存在する場合（コピー後のシート名と異なる場合のみチェック）
if (newSheetName !== copiedSheet.getName() && workbook.getWorksheet(newSheetName)) {
console.log(`${newSheetName} という名前のシートは既に存在します`);
return;
}
// 指定された名前に変更
copiedSheet.setName(newSheetName);
}
console.log(`${sourceSheetName} を複製して ${copiedSheet.getName()} を作成しました`);
}
Power Automateから新しいシート名を指定せずに実行すると、Excelが付けてくれた名前で複製される。
シートの移動
シートを移動する際は、WorksheetのsetPosition関数を使用する。
先頭へ移動
先頭へ移動する際は、引数に0を指定。
function main(workbook: ExcelScript.Workbook, sheetName: string) {
// 対象のシートを取得
const sheet = workbook.getWorksheet(sheetName);
// シートが存在しない場合
if (!sheet) {
console.log(`${sheetName} というシートが見つかりません`);
return;
}
// シートを先頭に移動（位置0が先頭）
sheet.setPosition(0);
console.log(`${sheetName} を先頭に移動しました`);
}
実行結果。
末尾へ移動
末尾に移動する際は、シートの数を取得し、その-1を指定する。
function main(workbook: ExcelScript.Workbook, sheetName: string) {
// 対象のシートを取得
const sheet = workbook.getWorksheet(sheetName);
// シートが存在しない場合
if (!sheet) {
console.log(`${sheetName} というシートが見つかりません`);
return;
}
// ワークブック内のシート数を取得
const worksheetCount = workbook.getWorksheets().length;
// シートを末尾に移動（シート数-1が末尾の位置）
sheet.setPosition(worksheetCount - 1);
console.log(`${sheetName} を末尾に移動しました`);
}
特定のシートの前へ移動
特定のシートの前に移動する際は、対象のシートのポジション（整数）を取得し、そのポジションを引数に指定する。
function main(workbook: ExcelScript.Workbook, targetSheetName: string, referenceSheetName: string) {
// 移動させるシートを取得
const targetSheet = workbook.getWorksheet(targetSheetName);
// 基準となるシートを取得
const referenceSheet = workbook.getWorksheet(referenceSheetName);
// 対象のシートが存在しない場合
if (!targetSheet) {
console.log(`${targetSheetName} というシートが見つかりません`);
return;
}
// 基準シートが存在しない場合
if (!referenceSheet) {
console.log(`${referenceSheetName} というシートが見つかりません`);
return;
}
// 基準シートの位置を取得
const referencePosition = referenceSheet.getPosition();
// ターゲットシートを基準シートの前に移動
targetSheet.setPosition(referencePosition);
console.log(`${targetSheetName} を ${referenceSheetName} の前に移動しました`);
}
実行結果。
シートの削除
シートの削除はWorksheetのdelete関数を使用する。
function main(workbook: ExcelScript.Workbook, sheetName: string) {
// 削除するシートを取得
const sheet = workbook.getWorksheet(sheetName);
// シートが存在しない場合
if (!sheet) {
console.log(`${sheetName} というシートが見つかりません`);
return;
}
// シートを削除
sheet.delete();
console.log(`${sheetName} を削除しました`);
}
実行結果。
おまけ：エラーを検知したい場合
Office Scriptはresultというオブジェクトに戻り値を詰めて、Power Automate側で操作失敗時の処理を書くことができる。
例えば以下のコードではboolの「success」とstringの「message（エラーメッセージ）」をPower Automateに返すことを宣言していて、
function main(workbook: ExcelScript.Workbook, sheetName: string): { success: boolean, message: string } {
try {
// シート名が空かどうかチェック
if (!sheetName || sheetName.trim() === "") {
return {
success: false,
message: "シート名が指定されていません"
};
}
// 同名のシートが既に存在するかチェック
if (workbook.getWorksheet(sheetName)) {
return {
success: false,
message: `${sheetName} という名前のシートは既に存在します`
};
}
// 新しいシートを作成
const newSheet = workbook.addWorksheet(sheetName);
// 作成したシートをアクティブにする
newSheet.activate();
// 成功の場合
return {
success: true,
message: `${sheetName} という名前の新しいシートを作成しました`
};
} catch (error) {
// 予期しないエラーが発生した場合
return {
success: false,
message: `エラーが発生しました: ${error.message || error}`
};
}
}
Power Automateの「条件」アクションなどで戻り値を参照し、エラー時の動作を構築することも可能。
コメント