😱

GASで提出状況のチェック

2022/07/21に公開

Google スプレッドシート

GAS

正規表現

tech

毎月数回、社内でレポートの提出がありますが、その締め切り時に提出が出来ていない人のチェックをしている総務部長から地味に大変だと相談をいただきました。

業務内容

毎回タイトルをkintoneで発信(1タイトル1レコード)、レポート提出をkintoneアプリでしてもらいます。
レポートアプリのレコードはスプレッドシートへGASで出力しています。

kintoneから出力している提出状況シートのイメージ

参加メンバーシートのイメージ

事前準備

GASでチェックするにあたり、チェックしたいタイトルはなにかを明らかにしないといけないので、色々考えましたが、シート上で設定することにしました。

設定用シート

入力規制で提出状況から選択するように設定

セルA2は入力規制でkintoneから出力しているタイトルから選択式にします。
ちなみにこのタイトルはタイトルアプリからルックアップしているので、バラバラにはなりません。

テーマ「2022/7/10締切りaaaaaaaa」をみると規則性があったので締切日は正規表現で取り出します。

=iferror(REGEXEXTRACT(A2,"(.*)締切.*"),"")

この締切日は新しく作成するシートのシート名に使います。

ここまで出来たら準備はOKです。

コード

コードgs

function myFunction() {
　const ss = SpreadsheetApp.getActiveSpreadsheet();
  /*チェック対象のタイトルと締切日 */
  const sh = ss.getSheetByName("表紙");
  const title = sh.getRange("A2").getValue();
  const deadLine = sh.getRange("B2").getValue(); 

　/*チェック対象の名前を配列で取得*/
  const checkSh = ss.getSheetByName("参加メンバー");
  const cLastRow = checkSh.getLastRow();
  const nameList = checkSh.getRange("A2:A"+cLastRow).getValues();

  /*提出状況*/
  const situationSh = ss.getSheetByName("提出状況");
  const sLastRow = situationSh.getLastRow();
  let situationList = situationSh.getRange("A2:C"+sLastRow).getValues();
  /*チェック対象のタイトルの行でフィルターをかける*/
  situationList = situationList.filter(function(rowItem){return rowItem[2] === title});

  /*名前のスペースを削除しておく*/
  let situationNameList = []
  situationList.forEach(function(name,index){
    situationNameList[index] = name[1].replace("　","");
  });

  /*まだシートがなければ新しいシートを追加する*/
  const allSheets = ss.getSheets();
  const sheetsName = allSheets.map(function(sheet){return sheet.getName()});
  if(sheetsName.indexOf(deadLine) <= -1){
    ss.insertSheet(deadLine,1);
  }else{
    ss.getSheetByName(deadLine).clearContents();
  }

  /*チェック結果の配列を生成*/
  nameList.forEach(function(name){
    if(situationNameList.indexOf(name[0]) <= -1){
      //未提出
      name[1] = "未提出";
    }else {
      //提出済
      name[1] = "提出済";
    }
  });
  //結果出力
  ss.getSheetByName(deadLine).getRange(1,1,nameList.length,nameList[0].length).setValues(nameList);
  return;
}

実行する時

図形にスクリプトを割り当ててワンクリックで簡単に実行できるようにしています。

ボタン

解説

必要な情報を取得して準備します。

提出状況のシートから情報を取得した後、今回チェックしたいタイトルの行だけでいいのでフィルターをかけています。

/*チェック対象のタイトルの行でフィルターをかける*/
  situationList = situationList.filter(function(rowItem){return rowItem[2] === title});

filterメソッドを使っています。
filterメソッドはArrayオブジェクトの反復メソッドです。

array.filter(function(name,index,array){/*フィルター条件*/});

コールバック関数を満たす要素のみの配列を返してくれるので、配列内の3番目(c列にあるテーマ)が、変数title(設定用シートのA2に入っているタイトル)に一致している行だけ残ります。

参加者の氏名はスペースが入っていたりいなかったり結構あるので、削除しておきます。

/*名前のスペースを削除しておく*/
  let situationNameList = []
  situationList.forEach(function(name,index){
    situationNameList[index] = name[1].replace("　","");
  });

replaceメソッドで置換しています。

文字列.replace("置換前","置換後")

シートが増殖してはいけないので、同じテーマで二回目以降にチェックしたらシートをクリア、初めてならシートを追加しています。

/*まだシートがなければ新しいシートを追加する*/
  const allSheets = ss.getSheets();
  const sheetsName = allSheets.map(function(sheet){return sheet.getName()});
  if(sheetsName.indexOf(deadLine) <= -1){
    ss.insertSheet(deadLine,1);
  }else{
    ss.getSheetByName(deadLine).clearContents();
  }

スプレッドシート内のシート名を一度で取得できたらいいですが、シート名は一つしか取得できないようだったので、全シートを取得したらmapメソッドで全シート名を配列にしています。
mapメソッドもfilterメソッドと同じような使い方ですが、mapメソッドはコールバック関数の戻り値で配列を生成するメソッドです。

array.map(function(name,index,array){/*配列にしたい値*/});

最後にチェック結果を配列にしてsetValuesで完成です。

所感

filterメソッドやmapメソッドは初めて使いましたが、とても便利でした。
危うくループ回して条件分岐してフラグ立ててチェックするところでした・・
GASは処理が遅いのでなるべく配列で処理して、スプレッドシートへのアクセスは最低限にしようと心掛けました。
レポート提出の参加メンバーは80人弱いるので、それを一人ひとり確認していく作業は大変だったと思いますが、GASを使って数秒で出来るようになりました。

参考文献

業務内容

事前準備

コード

実行する時

解説

所感

Discussion