こんにちは。ITライターのもり(@moripro3)です！GoogleAppsScriptの記事をお届けしています！
連載シリーズ「GASとスプレッドシートで作る勤怠管理ツール」では、勤怠管理ツールを題材としたGASの活用事例を紹介しています。
前回の記事では、ツール処理の一部である「シートをコピーして、スプレッドシートの一番左に移動する関数」を作成しました。
[kanren postid=”78286″]シリーズを通して、これまで2つの関数を作りました。これらはツールを構成する「部品」です。
今回の記事はシリーズ最終回、これらの「部品」を組み合わせて「翌月シートの一括作成」の処理を完成させますよ。
[box class=”yellow_box”]※当シリーズの題材にした「勤怠管理ツール」のスプレッドシートを、記事の最後で公開しています！コピーしてのご利用、および、全スクリプトの閲覧が可能です！
[/box]勤怠管理ツール・翌月シートの一括作成
このシリーズで紹介する「勤怠管理ツール」は2種類のスプレッドシートで構成されています。
- タイムシート集計（管理者用）
- 勤怠表（社員用）
①と②のスプレッドシートは関数でリンクしています。
※関数の仕組みなどは、当記事の最後に公開している実物のファイルをご確認ください。
①②それぞれのスプレッドシートについて、月が変わるたびに新しいシートを作成します。2019年9月→2019年10月になる場合、「201909」シートをコピーして「201910」シートを作成します。
このコピー処理を、社員の人数分、処理します。10人の社員がいたら、10ファイルに対して同じ処理を実行するので、これをGASで自動化しちゃいましょう。
今回のテーマは「複数のスプレッドシートに対して同じ処理を繰り返す方法」です！
スプレッドシートの数だけ処理を繰り返す
管理者用・社員用それぞれのスプレッドシートについて、月が変わるたびに翌月シートを作成します。
※管理者用スプレッドシートは1ファイルのみで繰り返し処理はないため、この記事では説明を省略します
for文を使用した繰り返し処理
ポイントはfor文を使用した「繰り返し処理」です。
管理者用の集計表には、各社員の勤怠表のスプレッドシートのURLを入力しています。B列3行目～10行目に入力されているURLの分、処理を繰り返します。
セルの値（対象のURL）を取得し、もし空の場合は、continue文で処理をスキップします。
var ActiveSheet = SpreadsheetApp.getActiveSheet();
//B列3行目～10行目まで繰り返す
for (var i = 3; i <= 10; i++) {
//勤怠表のスプレッドシートのURL
var targetURL = ActiveSheet.getRange(i, 2).getValue();
if (targetURL == '') { //空白の場合はスキップ
continue;
}
/*
* シートコピーの処理を記述
*/
}
処理対象のスプレッドシートの「ID」を取得する
処理対象のスプレッドシートを指定するために、スプレッドシートのIDを取得します。
GASでは、SpreadsheetAppクラスのopenByIdメソッドでスプレッドシートを開くことができます。openByIdメソッドの引数に「スプレッドシートのID」を指定するため、ID取得が必要になります。
スプレッドシートのURLはこのような形式になってるので、このURLからID部のみを抽出します。
[box class=”yellow_box”]【URLの例】https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0
→このスプレッドシートのIDは「abc1234567」です
[/box]※管理者用スプレッドシートには、editより前の「https://docs.google.com/spreadsheets/d/abc1234567」の部分のみ入力しているものとします。
replaceメソッドで、URLの固定値を削除して、ID部のみを取得します。
//URLのID部を取得する
var strgoogle = 'https://docs.google.com/spreadsheets/d/'; //固定値
var targetId = targetURL.replace(strgoogle, '');
targetId = targetId.replace('/', ''); //末尾にスラッシュがある場合削除
これで、スプレッドシートのID部を取得できました。
処理対象のスプレッドシートを開く
SpreadsheetAppクラスのopenByIdメソッドの引数にIDを指定して、対象のスプレッドシート（勤怠表）を開きます。
[box class=”blue_box” title=”openByIdメソッド”]SpreadsheetAppクラス.openById(ID)
指定IDのスプレッドシートを開きます。
※スプレッドシートはサーバー上でのみ開かれ、クライアント側では開かれません。
[/box]コードがこちらです。開いたスプレッドシート（勤怠表）を、変数targetSSに格納します。
var targetSS = SpreadsheetApp.openById(targetId); //勤怠表のスプレッドシート
変数targetSS（Spreadsheetオブジェクト）に対してコピー処理をしていきます。
コピー元シートとシート名の取得
スプレッドシートの一番左にあるシートが、当月（最新月）のシートです。この当月シートがコピー元シートです。「コピー元のシート（Sheetオブジェクト）」と「シート名」を取得します。
[box class=”blue_box” title=”getSheetsメソッド”]Spreadsheetオブジェクト.getSheets()
スプレッドシート内の全シートのコレクション（配列）を取得します。
1番目のシートを取得する場合は、インデックスに0を指定します。
[/box]対象のシートを取得できたら、SheetオブジェクトのgetNameメソッドで「シート名」を取得します。
[box class=”blue_box” title=”getNameメソッド”]Sheetオブジェクト.getName()
シート名を取得します。
[/box]2つのメソッドを使用して、「コピー元のシート（Sheetオブジェクト）」と「シート名」を取得します。
var thisMonthSheet = targetSS.getSheets()[0]; //勤怠表の1番目のシート var thisMonthSheetName = thisMonthSheet.getName(); //シート名を取得
翌月シートを作成する
「当月シート」をコピーして「翌月シート」を作成する処理です。シリーズを通して作成した2つの関数をここで使用します。
- createNextYYYYMM関数
→「当月の年月（YYYYMM）」から「翌月の年月（YYYYMM）」を作成する関数
（例）201909→201910
- CopySheet関数
→シートをコピーして、スプレッドシートの一番左に移動する関数
//翌月のシート名・年・月を取得
var [nextMonthSheetName, year, month] = createNextYYYYMM(thisMonthSheetName);
//当月シートをコピーして翌月シートを作成
var nextMonthSheet = CopySheet(thisMonthSheet,targetSS,nextMonthSheetName);
[box class=”yellow_box” title=”ポイント”]
シリーズ3回目の記事で作成した「createNextYYYYMM関数」は3つの変数「年月」「年」「月」を配列で返します。
呼び出し側も、変数を配列形式で指定することで、それぞれの変数に返り値が格納されます。
[/box]セルの年月設定と前回値のクリア
最後の処理です。セルの年月を設定します。
nextMonthSheet.getRange(5, 1).setValue(year); nextMonthSheet.getRange(5, 2).setValue(month);
先月シートをコピーして新しいシートを作成したので、今月の値を入力できるよう、先月入力値をクリアします。
var startRow = 11; var rowCnt = 31; nextMonthSheet.getRange(startRow, 3, rowCnt, 4).clearContent(); //C11~F41 nextMonthSheet.getRange(startRow, 12, rowCnt, 11).clearContent(); //L11~V41
スクリプトまとめ
この記事で作成したスクリプトのまとめです。
/*
* 各社員用の「勤怠表」の当月シートをコピーして翌月シートを作成する
*/
function Copy_EmployeeSheet() {
var ActiveSheet = SpreadsheetApp.getActiveSheet();
//B列3行目～10行目まで繰り返す
for (var i = 3; i <= 10; i++) {
//勤怠表のスプレッドシートのURL
var targetURL = ActiveSheet.getRange(i, 2).getValue();
if (targetURL == '') { //空白の場合はスキップ
continue;
}
//URLのID部を取得する
var strgoogle = 'https://docs.google.com/spreadsheets/d/'; //固定値
var targetId = targetURL.replace(strgoogle, '');
targetId = targetId.replace('/', ''); //末尾にスラッシュがある場合削除
var targetSS = SpreadsheetApp.openById(targetId); //勤怠表のスプレッドシート
var thisMonthSheet = targetSS.getSheets()[0]; //勤怠表の1番目のシート
var thisMonthSheetName = thisMonthSheet.getName(); //シート名を取得
//翌月のシート名・年・月を取得
var [nextMonthSheetName, year, month] = createNextYYYYMM(thisMonthSheetName);
//当月シートをコピーして翌月シートを作成
var nextMonthSheet = CopySheet(thisMonthSheet,targetSS,nextMonthSheetName);
//セルの年月の設定
nextMonthSheet.getRange(5, 1).setValue(year);
nextMonthSheet.getRange(5, 2).setValue(month);
//先月入力値のクリア
var startRow = 11;
var rowCnt = 31;
nextMonthSheet.getRange(startRow, 3, rowCnt, 4).clearContent(); //C11~F41
nextMonthSheet.getRange(startRow, 12, rowCnt, 11).clearContent(); //L11~V41
}
}
まとめ：GASとスプレッドシートで作る勤怠管理ツール
GASで「複数のスプレッドシートに対して同じ処理を繰り返す方法」を紹介しました。
当シリーズの題材にした「勤怠管理ツール」のサンプルファイルを公開します。スプレッドシートの関数もご確認ください。
[box class=”yellow_box”]※下記ファイルをコピーしてお使いください。「ツール → スクリプトエディタ」でスクリプトも見れるようになります。
②勤怠表（社員用）サンプルファイル ※社員の人数分必要です
※独自メニューからスクリプト実行が可能です。
管理者用・社員用の関係図は以下のとおりです。必要に応じてアレンジして使ってみてくださいね。
【連載目次：GASとスプレッドシートで作る勤怠管理ツール】
