Office スクリプトでシートを複製し Power Automate で運用

Power Automate で Excel のタブを複製するには？

Power Automate で Excel の操作を行おうとするとテーブルを作成し原則そのテーブルしか触ることができません。既存の Excel ファイル等でテーブルを適切に運用し使用しているファイルなんて見たことがないのは、テーブルなんて作らなくても普段の使用には何の問題も発生しないからだろうと思います。
確かにテーブルは便利です。行の追加等をコピペで実行し計算式の入力された行を追加しながらデータの蓄積を行うような場合には勝手に適切な行を増やしてくれるテーブルは非常に便利だと思います。逆に計算式が適切にすべての行に配置されるわけではない、セルの結合等の見た目で体裁を整えたい場合には使いづらいのも事実だと思います。

既存の Excel でよくある運用が「原本」シートを作っておいて月ごとに原本シートをコピーしてシートを増やしていくような運用方法です。シート内で様々な体裁を作っておき適切に計算式を入力して全体を構成しているような場合、記入済みのシートをコピーしてデータの入った行を削除し改めて計算式や体裁をやり直すよりも、データが入っていない空の体裁済みシートをコピーして使用するほうが圧倒的に楽なためだと推察します。

しかし、 Power Automate ではシートのコピーを簡単に実行できません。理由は分からないし今後はできるようになるのかもしれませんが、現時点でシートを丸ごとコピーする方法がないため別の手段を取るしかありません。そこで Power Automate からも実行が可能な Office スクリプトを利用しシートのコピーを自動実行させてみることとしました。

Office スクリプトを作成する

Office スクリプトは Excel 上から簡単に作成できます。作成された Office スクリプトは Windows のドキュメント内に「 Office Scripts 」のディレクトリが作成され保存されるため、 OneDrive でドキュメントフォルダがバックアップ対象になっていれば Power Automate 上から呼び出すのも比較的簡単だと思います。

Office スクリプトを作成する際には最初は操作の記録で作成するのが手っ取り早いと思います。実際に操作したい Excel ファイルがスクリプトの入っていない拡張子 .xlsx の場合でも、一度該当のファイルをコピーしてスクリプトを保存できる .xlsm のファイルを作成し、検証を含めそちらの Excel ファイルを利用してスクリプトの作成を行うと元の原本ファイルに影響を及ぼさないので適切かなと個人的には思っています。

まずはメニューの「自動化」タブから「操作を記録」をクリックしてコードの自動作成を行ってみます。

操作を記録のウインドウが開いて「記録中」となるので、原本シートを複製しシート名を変更してみました。

現在のシートはこんな感じです。

これで「停止」をクリックすると Office スクリプトが自動で作成されます。

「コードの編集」をクリックすれば実際のコードが確認できます。

実際にはこのコードをベースにコードを書き換えていろんな実装を行うのですが、コードの詳細についてはそこまで詳しいわけではないため割愛させて頂こうと思います。

Office スクリプトを編集しシートを複製するコード

実際に運用で使用しているコードを提示してみようと思います。何度か繰り返し自動実行を行わせては不具合を解消してきてはいますが、まだ完全に動作しているとは言い難いです…
「原本」シートの前提条件としては以下。セルの位置等を適切に書き換えればある程度実行可能だと思います。

1. セル「 I3 」に年を「 L3 」に月の数値を入力する欄が設けてある
2. Power Automate で操作することを前提にテーブルが作成してあり「メイン_1」のような名前が付与されている
3. 原本シートをコピーしたのち、新しいシートには翌月の「年月」のシート名に書き換え、テーブルの名前も「メイン_年月」に書き換えている

以下が実際のコードです。

function main(workbook: ExcelScript.Workbook) {
	let nextMonthly: Date = new Date();
	let lastDayOfMonth: Date = new Date(nextMonthly.getFullYear(), nextMonthly.getMonth() + 1, 0);
 
	// 年末だったら翌年1月のシートを作成
	if (nextMonthly.getMonth() == 11) {
		let sheetName: string = (nextMonthly.getFullYear() + 1) + "年1月";
	} else {
		// 日付を比較して月末を判定
		if (nextMonthly.getDate() == lastDayOfMonth.getDate()) {
			// getMonth は 0 ～ 11 なので来月は +2
			let month: string = nextMonthly.getMonth() + 2
		} else {
			// getMonth は 0 ～ 11 なので今月は +1
			let month: string = nextMonthly.getMonth() + 1
		}
		let sheetName: string = nextMonthly.getFullYear() + "年" + month + "月";
	}
	console.log(sheetName)
	let createddSheet: ExcelScript.Worksheet = workbook.getWorksheet(sheetName);

	if (createddSheet) {
		console.log(sheetName)
		console.log("すでにシートがあります！");
		return;
	}

	// 年末だったら翌年のリストネームを作成
	if (nextMonthly.getMonth() == 11) {
		let nextYear: string = (nextMonthly.getFullYear() + 1);
		let listName: string = "メイン_" + nextYear + "01";
	} else {
		let nextYear: string = nextMonthly.getFullYear();
		let listName: string = "メイン_" + nextYear + (("0" + month).slice(-2));
	}
	// Duplicate worksheet
	let selectedSheet: ExcelScript.Worksheet = workbook.getWorksheet("原本");
	let newSheet = selectedSheet.copy(ExcelScript.WorksheetPositionType.before, selectedSheet);
	// Rename worksheet
	newSheet.setName(sheetName);
	// Set range I3
	newSheet.getRange("I3").setValue(nextYear);
	// Set range L3
	newSheet.getRange("L3").setValue(month);
	// Rename table
    let TableName: string = newSheet.getTables()[0].getName();
	let newTable = workbook.getTable(TableName);
	newTable.setName(listName);
}

