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

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

それではまた、

2016年1月20日水曜日

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

アナタはこんなことで困っている


あなたは、社員の労務管理をしています。社員からは、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、になります。


スクリプトを起動する際は、集計ボタンをクリックすると起動されます。
うまく起動されると、勤怠管理資料は以下のように作成されます。




今回はここまで

今回は作業報告書を読み込んで勤怠管理資料に一覧を作成するスクリプトを作りました。

次回は以下のやりたいことを実現して、管理資料作成の自動化を完成させます。

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




初めてのGoogle Apps Script

準備作業

まず、プログラムを作成する前に、準備作業を行います。
詳しくは、初心者のためのGoogle Apps Scriptプログラミング入力の中でも紹介されています。
今回は抜粋した内容を説明します。


プログラムを書く場所(プロジェクトという)を作る

新規でスプレッドシートを作成します。
作成したスプレッドシートのツールメニューから、スクリプトエディタをクリックします。



そうすると、無題のプロジェクトが作成されます。



function myfunction()の{}の中にプログラムを書いていくことになります。

試しに動かしてみる


以下のソースコードをコピー&ペーストして下さい。
全て選択して、上書きして下さい。


function HelloWorld() {
  Browser.msgBox("Hello World!");
 }


こういう状態になっているはずです。




その状態で、保存(ctrl+S)して下さい。
プロジェクト名を入力して下さい、と聞いてくるので、適当な名前をつけて下さい。

プログラムを実行してみます。
関数リストから「Hello World」を選択し、実行ボタン(▶)をクリックします。
プログラムが実行され、作成したスプレッドシートに「Hello World」と書かれたダイアログが表示されていれば成功です。


これで、プログラムを作成して、実行する準備が整いました。



2016年1月8日金曜日

はじめまして

営業がGoogle Apps Scriptを使って事務担当の困りごとを解決してみる!


こんにちは!
日頃、Google Apps Scriptを使った業務システム提案を行っている営業(私、羽根田)が、目の前にいる事務担当の困りごとをネタにGASやAppsで改善策を定期的に投稿していくブログを始めます。

GASの技術ネタではなく、現実に転がっている業務のあるあるネタに注目して活用事例を紹介していきます。
・いまの時代もうちょっとうまく仕事やれない?と思っている担当者の方
・GoogleApps導入したのに誰も使ってくれないという導入担当者の方

に向けたちょっと楽する/楽させるヒントを提供することを目標にしています。
よろしくお願いします!

はじめに


GASとは何なのか、もしくは、興味は持っているがどうやって始めたらよいか分からない方向けに、スタートアップページを紹介します。

初心者のためのGoogle Apps Scriptプログラミング入力

こちちは、GASってなに?、どうやって始める?という疑問に丁寧に解説してくれています。ざっくり、概要を知りたいかたはどうそ!私も参考にしています。

目の前にいる事務担当の困りごと、とは


このブログで登場する事務担当者(仮に庄司くん、と名づけます)の目標と困りごとをリストアップしていきます。
庄司くんは、突発作業が多い事務業務を「誰でも(標準化)/どこにいても(在宅化)/偏りなく(平準化)」を目指して事務作業を効率的(自動化)にこなす仕組みを考えているエンジニア上がりの意識高い系事務リーダーです。基本的に、タスクをシステムに管理してもらい、自分たちは目の前の仕事に集中できるようにしたいと思っています。

彼の困り事は、

  • 管理資料作成の自動化(複数スプレッドシートの情報集計) 
  • 集計作業の自動化(複数スプレッドシートの結合) 
  • 仕事依頼の平準化(フォルダ追加通知) 
  • 書類作成の自動化(情報の一元管理、差込印刷) 
  • 雑多な問い合わせ対応の工数削減(Docの目次リンクでマニュアル化) 
  • 作業漏れの防止(作業リストのカレンダー一括登録、リマインド機能) 
  • 複雑な分析資料作成の自動化 
  • 催促作業の自動化(特定のスケジュールに予定追加) 

などなど。それぞれ、解決していく様子をブログ形式でお伝えします。