Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kuma_radcof(takuma koike)

【予約管理編】Google Apps Scriptで作る予実管理システム

Posted at

Google Apps Scriptで作るスプレッドシートの予実管理システムについて
今回は 【予約管理】 編となります

予約管理ファイルでのプログラムに関して肝は以下の点です。

  • 業務メニューを入力するとその下が自動で色分けされる
  • フォントや列幅が毎日リセットされる
  • 非定型での料金徴収分を含めて日毎の売り上げ実績を算出できるようにする

この予約管理については、当初作り始める段階ではもっとUIというか予約の確定方法を別のものにしたかったのですが、断念しました。
どうして別のものにしたかったのかというと、予約の取得・管理方法がスマホでの操作になっていたため、スプレッドシートの列操作（横移動）を最小限にした方が効率いいんじゃない？と思ったためです。
ただこれは「私がやりたい方法」であって「クライアントが求める方法」ではなかったため断念になりました。
こちらにとっての使いやすさは、相手にとっての使いやすさとはならないんだということを理解できた一件です。

さて話は本題に戻り、プログラムについてです。
いくつか関数・処理を用意しているのですが、

以下は自動での色分けがされるようなプログラムです。

function onEdit(){
  var activesheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var sheet_color = activesheet.getTabColor();

  if(sheet_color == null){  //シートの色が無色の時に処理継続、赤シートなどは処理中断
    var activecell = activesheet.getActiveCell();
    var activeRow = activecell.getRow();
    var activeCol = activecell.getColumn();
      // console.log(activeRow, activeCol);  //現在セル番地を数値で取得
      // return
    var inset_data = activesheet.getRange(activeRow, activeCol).getValue();
      // console.log(activesheet.getRange(activeRow,activeCol,3,1).getBackground());
      // return
    
    /** 予約枠の処理を行う（色付けを行う）行について */
    // 開始行を見つける：『担当』のセルがある行番号を見つける
    for(i=0; i<10; i++){
      if(activesheet.getRange(i+1,2).getValue() == "担当"){
        var sr = i+2;
      }
    }
    for(j=30; j<60; j++){
      var txt = activesheet.getRange(j,1).getValue();
      if(txt.match(/(施設支払)/)){
        var er = j-1;
      }
    }
    /**ここまでが予約枠の行数を調べる処理*/
    
    if(activeRow >= sr && activeRow <= er){
      if(inset_data.match(/[^\x01-\x7E]/) && activesheet.getRange(activeRow,activeCol).getBackground() != "#ffffff"){
        return
      }
      /**
       * 各予約枠を入力した時の処理
       * ・時間／担当者を基本として入力、時間については全角文字を入力するcaseもありうる（というかこっちがメイン）
       * ・時間がはいっている部分については白背景のまま
       * ・予約枠の下行について、予約項目に応じて色分け、色付けセル数の変更を行う
       * */ 

      var nextCell = activesheet.getRange(activeRow+1,activeCol);
      /** メニューAを入力した時の挙動 */
      if(inset_data.match(/(メニューA)/) != null){
        if(nextCell.getValue() == "" && nextCell.getBackground() == "#ffffff"){ //したのセルが空欄かつ、背景色が白の時=予約が入る状態
          activesheet.getRange(activeRow+1,activeCol,1,1).setBackground("#00ff00"); 
        } 
        return
      }

      /** メニューBを入力した時の挙動 */
      if(inset_data.match(/メニューB/) != null){
        if(nextCell.getValue() == "" && nextCell.getBackground() == "#ffffff"){
          activesheet.getRange(activeRow+1,activeCol,2,1).setBackground("#ffff00");
        }
        return
      }
 
        var top = activesheet.getRange(activeRow,activeCol).getBorder().getTop().getBorderStyle();
        var right = activesheet.getRange(activeRow,activeCol).getBorder().getRight().getBorderStyle();
        var left = activesheet.getRange(activeRow,activeCol).getBorder().getLeft().getBorderStyle();
        var bottom = activesheet.getRange(activeRow,activeCol).getBorder().getBottom().getBorderStyle();
        var rangeData = activesheet.getActiveRange().getValues().flat().join();
      
      if(activecell.getValue().match(/[^,]/) == null){
        if(activecell.getBackground() != "#ffffff"){
          return
        }
        if(activesheet.getRange(activeRow+1,activeCol,2,1).getBackgrounds().flat().join() == '#ffff00,#ffff00') { //黄色の時
          activesheet.getRange(activeRow+1,activeCol,2,1).setBackground(null);
        } else if(nextCell.getBackground() == "#00ffff"){ //水色の時
          activesheet.getRange(activeRow+1,activeCol).setBackground(null);
        } else if(nextCell.getBackground() == "#00ff00"){ //緑の時
          activesheet.getRange(activeRow+1,activeCol).setBackground(null);
        }
      }
      //アクティブなデータ範囲に何か任意の文字が入力されていた時に、処理を継続・中断するメソッド
            if(top == "SOLID" && right == "SOLID" && left == "SOLID" && bottom == "SOLID" ){
              if(rangeData.match(/[^,]/) == null){  //カンマ以外の何かがマッチしない＝カンマしかない＝セルは全て空欄
                return
              }
              else{
                return
              }
            }
      return
    } //7行目以降の処理について条件分岐終了
  } //シート無色の時の条件分岐終了
}

