スプレッドシートの自動コピーと内容の自動整理

まえおき

弊社では在庫管理をグーグルスプレッドシートで行っており、毎月新しい在庫表を私が作って担当者に配布していたのですが、
どうにも作り忘れることが多発するため全自動で勝手にやってしまいたいなと思いスクリプトにしてみました。
スクリプトざっとした説明としては
・バッチ処理用のスクリプトはスプレッドシートに埋め込まず外部で作成している。
・特定のフォルダ内にシートを複製する。
・複製したシートを操作する。
・メールを送信する。
といった内容のスクリプトとなっております。

プロジェクトのトリガーとして毎月1日の朝の6時～7時に設定しておいているので
朝会社に来た時には自動的に関係者へコピーして内容を修正したスプレッドシートが送られている・・・。というやりかたです。

また当方全くの初心者な為あえて今まで使ったことのなかったswitchやindexof、各functionに値渡しの挙動について覚えるために
あえて取り入れてあります。おそらく不適切な使い方がいくつかあるかとは思います。
もしよければこういう使い方が正しいよ！といった内容がございましたらどしどしご指摘頂けると嬉しいです！！！！

フォルダ階層について

下記の画像のようにフォルダが作られております。
ちなみに今回の処理ではこのフォルダにどんどん新規のスプレッドシートが作成されていく仕組みです。
【バッチ】というフォルダの中にスクリプトファイルとデータ書き込み用のスプレッドシートが配置されています。

スクリプトについて

今回作成したスクリプトについてタイトル、新規作成したスプレッドシートのURLとID、期、月についてスクリプトの中で記憶させておく術が、
私の中では全く思いつかなかったため、記録用のスプレッドシートを作成しました。

このスプレッドシートにタイトル、ID、期、月を記録しておくことにより次月は何を作成すればよいのか・・・という内容をAPIを無駄に叩かずに判別できると考えました。

在庫表の中身について

マスターシートと集計表が下記の様なスプレッドシートになっており、

入庫した際に入力するシートが下記の様になっており、

出庫した際に入力するようなシートが下記の様な構成になっています。

コード

コードの紹介になります。
前提としては弊社は10月が期末となっており、ファイル名が期+月といったフォルダ名になっております。
そのため11月に入ると期の数字が一つ増えます・・・。

function Createsheet(){
  var sp = SpreadsheetApp.openById('データを書き込む用のシートID'); //バッチ処理用のIDとかタイトルが記入されてるスプレッドIDを
  var sh = sp.getActiveSheet();
  var copygetid = sh.getRange(("B"+sh.getLastRow()) + ":" + "D"+sh.getLastRow()).getValues();

  var thisfolder = DriveApp.getFolderById("コピー先フォルダのID");　//コピーしたいフォルダIDを指定すること
  var thisfile = DriveApp.getFileById(copygetid[0][0]);

  var valueset = [[]]; //[0][0]はスプレッドタイトル、[0][1]はスプレッドID、[0][2]は期、[0][3]は月を格納している。

  if(copygetid[0][2] == 10){ //期判断　
    valueset[0][2] = copygetid[0][1] + 1;
  }else{
    valueset[0][2] = copygetid[0][1];
  }

  if(copygetid[0][2] == 12){ //月判断
    valueset[0][3] = 1;
  }else{
    valueset[0][3] = copygetid[0][2] + 1;
  }  

  valueset[0][0] = thisfile.makeCopy("第" + valueset[0][2] + "期" + valueset[0][3] + "月在庫表",thisfolder);　//タイトルを取得
  valueset[0][1] = valueset[0][0].getId();

  sh.getRange(("A"+(sh.getLastRow()+1)) + ":" + "D"+(sh.getLastRow()+1)).setValues(valueset);
  strDelete(valueset[0][1],valueset[0][3]);

}

function strDelete(id,mon){
  var getsheets = SpreadsheetApp.openById(id).getSheets(); 
  var sheetcnt = getsheets.length;
  var yr = Utilities.formatDate(new Date(),"Asia/Tokyo","yyyy") - 1988; 
  var setsheetname = getsheets[0].getName();

  for(i = 0; i < sheetcnt; i++){
    var sheetname = getsheets[i].getName();
    var sheetflg = "なし"
    sheetflg = sheetjudgment(sheetname,sheetflg)
    var getsheet = SpreadsheetApp.openById(id).getSheetByName(sheetname);

    switch(sheetflg){
      case "在庫表":
        stocktablebat(sheetname,getsheet,mon,yr);
        break;
      case "入庫表":
        intablebat(sheetname,getsheet,mon,yr,setsheetname);
        break;
      case "月":
        monthtablebat(sheetname,getsheet,mon,yr,setsheetname);
        break;
    }
  }
  //sendToMail(id,mon)
}

