GASで複数のスプレッドシートに対して同じ処理を繰り返すには？

業務改善

こんにちは。ITライターのもり(@moripro3)です！GoogleAppsScriptの記事をお届けしています！

連載シリーズ「GASとスプレッドシートで作る勤怠管理ツール」では、勤怠管理ツールを題材としたGASの活用事例を紹介しています。

前回の記事では、ツール処理の一部である「シートをコピーして、スプレッドシートの一番左に移動する関数」を作成しました。

シリーズを通して、これまで2つの関数を作りました。これらはツールを構成する「部品」です。

「当月の年月（201909）」から「翌月の年月（201910）」を作成する関数（シリーズ3記事目）
シートをコピーしてスプレッドシート内の一番左に移動する関数（シリーズ4記事目）

今回の記事はシリーズ最終回、これらの「部品」を組み合わせて「翌月シートの一括作成」の処理を完成させますよ。

※当シリーズの題材にした「勤怠管理ツール」のスプレッドシートを記事の最後で公開しています！コピーしてのご利用、および、全スクリプトの閲覧が可能です！

1 勤怠管理ツール・翌月シートの一括作成
2 スプレッドシートの数だけ処理を繰り返す
3 スクリプトまとめ
4 まとめ：GASとスプレッドシートで作る勤怠管理ツール

勤怠管理ツール・翌月シートの一括作成

このシリーズで紹介する「勤怠管理ツール」は2種類のスプレッドシートで構成されています。

タイムシート集計（管理者用）
勤怠表（社員用）

①と②のスプレッドシートは関数でリンクしています。

※関数の仕組みなどは、当記事の最後に公開している実物のファイルをご確認ください。

①②それぞれのスプレッドシートについて、月が変わるたびに新しいシートを作成します。2019年9月→2019年10月になる場合、「201909」シートをコピーして「201910」シートを作成します。

GASで翌月用にシートをコピーする

このコピー処理を、社員の人数分、処理します。10人の社員がいたら、10ファイルに対して同じ処理を実行するので、これをGASで自動化しちゃいましょう。

今回のテーマは「複数のスプレッドシートに対して同じ処理を繰り返す方法」です！

スプレッドシートの数だけ処理を繰り返す

管理者用・社員用それぞれのスプレッドシートについて、月が変わるたびに翌月シートを作成します。

勤怠管理ツール自動化2

※管理者用スプレッドシートは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部のみを抽出します。

【URLの例】https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0

→このスプレッドシートのIDは「abc1234567」です

※管理者用スプレッドシートには、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を指定して、対象のスプレッドシート（勤怠表）を開きます。

SpreadsheetAppクラス.openById(ID)

指定IDのスプレッドシートを開きます。

※スプレッドシートはサーバー上でのみ開かれ、クライアント側では開かれません。

コードがこちらです。開いたスプレッドシート（勤怠表）を、変数targetSSに格納します。

var targetSS = SpreadsheetApp.openById(targetId); //勤怠表のスプレッドシート

変数targetSS（Spreadsheetオブジェクト）に対してコピー処理をしていきます。

コピー元シートとシート名の取得

スプレッドシートの一番左にあるシートが、当月（最新月）のシートです。この当月シートがコピー元シートです。「コピー元のシート（Sheetオブジェクト）」と「シート名」を取得します。

Spreadsheetオブジェクト.getSheets()

スプレッドシート内の全シートのコレクション（配列）を取得します。

1番目のシートを取得する場合は、インデックスに0を指定します。

対象のシートを取得できたら、SheetオブジェクトのgetNameメソッドで「シート名」を取得します。

Sheetオブジェクト.getName()

シート名を取得します。

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);

シリーズ3回目の記事で作成した「createNextYYYYMM関数」は3つの変数「年月」「年」「月」を配列で返します。

呼び出し側も、変数を配列形式で指定することで、それぞれの変数に返り値が格納されます。

セルの年月設定と前回値のクリア

最後の処理です。セルの年月を設定します。

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
    
  }
  
}