今見返すと無理矢理感がかなり強いのですが、これで動作しているので良いでしょう。
毎日同時に複数人が操作する環境であってもonEdit関数を利用しての処理が何とかなるということがわかりました。

以下がフォントや列幅のリセット処理です。

予約管理.gs
    function DailyCssReset(){
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      var visible_sheets = []
      sheets.forEach(function(sheet){
        if(sheet.isSheetHidden() == false){
            // 実績取得対象月のシートだけを配列に用意する
            // 実績対象月の確認
            const now = new Date();
            let month = now.getMonth() + 1;
            let year = Utilities.formatDate(now,"JST", "yy");
            if(month == 0){
                let year = 23;
                month = 12;
            }
            const targetMonth = year + "年" + month + "月";
    
            let name = sheet.getName();
            if(name.match(targetMonth) != null){
                visible_sheets.push(sheet)
            }
        }
      })
    
      // console.log(visible_sheets[6].getName());
      // return
      visible_sheets.forEach(function(sheet){
        sheet.getDataRange().setFontSize(18).setFontWeight("bold").setFontFamily("Arial")
      })
    }

予約管理ファイルは1年間を通して同じファイル内で管理を行うので、施設数✖️12か月分というシートが用意されることになります。
シートについては、2か月前のものについては非表示になるようにプログラムを組んでいるので、現状表示されているシートの中から、当該月のものだけをピックして、そのスタイルをリセットできるような感じにしました。

毎日、すべてのシートで処理を行うと結構な処理時間がかかってしまいますからね…
参考
https://qiita.com/kakakaori830/items/84a7dcba2d6119ed320a

そして最後に日々の売り上げを算出する処理ですが、こちらは施設に応じて料金体系が異なるので力技感もありますが、基本的には、各メニュー項目における料金✖️その日（列毎の）件数を計算した上で、それらをすべて加算することで売上のベースを確定させます。
そこに不定期発生する金銭授受に関して金額を加算できるようにしました。
すでにonEditを使っているので、どうやって都度計算しようか悩みましたが、結論としてはチェックボックスを各列に一つ用意し、日々の業務が終わったらそこにチェックを入れてもらうことで不定期発生分の金額も含めた計算処理が走るようにしました。

function sumcash(check){
    // 現在のシートを取得
    var ss = SpreadsheetApp.getActiveSheet();
    var col = ss.getActiveCell().getColumn();
    var base_row = ss.getRange(4,2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    var values = ss.getRange(7,col,base_row-14,1).getValues();
  
    if(check !== true){
        return
    }
    // checkの値がTrueの時の処理

    // 列番号から、上から順に見ていって『現金●●円』の表記があるセルをピックアップ
    var cash_array = [];
    for(var i=7; i<=base_row; i++){
        var get_cash = ss.getRange(i,col).getDisplayValue();
        if(get_cash.match("現金対応") !== null){
          continue;
        }
        if(get_cash.match("現金") !== null){
            var removed = get_cash.replace(/,/g,"").replace(/\D/g,"").replace(/[^\x01-\x7E]/g,"");
            
            var cash = parseInt(removed,10);  //現金、の入っていたセルから位区切のカンマを削除して10進数の数字型に変換
        
            if(typeof(cash) == "number"){
                cash_array.push(cash);
            }else{
                var cash = 0;
            }
        }
    
        if(get_cash.match("クレジット") !== null){
            var removed = get_cash.replace(/,/g,"").replace(/\D/g,"").replace(/[^\x01-\x7E]/g,"");
            var cash = parseInt(removed,10);
            cash_array.push(cash);
        }
    }
    return cash_array.reduce(function(sum, element){
        return sum + element;
    }, 0);;
}

ちょっと処理に時間がかかるのは嫌な点ですが、入力される項目のランダム性とかを考慮すると仕方ないのかなという部分もあるという感じです。

0
0
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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?