Office スクリプトの簡単な説明

翌月のデータを取得するため現在の日付データを取得します。

let nextMonthly: Date = new Date();

console.logを利用するとデータの内容がログに書き出されます。これは Power Automate の実行ログにも表示されるので例えばconsole.log(nextMonthly)とした場合

{
  "logs": [
    "[2024-07-03T03:41:59.1610Z] \"2024-07-03T03:41:59.116Z\"",
  ]
}

のようなログが記録されます。

月末を取得します。

let lastDayOfMonth: Date = new Date(nextMonthly.getFullYear(), nextMonthly.getMonth() + 1, 0);

ここで注意が必要なのはnew Date()で月を指定する場合1 ～ 12ではなく0 ～ 11であると言うことです。毎月月末に翌月のシートを追加しようとして何度も躓きました…
月末を判定するために取得した現在の日付データから月のデータを+1してnextMonthly.getMonth() + 1としています。日の部分を0とすることで月末が取得できます。
※ 資料を確認すると基準が1で負の数を指定すると遡った日付になる、0を指定すると前月の最終日となると読めるのですが、上記コード指定では当月の最終日が取得できています。なぜだろう…
Power Automate で上記ログを表示するとこんな感じです。

{
  "logs": [
    "[2024-07-03T03:41:59.1900Z] \"2024-07-31T00:00:00.000Z\"",
  ]
}

年末かどうかを判定してシート名を作成します。

if (nextMonthly.getMonth() == 11) {
	let sheetName: string = (nextMonthly.getFullYear() + 1) + "年1月";
}

手動実行する可能性も考えて月末の実行であるかどうかを判定してシート名を作成します。

if (nextMonthly.getDate() == lastDayOfMonth.getDate()) {
   // getMonth は 0 ～ 11 なので来月は +2
   let month: string = nextMonthly.getMonth() + 2
} else {
   // getMonth は 0 ～ 11 なので今月は +1
   let month: string = nextMonthly.getMonth() + 1
}
let sheetName: string = nextMonthly.getFullYear() + "年" + month + "月";

念のためすでに同じシート名が存在していたら処理を終了します。

let createddSheet: ExcelScript.Worksheet = workbook.getWorksheet(sheetName);

if (createddSheet) {
   console.log(sheetName)
   console.log("すでにシートがあります！");
   return;
}

年末であるかどうかを判定してテーブルに付与するネームを生成します。

if (nextMonthly.getMonth() == 11) {
   let nextYear: string = (nextMonthly.getFullYear() + 1);
   let listName: string = "メイン_" + nextYear + "01";
} else {
   let nextYear: string = nextMonthly.getFullYear();
   let listName: string = "メイン_" + nextYear + (("0" + month).slice(-2));
}

月の部分は 2 桁に合わせたいので("0" + month).slice(-2) として月のデータに0を足して右から 2 桁を取得することで桁合わせを行っています。

「原本」シートのコピーを実行します。

let selectedSheet: ExcelScript.Worksheet = workbook.getWorksheet("原本");
let newSheet = selectedSheet.copy(ExcelScript.WorksheetPositionType.before, selectedSheet);

コピーされたシートのそれぞれの項目を修正します。
必要に応じてセルの位置を変更したりすれば任意の形式でも流用可能かと思います。

// Rename worksheet
newSheet.setName(sheetName);
// Set range I3
newSheet.getRange("I3").setValue(nextYear);
// Set range L3
newSheet.getRange("L3").setValue(month);

テーブルの名前を修正します。この場合シート内にテーブルは 1 つしかない前提で取得していますので、複数のテーブルが存在している場合には注意が必要です。

// Rename table
let TableName: string = newSheet.getTables()[0].getName();
let newTable = workbook.getTable(TableName);
newTable.setName(listName);

まとめ

getMonth()で月の表現が0 ～ 11だと気づかずに何度も失敗を繰り返してしまいましたが、 Power Automate で月末にスクリプトを実行することで翌月のシートを自動生成させることに成功はしています。同様に Office スクリプトで少し幅の広い自動実行ができそうな感じなのでいろいろと検証は行ってみたいなと思います。