LoginSignup
4
3

More than 3 years have passed since last update.

@SanQ(San Q)

javascriptのことをバックエンドと思い込んだ男子大学生がGoogle App Scriptで優勝する記事です

Posted at

初めに

　自分の勤めている職場で、GASを使用した職場内の業務効率化をするプログラムの改善を自分がした際に行ったことをこの記事で挙げます。もしGasを使ってバックエンドっぽいことをしたい方は参考にしてみてください。（バックエンドといっても、SpreadsheetをDBみたいに使うだけです。あれ、これってバックエンドて言えなくね？）

本題

今まで使用していた表の機能

  • スタッフごとに一か月間の日勤（昼間に働く）の時間、夜勤の時間、休憩時間の情報を取得し、表をGoogleSpreadsheetのシートへ作成・表示する。
  • スタッフの勤務状況はGoogleカレンダーで管理されているので、スタッフの勤務状況はGoogleカレンダーIDから取得する。
  • もし新規のスタッフの勤務状況を追加または既存のスタッフの勤務状況を削除して表を作成する際は、GASのコードへ直接追加するスタッフ名とそのカレンダーIDを追加するか、削除するスタッフ名とそのカレンダーIDを削除しなくてはいけない。⇒GASを扱える人がいないと追加と削除が難しい

改善する際の要件

  • 表を作成する際に、もしスタッフを追加または削除する場合はGASのコードへじかに書き込まなくても良い状態にする。

ではどうするか

SpreadSheatにDBの役割を持ったシートを作る。そこへ表に必要なスタッフ名とそのカレンダーIDを登録することで、もし追加または削除が起きた際でも該当者のシートへの追加または削除をすることにより対応する。

実際のコード

ただ関数の中に処理を書き込んでもよかったのですが、それだと面白くないし処理の変更にも対応しにくいので、行う処理ごとにMVC的な役割を持たせ、役割ごとに関数内の処理を定めてみました。（実際のファイル名はcode.gasですが、そのままここでコードを見せると文字の色が変わらず見づらいので、ファイル名をcode.jsにしています。）

Model

基本的にここではクライアント(表の作成者)からのデータの取得、DB周りの処理を行います。
ここで行うことは以下の７つです。

  1. DBの追加に関するクライアントからの入力値を取得する。
  2. DBの削除に関するクライアントからの入力値を取得する。
  3. クライアントからの入力を基にスタッフの追加を実行する。
  4. クライアントからの入力を基にスタッフの削除を実行する。
  5. 表の作成に関するクライアントからの入力値を取得する。
  6. 表を作成する際に必要なデータをDBから取得する。
  7. DBからスタッフ名とそのカレンダーのIDのデータを取得する。

GASでのコード

code.js
//1. DBの追加に関するクライアントからの入力値を取得する。
function getStaffInfo() {
  const name = Browser.inputBox('登録者の名前を入力してください','お名前', Browser.Buttons.OK_CANCEL);
  switch(name) {
    case 'cancel':
     Browser.msgBox("処理を終了します。");
     break;

    case "":
     Browser.msgBox("名前が空です。処理を終了します。",Browser.Buttons.OK);
     break;

    default:
      const getId = Browser.inputBox('登録者のカレンダーIDを入力してください。','ID', Browser.Buttons.OK_CANCEL);
      switch(getId) {
        case 'cancel':
          Browser.msgBox("処理を終了します。");
          break;

        case "":
          Browser.msgBox("カレンダーIDが空です。処理を終了します。",Browser.Buttons.OK);
          break;

        default:
          return [name, getId];
      }
  }
}

//2. DBの削除に関するクライアントからの入力値を取得する。
function getStaffName() {
  const name = Browser.inputBox('該当者の名前を入力してください','お名前', Browser.Buttons.OK_CANCEL);
  switch(name) {
    case 'cancel':
     Browser.msgBox("処理を終了します。");
     break;

    case "":
     Browser.msgBox("名前が空です。処理を終了します。",Browser.Buttons.OK);
     break;

    default:
     return name;
  } 
}

//3. クライアントからの入力を基にスタッフの追加を実行する。
function writeDB(sheet, array) {
  let n = 1;

  while(sheet.getRange(n, 1).getValue()) {
    n++;
    continue;
  } 

  for(let i=0; i < array.length; i++) {
         sheet.getRange(n, i+1).setValue(array[i]);
        }
}

//4. クライアントからの入力を基にスタッフの削除を実行する。
function deleteDB(sheet, name) {
  let n = 1;

  while(sheet.getRange(n, 1).getValue() !== name) {
    if(sheet.getRange(n, 1).getValue() === "") {
       Browser.msgBox("該当者がいません。処理を終了します。",Browser.Buttons.OK);
       return;
     }
      n++;
      continue;
    }
   sheet.deleteRow(n);
}

//5. 表の作成に関するクライアントからの入力値を取得する。
function getTime() {
  const year = Browser.inputBox('YYYYの形式で入力してください。例 2018','取得年', Browser.Buttons.OK_CANCEL);
  if (year == 'cancel'){
    Browser.msgBox("処理を終了します。",Browser.Buttons.OK);
    return;
  }
  if (year.match(/^20[0-9].$/) == null){
    Browser.msgBox("入力形式が違います。最初からやり直してください。",Browser.Buttons.OK);
    return;
  } else {
    const month = Browser.inputBox('MMの形式で入力してください。例 04 ','取得月', Browser.Buttons.OK_CANCEL);
    if (month == 'cancel') {
      Browser.msgBox("処理を終了します。",Browser.Buttons.OK);
      return;
    }
    if (month.match(/^(01|02|03|04|05|06|07|08|09|10|11|12)$/) == null) {
       Browser.msgBox("入力形式が違います。最初からやり直してください。",Browser.Buttons.OK);
       return;
    }
    return [year, month];
  }
 }

