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

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

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

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

[kanren postid=”78286″]

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

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

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

[box class=”yellow_box”]

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

[/box]

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

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

  1. タイムシート集計(管理者用)
  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部のみを抽出します。

[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で独自メニューを追加

[/box]

管理者用・社員用の関係図は以下のとおりです。必要に応じてアレンジして使ってみてくださいね。

勤怠管理ツールの全体像

 

【連載目次:GASとスプレッドシートで作る勤怠管理ツール】

  1. GASでスプレッドシートのシフト表を自動化するには?
  2. スプレッドシートのユーザー編集権限を変更する方法
  3. GASでスプレッドシート活用術。今月日付から翌月日付を作る
  4. GASでスプレッドシートをコピー、シート内の一番左に表示する方法
  5. GASで複数のスプレッドシートに対して同じ処理を繰り返す(※サンプルファイル公開あり)
請求書の受け取りはsweeepで自動化

【AI請求書処理】従来の請求書OCRでは対応できない非定型帳票や

自動会計仕訳も、sweeepなら対応可能!最短で即日導入、

面倒な設定不要。手軽に導入して請求業務を効率化。