アナタはこんなことで困っている
あなたは、社員の労務管理をしています。社員からは、Excelの月間作業資料を渡されて、中身を確認し、問題なければ確認済みのフォルダに入れています。毎月同じ作業を繰り返していて、社員が増える毎に労務管理作業にかかる時間が増えてきています。
この課題を、Google Apps Scriptで解決できないか、と考えています。
アプローチ方法を考えてみる
Google Apps Scriptで何がやりたいかざっくり考えてみます。
- いちいち全ての作業報告書を見なくても良いようにしたい。一覧でみたい。
- 確認済みフォルダに移動することもしたくない
それぞれ、Google Apps Scriptで何ができればよいかを考えてます。
【いちいち全ての作業報告書を見なくても良いようにしたい。一覧でみたい。】
- 作業報告書スプレッドシートを読み込んで、一覧シートに必要な情報を書き出す
- 詳細が見たい時に、簡単に見れるようにしたい
【確認済みフォルダに移動することもしたくない】
- 各人の作業報告書に確認済みなのか、確認未済なのか分かりたい
- 確認済みになったら決まったフォルダーに自動で移動してほしい
これらができれば、困り事は解決できそうです。
まずは、一覧で見れるようにしてみる
こんな作業報告書スプレッドシートを用意します。
更には、こういうフォルダに社員が作業報告書を入れるとします。
一覧スプレッドシートを作成します。
スプレッドシートを新規作成します。シート名は勤怠管理資料とし（シート名はなんでもよいです）、スクリプトエディタを開きます。
スクリプトエディタの開き方は、「初めてのGoogle Apps Script」に記載しています。
スクリプトを書いていきます。
最初に、完成したソースコードを記載します。スクリプトエディタで全て上書きして下さい。
作業報告書スプレッドシートと報告書が入るフォルダと一覧スプレッドシートがあれば、基本的に動きます。但し、フォルダIDとドキュメントIDを書き換えて下さい。
スクリプトの起動方法は、後述しています。
//作業報告書が入っているフォルダID
var reportsFolder = DriveApp.getFolderById('フォルダIDに書き換える');
//勤怠管理資料のドキュメントID
var ss = SpreadsheetApp.openById('ドキュメントIDに書き換える');
//一覧作成用のスクリプト
function importReport() {
//作業報告書のデータを読み込む
//読み込むデータに名前を付ける。後々、一覧シートの項目名となる
var table = [['所属', '報告日', '管理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([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はURLから取得することができます。
・フォルダを開いて、URLの以下の部分がフォルダID
・勤怠管理資料を開いて、URLの以下の部分がドキュメントID
取得したIDで以下を書き換えて下さい。
//作業報告書が入っているフォルダID
var reportsFolder = DriveApp.getFolderById('フォルダIDに書き換える');
//勤怠管理資料のドキュメントID
var ss = SpreadsheetApp.openById('ドキュメントIDに書き換える');
フォルダの中に入っている情報を取得します。
table.push([values[5][0], values[4][8], values[6][8], values[13][8], values[11][2], values[48][5], link]);
作業報告書では情報位置は決まっているので、セル位置を指定してデータを取得しています。
セル位置＝values[行位置][列位置]
となっていて、行位置、列位置は0から始まる数字で位置を表現しています。
行であれば、
0→1行目
1→2行目
・・・
列であれば、
0→A列
1→B列
・・・
となります。
以下のvalues[5][0]であれば、[5]が6行目、[0]がA列を表現していて、A6からデータを取得することを意味しています。
次に一覧シートを書き出します。
//勤怠管理資料にデータを書き込む領域をとります
//sheet.getRange(開始行, 開始列, 領域を取る行数, 領域を取る列数)
var range = sheet.getRange(1, 1, table.length, table[0].length);
//勤怠管理資料にデータを書き込みます
range.setValues(table);
スプレッドシートでは、書き込む領域をgetRangeで確保して、その領域に対してsetValueでデータを書き込みます。
最後に、スプレッドシートからスクリプトを動かせるようにします
簡易的に、スプレッドシートからクリックできるボタンを用意します。
スプレッドシートの挿入メニューから図形描画をクリックして、適当な図形をスプレッドシートに挿入します。
ここでは、四角図形を選択して、集計というテキストを入力しています。
挿入した図形を右クリックするとスクリプトを割り当てを選択して、スクリプト名（functionに続く文字列）を記入します。
今回の一覧作成用のスクリプトは以下なので、
function importReport() {
割り当てるスクリプト名はimportReprt、になります。
スクリプトを起動する際は、集計ボタンをクリックすると起動されます。
うまく起動されると、勤怠管理資料は以下のように作成されます。
今回はここまで
今回は作業報告書を読み込んで勤怠管理資料に一覧を作成するスクリプトを作りました。
次回は以下のやりたいことを実現して、管理資料作成の自動化を完成させます。
【確認済みフォルダに移動することもしたくない】
- 各人の作業報告書に確認済みなのか、確認未済なのか分かりたい
- 確認済みになったら決まったフォルダーに自動で移動してほしい