function sheetjudgment(sheetname,sheetflg){
  if(sheetname.indexOf("在庫表") !== -1){
    sheetflg = "在庫表";
    return sheetflg;
  }
  else if(sheetname.indexOf("入庫表") !== -1){
    sheetflg = "入庫表";
    return sheetflg;
  }
  else if(sheetname.indexOf("早見表") !== -1){
    sheetflg = "早見表";
    return sheetflg;
  }
  else if(sheetname.indexOf("月") !== -1) {
    sheetflg = "月";
    return sheetflg;
  }
  else if(sheetname.indexOf("倉庫移動") !== -1){
    sheetflg = "倉庫移動";
    return sheetflg;  
  }
}

function stocktablebat(sheetname,getsheet,mon,yr){
  getsheet.getRange("A1").setValue("H" + yr + "." + mon + "　在庫表");
  getsheet.setName("在庫表H" + yr + "." + mon);
  getsheet.getRange("M6:N320").clearContent();

}

function intablebat(sheetname,getsheet,mon,yr,ges){
  getsheet.getRange("B3").setValue("H" + yr + "." + mon + "　入庫入力表");
  getsheet.getRange("B6:F300").clearContent();
  getsheet.getRange("M6:M300").clearContent();

  var setv = [[]];
  setv[0][0] = "=if($E6=\"\",\"\", vlookup($E6,'" + ges + "'!$A:$L,2,0))";
  setv[0][1] = "=if($E6=\"\",\"\", vlookup($E6,\'" + ges + "\'!$A:$L,3,0))";
  setv[0][2] = "=if($E6=\"\",\"\", vlookup($E6,\'" + ges + "\'!$A:$Q,17,0))";
  setv[0][3] = "=if($E6=\"\",\"\",vlookup($E6,\'" + ges + "\'!$A:$G,7,0))";
  setv[0][4] = "=rounddown(J6*(1+'" + ges + "'!$P$2),0)*F6";
  setv[0][5] = "=F6*J6";
  getsheet.getRange("G6:L6").setValues(setv);

  getsheet.getRange("G6:L6").activate();
  getsheet.getActiveRange().autoFill(getsheet.getRange("G6:L300"),SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

}

function monthtablebat(sheetname,getsheet,mon,yr,ges){
  getsheet.getRange("A3").setValue("H" + yr + "." + mon + "　出庫入力表");
  getsheet.setName(mon+"月");
  getsheet.getRange("A6:H600").clearContent();
  getsheet.getRange("M6:M600").clearContent();
  var setv = [[]];
  setv[0][0] = "=if($G6=\"\",\"\", vlookup($G6,\'" + ges + "\'!$A:$L,5,0))";
  setv[0][1] = "=if($G6=\"\",\"\", vlookup($G6,\'" + ges + "\'!$A:$Q,17,0))";
  setv[0][2] = "=if(G6=\"\",\"\",vlookup($G6,\'" + ges + "\'!$A:$G,7,0))";
  setv[0][3] = "=rounddown(K6*(1+'" + ges + "'!$P$2),0)*H6";
  setv[0][4] = "=H6*K6";
  getsheet.getRange("I6:M6").setValues(setv);

  getsheet.getRange("I6:M6").activate();
  getsheet.getActiveRange().autoFill(getsheet.getRange("I6:M600"),SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  getsheet.getRange("I6:M600").activate();
}

function sendToMail(id,mon){
  var mailaddress = "メールアドレス";
  MailApp.sendEmail(mailaddress, mon + "月分在庫表を作成いたしました","関係各位\nお疲れ様です。\n" + mon + "月分の在庫表を作成いたしましたのでご確認のほどよろしくお願い致します。\n\n\n" + "https://docs.google.com/spreadsheets/d/" + id);
}

コードの解説

■在庫自動出力用というスプレッドシートの末尾に作成する内容を自動的に書き出し、
書き出したのちに、前月分のスプレッドシートをコピーして内容を変更するスクリプトになっている。
バッチが動くタイミングは毎月1日の６～７時で設定を行っている。
下記の前月のスプレッドシート、次月のスプレッドシートという表現は以下を例に判断をしてほしい。
　現在の日時　2018年11月1日
　前月のスプレッドシート　＝　10月のスプレッドシート
　当月のスプレッドシート　＝　11月のスプレッドシート

～スクリプトの流れ～
◇Createsheet()
■1～4行目
　createsheetがメインの関数となっている。
　在庫自動出力用のスプレッドIDを指定しspに格納
　shにアクティブシート(シートは一つしかない為シート１が自動的に選択される）
　copygetid[0][0] = “ID”
　copygetid[0][1] = “期”
　copygetid[0][2] = “月”
　※getvaluesでデータ範囲で値を取得すると2次元配列として格納される。
■6～7行目
　thisfolderにてコピー先のフォルダーを指定する。
　thisfolderの中のコピー元のファイルを特定しておく。※後にコピーをする際に必要なため。
■9行目
　valuesetの二次元配列を用意する。
　[0][0]にはコピー後のスプレッド（当月のスプレッド）のスプレッドシートのタイトル
　[0][1]にはコピー後のスプレッド（当月のスプレッド）のID
　[0][2]にはコピー後のスプレッド（当月のスプレッド）の期
　[0][3]にはコピー後のスプレッド（当月のスプレッド）の月を格納するための配列である。
■11～15行目
　期判断の部分ではcopygetid[0][2]（現在の月）をif文にて期を判断する。
　10月の時点でバッチが走ると11月分を作成するため
期が変わるので現在の期にプラス１をした値をvalueset[0][2]に代入する。
■17～21行目
　月判断の部分ではcopygetid[0][2]（現在の月）をif文にて月を判断する。
　12月の時点ではバッチが走ると1月分を作成したいので13月にならないように1月とする。
■23行目
　valueset[0][0] = thisfile~
　というところではthisfile（前月のスプレッドシート）をコピーし、
　thisfolder（保存したいフォルダ）にコピーを行いつつ、valueset[0][0]にタイトルを格納している。
■24行目
　valueset[0][1]にてコピーしたスプレッドシートのIDを取得し配列に格納する。
■26行目
　sh.getRange~にてスプレッドシートに次月のタイトル、ID、期、月を書き込む。
■27行目
　strDeleteにスプレッドIDと月を値として渡して次の関数へ

◇strDelete
■32～34行目
　getsheetsはCreatesheetからvalueset[0][1]（次月のスプレッドシートのID）を
　1次元配列で次月のスプレッドシートのシートたちを取得する。　
　sheetcntは次月のスプレッドシートのシート数を格納する。
　yrは今現在の年を取得し　2018年だったら　30と返すように和暦の数字のみを格納している。
■35行目
　のちにVlookupの関数をシートに埋め込むため在庫表H??.??月のシート名を取得しておく。

■37行目～55行目と58行目～79行目
　for分でシートの枚数だけ以下の処理を回す。
　□38行目
　　sheetname = getsheets[i].getNameにてシートの名前を格納する。
　□39行目～40行目
　　sheetflgをデフォルトで無しにセットしたのちにsheetflgをセットするsheetjudgmentを呼び出す。
　□58行目
　　strDeleteよりsheetnameとsheetflgを受け取る。
　□59～79行目
　　if文にてsheetnameの部分一致検索を行いsheetflgに特定の値を格納し、strDeleteへsheetflgを返す。
　　sheetjudgmentの処理はこれで終わり
　□41行目
　　処理を行いたいシートをシート名指定でセットしておく。
　□43行目
　　switch文にてsheetflgを判断し個別に関数を呼び出す。
　□45行目　
　　case 在庫表
　　処理しようとしているスプレッドシートの名前、処理を仕様としているスプレッドシートの場所、
　　月、現在の年を引数で渡している。
　□47行目
　　case 入庫表
　　処理しようとしているスプレッドシートの名前、処理を仕様としているスプレッドシートの場所、
　　月、現在の年、35行目で取得したVlookup用の在庫表H??.??のシート名を引数で渡している。
　□50行目
　　case 月
　　処理しようとしているスプレッドシートの名前、処理を仕様としているスプレッドシートの場所、
　　月、現在の年、35行目で取得したVlookup用の在庫表H??.??のシート名を引数で渡している。

■81行目～123行目
　各指定したシートの不要な部分の削除を行っている。
　setvについてはsetValuesで値を書き込むために2次元配列で初期化を行っている。
　各々setvにスプレッドシートに書き込みたい値をセットしsetValuesで貼付けを行った後、
　一番最後の行までオートフィルを使用し貼付けを行っている。

■55行目と125行目～128行目
　sendToMailにて処理が終わった後にメールを送信している。
　□126行目
　　送信先のメールアドレスをセット。
　□127行目
　　126行目でセットしたメールアドレス宛に
　　MailApp.sendEmail(”送信先メールアドレス”,”タイトル”,”本文”)といった内容でメールを送信している。

終わりに

以前はSetValuesの扱い方が全く分からずひたすらSetValueをループさせたり、
引数で値を渡せばよいのにグローバル変数にして無理やり処理をしてみたりと無茶苦茶をやっていましたが、
その当時よりは真面なコードがかけているのかな・・・？
とにかく今回のこのプログラムではSetValuesと値渡し、switch文の使い方について勉強ができたので個人的にはいい勉強になったと思っています。
ただこのレベルではまだまだ終わりたくないので精進していきます。