2016年2月24日水曜日

Google Apps Scriptで管理資料作成の自動化(複数スプレッドシートの情報集計)をやってみる(2/2)

前回の続きで、管理資料作成の自動化です。
このテーマの困り事とアプローチは前回を参照ください。

今回のお題は以下です。

【確認済みフォルダに移動することもしたくない】

↑今回のお題

  • 各人の作業報告書に確認済みなのか、確認未済なのか分かりたい
  • 確認済みになったら決まったフォルダーに自動で移動してほしい

これらができれば、困り事は解決できそうです。

業務の前提(前回のおさらい含む)

こんな作業報告書スプレッドシートを用意します。
https://docs.google.com/spreadsheets/d/1C1d-ycfIHlDHKiD6sMLbMX0AucFJ_nntDEdJzO-nRXA

更には、こういうフォルダ(作業報告書フォルダ)に社員が作業報告書を入れるとします。

https://drive.google.com/drive/folders/0B6KpLfrWqmytaklDWGJYSlV5QXM

↑のフォルダに入れられた作業報告書は集計されて、以下のスプレッドシートに集計データが入ります。前回の完成版です。

https://docs.google.com/spreadsheets/d/1K4TdO-Hfh7G3lNgDT_J2Nk7JZLJGZCL3DnHy0Roy5cY/edit#gid=0

今回確認済みになったら、以下のフォルダ(確認済フォルダ)に移動することを目指します。

https://drive.google.com/drive/folders/0B6KpLfrWqmytMncwY2ZQV2lFdFE


前回作った一覧作成スプレッドシートを改良します

スプレッドシートを新規作成します。シート名は勤怠管理資料とし(シート名はなんでもよいです)、スクリプトエディタを開きます。
スクリプトエディタの開き方は、「初めてのGoogle Apps Script」に記載しています。

スクリプトを書いていきます。
最初に、完成したソースコードを記載します。スクリプトエディタで全て上書きして下さい。

//作業報告書が入っているフォルダID
var reportsFolder = DriveApp.getFolderById('0B6KpLfrWqmytaklDWGJYSlV5QXM');
//勤怠管理資料のドキュメントID
var ss = SpreadsheetApp.openById('1K4TdO-Hfh7G3lNgDT_J2Nk7JZLJGZCL3DnHy0Roy5cY');
//一覧作成用のスクリプト
function importReport() {
  //作業報告書のデータを読み込む
  //読み込むデータに名前を付ける。後々、一覧シートの項目名となる
  var table = [['ID','所属', '報告日', '管理No', '氏名', '作業内容', '月間作業時間','作業報告書']];
  //作業報告書が入っているフォルダから全てのスプレッドシートを取得する
  var reports = reportsFolder.getFiles();

  //フォルダに入っているスプレッドシートの数だけ繰り返し処理を行う
  for (var i = 1; reports.hasNext(); i++) {
    //データを取得する作業報告書を開く
    var report = reports.next();
    var s = SpreadsheetApp.open(report).getSheets()[0];
 
    //valuesに作業報告書に含まれる全てのデータを格納する
    var values = s.getDataRange().getValues();
 
    //以降、一覧作成に書き込むデータを作業報告書から取得する
    
    //詳細が見たい時に簡単に内容確認できるように作業報告書へのハイパーリンクを作成する
    var link = '=HYPERLINK("' + report.getUrl() +'","作業報告書へのリンク")';
 
    //valuesからセルを指定して作業報告書のデータを取得する
    //セル位置=values[行][列]という意味で、0から始まる数字でセル位置を指定しています。
    //例えば、values[5][0]であれば、values[5]【→6行目】[0]【→A列】=A6の値を取得しなさい、となります。
    table.push([report.getId(), values[5][0], values[4][8], values[6][8], values[13][8], values[11][2], values[48][5], link]);
  }
  //勤怠管理資料に含まれるデータを初期化して全て消す
  var sheet = ss.getActiveSheet()
  sheet.clear({contentsOnly: true});

  //勤怠管理資料にデータを書き込む領域をとります
  //sheet.getRange(開始行, 開始列, 領域を取る行数, 領域を取る列数)
  var range = sheet.getRange(1, 1, table.length, table[0].length);
  //勤怠管理資料にデータを書き込みます
  range.setValues(table);
}


作業報告書スプレッドシートと報告書が入るフォルダと一覧スプレッドシートがあれば、基本的に動きます。但し、フォルダIDとドキュメントIDを書き換えて下さい。

前回作ったスクリプトに、フォルダ移動するために、各作業報告書フォルダのIDを取得するように改良します。
黄色背景部分が、前回から改良した部分です。
これで、A列にドキュメントIDが記入されます。

作業報告書を確認済フォルダに移動するスクリプトを書く

今回のお題は、以下の2点です。

  1. 各人の作業報告書に確認済みなのか、確認未済なのか分かりたい
  2. 確認済みになったら決まったフォルダに自動で移動してほしい

これを、確認済フォルダに移動するスクリプトを作ることで実現したいと思います。