//6. 表を作成する際に必要なデータをDBから取得する。
function getInfoFromDB(sheet) {
  let info = {};
  let n = 1;

  while(sheet.getRange(n, 1).getValue() !== "") {
    const key = sheet.getRange(n, 1).getValue();
    const value = sheet.getRange(n, 2).getValue();
    info[key] = value;
    n++;
    continue;
  }
  return info;
}

//7. DBからスタッフ名とそのカレンダーのIDのデータを取得する。
function getResult(info, year, month) {
  let result = {}
  const startDay = new Date(year +'/'+ month + '/'+ 1);
  const lastDay = new Date(startDay.getFullYear(), startDay.getMonth() + 1,0, 0);//このままでは月の最終日が含まれない
  lastDay.setDate(lastDay.getDate()+1);//月の最終日を含める

  for(let key in info) {
       const calendar = CalendarApp.getCalendarById(info[key]);
       const events = calendar.getEvents(startDay, lastDay);
       let day = 0;
       let night = 0;
       let rest = 0;
       let money = 0;
    //スタッフごとの日勤の時間と夜勤の時間の合計を求める
     for(let i=0; i<events.length; i++) {
          const endTime = events[i].getEndTime().getHours();
          const workEnd = (events[i].getEndTime().getHours()*60 + events[i].getEndTime().getMinutes())/60;
          const workStart = (events[i].getStartTime().getHours()*60 + events[i].getStartTime().getMinutes())/60;
       //夜勤の場合
          if(endTime > 22.0) {
           day += workEnd - workStart - 1.0;
           night += workEnd - 22.0;
          } else {
       //日勤の場合
           day += workEnd - workStart;
          }
       //休憩時間の合計を求める
          const str = events[i].getDescription();//イベントの説明欄から休憩時間を取得
          const convert = str.replace(/[０-９]/gi, function(s) { return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);});//全角数字を半角文字へ変換

          switch(convert) {
            case '休憩：30分':
              rest += 0.5;
              break;

            case '休憩：1時間':
              rest += 1.0;
          }
         }
    //スタッフの月の給料を求める
       money += (day - rest)*1000 + night*1250;

       result[key] = {'day':day, 'night':night, 'rest':rest, 'money':money};    
      }
  return result;
}

View

ここでは行うのは、表をSpreadsheetへ作成することとSpreadsheetのバーに関数を登録することです。

GASでのコード

code.js
//関数の登録
function onOpen() {
  const myMenu=[
    {name: "スタッフを登録する", functionName: "addData"},
    {name: "スタッフを削除する", functionName: "deleteData"},
    {name: "勤務時間と給料の合計を出力", functionName: "resultController"}
  ];

   SpreadsheetApp.getActiveSpreadsheet().addMenu("メニュー",myMenu); 
}

//表の作成
function createResultTable(sheet, year, month, result) {
 sheet.getRange(2, 2).setValue(year+'年'+month+'月');
 sheet.getRange(3, 2).setValue('スタッフ名');
 sheet.getRange(3, 3).setValue('通常勤務時間');
 sheet.getRange(3, 4).setValue('夜間勤務時間');
 sheet.getRange(3, 5).setValue('休憩時間');
 sheet.getRange(3, 6).setValue('今月の給料');
 sheet.getRange(3, 7).setValue('※通常勤務・休憩：1000円/h、夜間勤務：1250円/1hで換算');
 sheet.getRange(3, 2, 1, 5).setBackground('#fff2cc');

 let i = 1;
 for(let key in result) {
      sheet.getRange(3+i, 2).setValue(key);
      sheet.getRange(3+i, 3).setValue(result[key]['day']);
      sheet.getRange(3+i, 4).setValue(result[key]['night']);
      sheet.getRange(3+i, 5).setValue(result[key]['rest']);
      sheet.getRange(3+i, 6).setValue(result[key]['money']);
      i++;
     }
}

Controller

ここではModelとViewの処理を結びつけます。（Controllerとして扱っても良いのかな？）

GASでのコード

code.js
//DBの追加に関するController
function addData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB');
  const info= getStaffInfo();

  if(info == null) return;

  writeDB(sheet, info);
}

//DBの削除に関するController
function deleteData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB');
  const name = getStaffName();

  if(name == null) return;

  deleteDB(sheet, name);
}

//表の作成のController
function resultController() {
 let box;
  //年と月を取得
  box = getTime();
  if(box == null) return;//
  const timeData = {'year':box[0], 'month':box[1]};

  //DBシートからスタッフの勤務データを取得
  const sheetDB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB');
  const staffData = getInfoFromDB(sheetDB);

  //記入の対象のシートを取得
  const sheet = SpreadsheetApp.getActiveSheet();

  //スタッフごとで勤務時間と給料の合計を取得
  const result = getResult(staffData, timeData['year'], timeData['month']);

  //表の作成
  createResultTable(sheet, timeData['year'], timeData['month'], result);
}

終わりに

　LaravelなどでMVCを扱おうと思ってもそんなに気軽にできないけど、GASであれば手軽＆実用的にMVC（っぽいもの）を扱えるので、バックエンドの学習の一環としてGASをやるのは面白いな～と思いました。
実際のバックエンドを触るのも面白いですが、今後もGASでWebアプリケーションとかを作ってみるなどして、お手軽疑似バックエンドで遊んでみようと思います。

4
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3