右の図で示す通り、一覧は常に作業報告書
フォルダの中身を読込・集計します。

確認済フォルダに移動するスクリプトがあれば、作業報告書フォルダからファイルがなくなるため、一覧からはなくなるため、常に確認未済のファイルのみが表示されます。
これで、1のはクリアできたと見なせます。
#もちろん、他のやり方もありますが、





そこで、フォルダ移動スクリプトを書きます。
完成したソースコードは以下です。
// 未確認の作業報告書が入っているフォルダID
var reportsFolder = DriveApp.getFolderById('0B6KpLfrWqmytaklDWGJYSlV5QXM');
// 確認済の作業報告書が入っているフォルダID
var completeFolder = DriveApp.getFolderById('0B6KpLfrWqmytMncwY2ZQV2lFdFE');
function moveSelectedSpreadsheetToFolder() {
  //現在表示しているスプレッドシートから確認済フォルダに移動するID(A列の値)を取得する
  var sheet = SpreadsheetApp.getActiveSheet();
  //セルで確認済みにする作業報告書を選択する
  var selectedRow = sheet.getActiveCell().getRow();
  //選択した作業報告書のA列からIDを取得する
  var id = sheet.getRange(selectedRow, 1).getValue();

  // A列が空の場合のエラー処理
  if (!id) {
    Browser.msgBox("データが含まれる行を選択してください");
    return;
  }
  // 確認済みにするファイルを使用者にYes/Noで確認する
  var ans = Browser.msgBox(selectedRow + "行目のデータを確認済フォルダに移動します。よろしいですか?",Browser.Buttons.OK_CANCEL);
  //Yesの場合、対象のファイルを確認済フォルダに移動する
  if (ans == "ok") {
    //IDからFileを取得する
    var report = DriveApp.getFileById(id);
    //確認済の作業報告書が入っているフォルダ(CompleteFolder)に加えて、
    //未確認の作業報告書が入っているフォルダから削除する。
    completeFolder.addFile(report);
    reportsFolder.removeFile(report);
    //確認済フォルダに移動したことを利用者に知らせる
    Browser.msgBox("確認済フォルダに移動しました");
 
    //作業報告書リストを再読み込みして、最新の一覧を作成し直す
    importReport();  
 
  }
}

少し解説します

移動させる作業報告書のIDを取得します
これは、前回の集計スクリプトを改良することで、作業報告書のIDをA列に記載するようにしています。今回は、セルで移動させる作業報告書(=確認済にする作業報告書)を選択します。
function moveSelectedSpreadsheetToFolder() {
  //現在表示しているスプレッドシートから確認済フォルダに移動するID(A列の値)を取得する
  var sheet = SpreadsheetApp.getActiveSheet();
  //セルで確認済みにする作業報告書を選択する
  var selectedRow = sheet.getActiveCell().getRow();
  //選択した作業報告書のA列からIDを取得する
  var id = sheet.getRange(selectedRow, 1).getValue();
取得したIDから、フォルダ移動でいるデータ形式に変換する。
    //IDからFileを取得する
    var report = DriveApp.getFileById(id);
作業報告書を確認済フォルダに移動する。
    //確認済の作業報告書が入っているフォルダ(CompleteFolder)に加えて、
    //未確認の作業報告書が入っているフォルダから削除する。
    completeFolder.addFile(report);
    reportsFolder.removeFile(report);
勤怠管理資料を再読み込みして、最新の一覧を集計し直す。
この処理が入ることで、常に管理資料に表示されている一覧は確認未済の一覧となります。
今回改良したスクリプトを実行しています。
    //作業報告書リストを再読み込みして、最新の一覧を作成し直す
    importReport();    

実際に動かしてみます

勤怠管理資料は以下から確認可能です。
https://docs.google.com/spreadsheets/d/1K4TdO-Hfh7G3lNgDT_J2Nk7JZLJGZCL3DnHy0Roy5cY/edit#gid=0

リンクを開いてみると、以下のようなスプレットシートが表示されます。
前回同様、確認済にする、という画像からスクリプトを起動しています。


完了済にしたい作業報告書(↓だと6行目の永和二郎4さんの作業報告書)をセルで指定します。
確認済みにする、をクリックすると、6行目のデータを移動していいか、確認されます。


OKをクリックすると、以下の様に確認済フォルダに移動しました、と表示され、作業報告書が移動します。

さらに、OKボタンをクリックすることで、一覧が再読込され、

確認未済のファイルのみの一覧が作成されます。
#6行目にあった永和二郎4がなくなっています。

まとめ

前回と併せて、全2回で管理資料作成の自動化を公開しました。
今回紹介している実現方法は一例です。
もっと別のやり方もありますが、できる限りシンプルにやりたいことのみを実現することを念頭においています。別のやり方は、別途行っているハンズオンで紹介したいと考えてます。

皆さんも紹介しているスクリプトをベースに改良していってもらえれば幸いです。

それではまた、

0 件のコメント:

コメントを投稿